Making Nav server talk with SQL server

stiasta · 2011-05-10

Hello!
I have created a web site that uses Dynamics Nav 2009 web services. The web site is hosted on a windows 2008 R2 server that is on a seperate server computer. When I start the web site and use a command that needs dialog between Dynamics Nav server and SQL server this error is raised:

The login failed when connecting to SQL Server [servername].

I think the problem here is that the permissions are not properly set for the dynamics server to properly talk with the SQL server when the IIS-User is connecting to it.

Everything else works properly with the Dynamics Nav server. I can connect with RTC and classic with no problems.

Does anyone have any idea of what this can be?

kine · 2011-05-10

1) The SPNs must be set correctly
2) The Web app must use some account which have needed permissions in NAV. If it is using the client account, this account must have enough permissions in NAV.

stiasta · 2011-05-10

2) For the web site credentials i am using an application user (pass-through authentication)
How can I set the proper user?
I have tried to add the IISUSER to the SQL logins but for some reason the IISUSER doesn't exist for the SQL-Logins, and I am unable to add the user.

kine · 2011-05-10

Try to assign some domain account to your application pool on the IIS and do not use the default user...

stiasta · 2011-05-11

I have made the app pool use network service as a user and the network service is added in the SQL login. But nevertheless it still will not work. Here are the configuration screencaps.

stiasta · 2011-05-11

btw, here is the error message I keep getting. There is nothing wrong when I use the developing computer to run it. The developing computer has all permissions set properly. But when I upload the web site to the server and run it from there, i get this error message.

kine · 2011-05-11

Once again, the SPNs must be set correctly. It looks like delegation is not working...

stiasta · 2011-05-13

kine: thank you for your patience.

Since I haven't heard about spn before I had to look it up.
1. Is it correct that the spn is a tool that makes the client identify an instance of a server?

2. I have been looking around and it seems that the spn tool only has cmd-based interface. Does it have GUI?

3. How do I set it up properly do you have a short command with some explenation?

Thank you for all your help.

Luc_VanDyck · 2011-05-13

Here are answers to your questions 2 & 3:

There is no GUI for the setspn command. Documentation about the command (examples & syntax) is available at http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx

stiasta · 2011-05-13

Great tip!
The proper command to make the microsoft dynamics nav server to recognize the mssql server would then be:

setspn -a MicrosoftDynamicsNavServer/MSSQLSERVER

Is this correct?

Another question, why would I need to set this for the web site and not for the role based client?

kine · 2011-05-13

no, it is not correct, each time you need to specify account under which the service is running (or server account if running under system account like "Network Service").

http://msdn.microsoft.com/en-us/library/dd301254.aspx

http://blogs.msdn.com/b/nav_developer/a ... -2009.aspx

stiasta · 2011-05-13

the different services are runnin on these users:
MSSQLServer = local system account
DynamicsNavServer = local system account

would the spn commands then be something like this?

setspn -a MSSQLSvc/SERVER2.dycon2.no:1433 SERVER2
setspn -a DynamicsNav/SERVER2.dycon2.no:7046 SERVER2

kine · 2011-05-13

Instead SERVER2 will be something like domain\SERVER2$ (this is how the domain account for the server looks like)... And I do not know if Local system account will work. It will work with network service account, but I think that local system account have no access to network resources...

stiasta · 2011-05-13

The SPN's are now added with the commands

setspn -a MSSQLSvc/SERVER2.dycon2.no:1433 dycon2\SERVER2$
setspn -a DynamicsNav/SERVER2.dycon2.no:7046 dycon2\SERVER2$

I have made these services use these user accounts
Dynamics Nav Server - Network Service
SQL Server - Network Service

The AppPool is also using the identity, network service, as shown in the earlier image.

Is there anything else I can try ?

kine · 2011-05-13

And is it working or not?

When I had some problems with SPNs I used this tool:

http://www.iis.net/community/default.as ... g=6&i=1887

see http://blogs.iis.net/bretb/archive/2008 ... onfig.aspx

stiasta · 2011-05-13

sorry,
it is not working.

stiasta · 2011-05-13

Got it to work!

The SPN was what i needed to fix.
+
I had to set anonymous authentication to disabled and then enable windows authentication.

Kine: the tool you gave me is GREAT for setting up SPN. It has a great report that pointed out some errors, and a solution to fix it.

Thank you all for your help!
(Specially Kine)

kine · 2011-05-13

Yes, it helped me too when I was solving similar thing. Basic thing is to not forget that you are not alone with this problem on the planet and because the delegation and kerberos is widely used, you can be sure that someone did something what will help you. The problem is only to find the correct tool... :-D

Making Nav server talk with SQL server

Comments

Categories