Regarding Rashed's SQL Jobs and NAV Web Service

melsadek

All,

I'm trying to implement Rashed's "Replacing NAS with SQL Jobs and NAV Web service" from this URL: http://mibuso.com/blogs/ara3n/2009/11/14/replacing-nas-with-sql-jobs-and-nav-web-service/

I followed all the steps diligently, and repeated it several times, yet no matter what I do I can't get past this error message that I get when I attempt to run the NAVJobScheduler stored procedure:

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.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
   at System.Net.HttpRequestCreator.Create(Uri Uri)
   at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
   at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)

The VS project was compiled in VS2010 Pro in .NET 2.0. I have full sysadmin permissions on the SQL Server, and I'm using my AD login credentials as the parameters to the NAVJobScheduler stored procedure. I'm running NAV 2009 R2 Classic 6.00.33137.

Has anyone encountered this problem before and was able to resolve it? I can provide more info if required, but I hope the above suffices.

Wisa123

Hey melsadek,

I don't know the specific example you are referring to and im pretty much guessing here based on the error message.
Actually experienced the error myself a few times.

Please make sure, that the user calling the WS is Super without any company restrictions.
There is a bug in 2009, where users with roles restricted by company cannot access WS properly.

Regards,
/Wisa

melsadek

Hi Wisa,

I can confirm that I have super user access to the NAV database that I am trying to access with this procedure. It must be something else.

Any ideas?

kriki

You can also try using powershell : http://www.mibuso.com/howtoinfo.asp?FileID=24

If you use it in SQL Server Agent, your SQL server agent needs to be a user that is also a user in NAV.

I've used it since some time with NAV2009R2 and later and it works fine. That is the reason I wrote the How-to.

melsadek

Hello Alain,

I thought you nailed it, but unfortunately I haven't even reached the point of placing the stored procedure on the sql agent. I'm just trying to test it right now through a T-SQL script, using my credentials (which are sysadmin on the SQL Server and SUPER user on the NAV database).

What is the error I'm getting referring to? It's really vague and unclear. It also doesn't leave a trace at the Event Viewer.

Any ideas? Rashed?

kriki

Hi Mohamad,

Has your servicetier "Allow Ntlm"=true?

melsadek

Alain,

Are you referring to "WebServicesUseNTLMAuthentication"? If so, it's set to TRUE. I'm not familiar with an "Allow NTLM" attribute...

kriki

That is the one.

The last thing takes comes to my mind is that there are some SPN's missing or wrong. These are needed for NAV2009xx and it is easy to make mistakes.

NCISD

You should check out 3 areas related to permissions below:
1. Have you done setting up access for delegation for the service account that you used for the Web Service logon (under Services). By that I meant SETSPN (lookup a tool called httpnamespacemanager as it may come in handy) and httpcfg.exe (under Service folder of NAV installation).
2. In SQL you need to set up this same account with certain permissions such as Select, Connect, & View Database State).
3. For the account that you used to authenticate for web services you need to raise its delegation level to "Trust this user for delegation for specified Services only" and select all the services that apply.

Hope this helps.

NCISD