mibuso.com

Microsoft Business Solutions online community
It is currently Thu Jun 20, 2013 12:57 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 28 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 9:06 am 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
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:
Code: Select all
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!


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 9:22 am 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
Ok, so after restarting the web service (which I think somehow stalled ??) I am receiving this error:

Code: Select all
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.


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 10:22 am 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7526
Location: Milan
Country: Italy (it)
Seems you don't have access to the DB.
Probably you have to setup the SP's for using webservices.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 10:42 am 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 2:06 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7526
Location: Milan
Country: Italy (it)
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

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 2:21 pm 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 2:27 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8732
Location: 3rd rock from sun
Country: United States (us)
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?

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 2:56 pm 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
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


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 3:27 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8732
Location: 3rd rock from sun
Country: United States (us)
are you using my visual studio project and my functions? I would like you to run it without changing anything.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 3:38 pm 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
Yes, I used your VS project and functions, that's when I received the unauthorized error when running stored proc from SQL Management Studio.


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Fri Oct 28, 2011 5:34 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8732
Location: 3rd rock from sun
Country: United States (us)
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

Quote:
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.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Mon Oct 31, 2011 8:28 am 
Offline

Joined: Mon Mar 10, 2003 12:24 pm
Posts: 17
Location: Johannesburg
Country: South Africa (za)
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


Top
 Profile E-mail  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Mon Oct 31, 2011 4:47 pm 
Offline

Joined: Fri Mar 02, 2007 5:24 pm
Posts: 11
Location: USA
Country: United States (us)
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!!


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Tue Nov 01, 2011 1:30 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8732
Location: 3rd rock from sun
Country: United States (us)
what spn have you registered? There are separate ones for http that need to be registered in AD.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Executing Web Service from SQL - Ref: Rashed
PostPosted: Tue Nov 01, 2011 6:48 am 
Offline

Joined: Thu Dec 08, 2005 9:12 am
Posts: 2870
Location: India
Country: India (in)
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/

_________________
CA Sandeep Singla
http://ssdynamics.co.in


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 28 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 6 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: