Failing to run a web service from mssql stored procedure

zimworxcode

I have been going through the post By Rashed Amini on how to replace the NAS with the SQL Job Scheduler. It describes how to run a stored procedure created through CLR in MSSQL, the stored procedure will call a web service.

http://mibuso.com/blogs/ara3n/2009/11/1 ... b-service/

I have managed to follow the instructions on the blog, now when I test the Dynamics NAV exposed services by running the following command in Management Studio:

EXEC NavJobScheduler 'codeunit',50010,'USERID','password','Domain','http://localhost:7047/DynamicsNAV/WS/KRONUS2009SP1/Codeunit/RunObject'

I get the error:

Msg 6522, Level 16, State 1, Procedure NAVScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NAVScheduler":
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)

When I take a look at the Event Viewer, I see some login failure message: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

If anyone could direct me how i should resolve the error. I am using MSSQL 2008 R2, and my OS is Windows 7 Home basic.

Sam_Morris

Sounds as if you need to give the user NT AUTHORITY\NETWORK SERVICE permissions in NAV. Or, run the procedure using a different login that does have permissions. Remember that the windows user calling the webservice still needs to authenticate in NAV.

NavJen

Were you able to find a resolution? I am running into the same issue so if you could share the resolution that would be great!

zimworxcode

I noticed the my URL was not properly formed so you may want to check your parameters.

ssingla

NavJen wrote:

Were you able to find a resolution? I am running into the same issue so if you could share the resolution that would be great!

I faced a problem when working with the solution and resolved by making 2 users. I used for running the service and another for calling the webservice. The problem does not occur every time I tried in new environment but sometimes it does. I also make sure the service is run by a domain user and not NTAuthority. You may give it a try.