Executing Web Service from SQL - Ref: Rashed

rrvanzyl · 2011-10-28

Where to begin...

So I read Rashed's Solution http://mibuso.com/blogs/ara3n/2009/11/14/replacing-nas-with-sql-jobs-and-nav-web-service/
to replace NAS with Web Service executed through a SQL Job.

The setup is simple, I am connecting to Cronus, running SQL 2005, Windows 7 and NAV 2009 R2 all on the same machine.
When navigating to my Web Service in Explorer, all is well and I can see the pulished Code Unit tasks that I set up.

Now, back to Rashed's post above; I created the new NavobScheduler as described, and all went as expected, until I tried to execute it.
SQL gives me the following error:

Msg 6522, Level 16, State 1, Procedure NavJobScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NavJobScheduler": 
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 192.168.10.222:7047
System.Net.Sockets.SocketException: 
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException: 
   at System.Net.HttpWebRequest.GetRequestStream(TransportContext& context)
   at System.Net.HttpWebRequest.GetRequestStream()
   at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)

I have opened ports 7046 and 7047 on the machine, in case there there may be an issue using the stored proc to connect to the WS, no change. :-k

Does any one have any ideas or (helpful) thoughts on this? [-o<

Thanks in advance!

rrvanzyl · 2011-10-28

Ok, so after restarting the web service (which I think somehow stalled ??) I am receiving this error:

Msg 6522, Level 16, State 1, Procedure NavJobScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NavJobScheduler": 
System.Net.WebException: The remote server returned an error: (401) Unauthorized.
System.Net.WebException: 
   at System.Net.HttpWebRequest.GetResponse()
   at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)

The Windows Authenticated user name I am using is setup in NAV and SQL.

kriki · 2011-10-28

Seems you don't have access to the DB.
Probably you have to setup the SP's for using webservices.

rrvanzyl · 2011-10-28

Can you please elaborate on your comment?

I have enabled CLR in the SQL database.
The db is also setup as "Trustworthy"
The domain user name I use is setup in NAV as SUPER.

kriki · 2011-10-28

If you are working with different machines, you need to set up the spn's (server principal names) on the domain for delegation.

Here are some links:
http://msdn.microsoft.com/en-us/library/dd301254.aspx
http://blogs.msdn.com/b/nav_developer/archive/2009/01/16/nav-2009-web-services-on-a-three-machine-setup.aspx
http://msmvps.com/blogs/kine/archive/2011/05/29/dynamics-nav-2009-and-spns.aspx
http://blogs.msdn.com/b/nav/archive/2009/10/20/creating-a-web-service-manually-the-importance-of-what-name-you-give-it-and-a-few-small-things-to-remember.aspx

rrvanzyl · 2011-10-28

Thanks! I am however running everything on one machine.

I sorted the issue, I had to make a change to Ara3n's code in the StoredProcedure.cs file

//System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
//NetworkCredential netCred = new NetworkCredential(Login, Password, Domain);
//myCredentials.Add(new Uri(WebServiceURL), "NTLM", netCred);
//request.Credentials = myCredentials;

System.Net.ICredentials netCred = new System.Net.NetworkCredential(Login,Password,Domain);
request.Credentials = netCred;

To be honest, I am not totally sure what the difference is between the original code and the code I replaced it with.

ara3n · 2011-10-28

Try and run your solution in Visual Studio using C# instead of CLR. It could be just delegation.
Or run it on one pc environment where everything is on box. sql server/ service tier.
I'm guessing you have changed webservice authentication to NTLM in config file?

rrvanzyl · 2011-10-28

Thanks Rashed for taking the time to reply to my message.
Yes, NTLM is active in the config file of the Web Services.
Everything (SQL, Service Tier) is already on one PC.
If I run it in VS with C#, works as expected...

In the end I made the following change to your code which allowed the execution from SQL to work.
//System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
//NetworkCredential netCred = new NetworkCredential(Login, Password, Domain);
//myCredentials.Add(new Uri(WebServiceURL), "NTLM", netCred);
//request.Credentials = myCredentials;

System.Net.ICredentials netCred = new System.Net.NetworkCredential(Login,Password,Domain);
request.Credentials = netCred; <-- New Code

