mibuso.com

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:


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.

Does any one have any ideas or (helpful) thoughts on this?

Thanks in advance!

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



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

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

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.

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

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.

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?

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

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

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

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



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

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?

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!!

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

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/