I found the code in the comments on an MSDN site: Unauthorized or AccessDenied exception when using SOAP Native Xml Web Services
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/18/482315.aspx

ara3n · 2011-10-28

are you using my visual studio project and my functions? I would like you to run it without changing anything.

rrvanzyl · 2011-10-28

Yes, I used your VS project and functions, that's when I received the unauthorized error when running stored proc from SQL Management Studio.

ara3n · 2011-10-28

Change the code back to the way it is. I just downloaded the project under VS 2010. SQL Server 2008.

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO

ALTER DATABASE HH SET TRUSTWORTHY ON

disabled firewall for debugging for VS. Ran VS as admin. and the deployment was successful on SQL Server. I made sure it was for .NET 3.5.

In CustomSettings.config I set NTLM to true

Ran the command it worked.

http://localhost:7047/DynamicsNAV/WS/[COMPANYNAME]/Codeunit/RunObject

And it ran successfully.

I then changed CustomSettings.config to false, and restarted the service tier and got the error you are receiving

Msg 6522, Level 16, State 1, Procedure NavJobScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NavJobScheduler":
System.Net.WebException: The remote server returned an error: (401) Unauthorized.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)
.

So please double check the setting and restart the service tier.

rrvanzyl · 2011-10-31

Great! Thanks Rashed, I am going to triple check everything now, like you stipulated above and change the code back.
Do you have any ideas why the code I added worked? I mean, in theory, I should be getting the same error? :-k

NavJen · 2011-10-31

I am running into the exact same problem. I did the following:

1. Enabled CLR
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
2. Set Trustworthy On
ALTER DATABASE TestDatabase SET TRUSTWORTHY ON;
3. Modified the config file to use NTLM authorization
<add key="WebServicesUseNTLMAuthentication" value="true"></add>
4. Restarted the services
5. Tested to make sure I could access the web service from IE, both from the server and from a client machine

When I try and run the NAVJobScheduler from SQL I receive the same error message:
Msg 6522, Level 16, State 1, Procedure NAVJobScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NAVJobScheduler":
System.Net.WebException: The remote server returned an error: (401) Unauthorized.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)

I have not modified any of the functions in VS at all. I was able to get it to work in a test environment but when I move it to live I get the error mentioned above. I thought the issue might be SPN delegation, but if it was, why would I be able to access the web services via IE on the third tier without error?

The service is running under the network service account and not a domain account which might be a factor. I would prefer not to change this if possible because the issue is in the client’s live environment.

Any insights or help would be appreciated, thanks!!

ara3n · 2011-10-31

what spn have you registered? There are separate ones for http that need to be registered in AD.

ssingla · 2011-11-01

Its not totally relevant for this post but I have written a blog on SQL CLR.
http://mibuso.com/blogs/ssingla/2011/11/01/replace-nas-with-sql-jobs-extension/

zimworxcode · 2011-11-16

I am facing the same problem as NavJen. so was this resolved and how? On my local developer machine the solution runs perfectly but on Live it does not run. I have noticed that the authentication scheme on my local machine is NTLM and that on the live is SQL. could this be the problem? how would i resolve it?

ara3n · 2011-11-20

you need to setup delegation (SPN) for HTTP

Lec11 · 2011-11-23

Hello,

I'm also trying to implement this solution, on my local machine for the moment.

First I stumbled upon the following error when running the procedure in SQL Server:

A .NET Framework error occurred during execution of user-defined routine or aggregate "spNavJobScheduler":
System.Net.WebException: The remote server returned an error: (500) Internal Server Error.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)

.
Consulting the event viewer is says:

Message: Access denied. Delegation or Impersonation permission is required for access.

Of course, I read all the posts from Mibuso regarding this solution and many other posts from the web. I tried to run it from a console application in C# but the same problem occurred. Finally, after changing the credentials to

request.Credentials = System.Net.CredentialCache.DefaultCredentials;

I was able to run it successfully in C#. So I modified the procedure accordingly, and run it again in Sql Server, but the problem persists. The only difference is that is no error in Event Viewer anymore.

So, does somebody have a clue about what might be wrong?

Thanks!

ara3n · 2011-11-24

SQL SLR does not pass the sql agent credentials you have to pass your own.

Lec11 · 2011-11-24

Thanks for your answer!

However this is exactly what I've done first ... but got the "impersonation or delegation permission" error. I granted every of these permissions to my request but the error is still there.

Lec11 · 2011-11-24

Problem solved ... it appears I didn't have modify permissions in Security tab for Application Data folder.

Regards!

NavJen · 2011-12-09

I am still running into the same problem, I have never gotten this resolved.

I double checked and the SPN for HTTP is setup. There are four SPNs setup in total, two for HOST and two for HTTP (one with just the server name and one with the fully qualified domain name).

I tried it on two separate servers, one with the service setup with a domain login and the other one using the network service account. Both receive the same error message. I can access the Web service through a browser but when I try and run it from SQL I receive the following error message:

Msg 6522, Level 16, State 1, Procedure NAVJobScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NAVJobScheduler":
System.Net.WebException: The remote server returned an error: (401) Unauthorized.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)

I triple checked and NTLM is set to true in the config file. I am not sure what else to try. Any suggestions?

madalina · 2012-02-22

I solved the problem by setting up the web service accout with a domain user that has full rights on sql database in question.

On the other hand I encounter a problem when trying to use the procedure for multi company.
I have modified the sql script to switch the company name in the url of the web service.
The codeunit that is lanched by the web service calls the report 795 Adjust Cost - Item Entries.
The database is a big one, with 5 companies inside 3 of which have a lot of item ledger entries.
I have created a job on the SQL Agent with a step that executes my script (the one that calls the stocked procedure for different companies).
The problem is the following, the job runs fine for the first company which is a small one, it finishes in about 20 sec then it passes to the second company which has a big amount of entries and then is crashes with a timout error.
When i check in navision the item register in the first company it's ok, the WS has done it's job well and then i go to the second (large) company and i see the record inserted by the WS but it seems that the batch is still running, because the To Value Entry No. field goes on refreshing, and this goes on for about 10 min every time I do a refresh untill it reaches it's end.
So to resume, the script form the SQL Job returns en error, while the WS is still running the codeunit utill it finishes what it has to do.

My question is : does this method work for multi company with long batches to run?

Any answer or suggestion will be welcome.

Mada

deV.ch · 2012-02-22

There is a Timeout Property in the WebRequest Object. I guess you need to increase that one for large jobs... Maybe it's possible to disable the timeout, but i don't know.

madalina · 2012-02-22

I have :
"OperationTimeout" value="MaxValue" in the CustomSettings.

Thanks for the answer.

deV.ch · 2012-02-22

Yeah thats good, but i mean the timeout for your WebRequest, you need to change the code of the CLR that you call from sql.

If you are using Rashed's solution, open the visual studio solution (NavJobScheduler.sln) and add such a line:

request.Timeout = int.MaxValue;

Then it looks like this:

        WebRequest request = HttpWebRequest.Create(WebServiceURL);
        request.Headers.Add("SOAPAction", @"""urn:microsoft-dynamics-schemas/codeunit/RunObject:RunJob""");
        request.ContentType = "application/xml; charset=utf-8";
        request.ContentLength = Body.Length;
        request.Method = "POST";
        request.Timeout = int.MaxValue;

        System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
        NetworkCredential netCred = new NetworkCredential(Login, Password, Domain);

Then rebuild the project.

And report back if taht halped you. I haven't tested this, but the webrequest has a default timeout of 100sec.

madalina · 2012-02-22

I have modified now the StoredProcedure1.cs in Rashed's solution, i have rebuilt the solution and regenerated the assembly and the stocked procedure in my sql database.

When i run de job defined in the prevous post i get the same error of timout (with [SQLSTATE 42000] (erreur 6522)). In fact nothing has changed

madalina · 2012-02-28

I've found a work around the problem by creating a Job with one step for each company in my database.
It works fine, the only downside is that if new companies are created in the database, new steps must be created in the job with the according T-sql scripts.

But for now that will do.

Cheers,

Howdy, Stranger!

Quick Links

Categories

Executing Web Service from SQL - Ref: Rashed

Comments