§$&%$ Delegation (NAV Server -> SQL Server)

sh

Hi there,

I am having a huge problem with a 3-Tier installation on two different machines. If I try to connect via RTC from one of these two machines to the NAV service tier (which is the second server) it will work. RTC connections from user computers don't work, though.

Error message:
"The login failed when connecting to SQL Server sqlsrvtest.domain.local."

Message in the SQL error log:
Login failed for user "NT-AUTHORITY\ANONYMOUS LOGON"

That's a common authentication error and nothing new to me. Problem is, I cannot find the error and I am lost now. I hope one of you guys might have an idea which will put me back on track

Setting:

Server #1

sqlsrvtest.domain.local

SQL service runs as domain account "sqlsvc@domain.local". This account runs a few SQL services on different machines. The service account automatically registers MSSQLSvc SPNs.

SPNs for user "sqlsvc@domain.local" as shown in Active Directory:

MSSQLSvc/sqlsrvtest.domain.local:1433           <- Test SQL Server, relevant
MSSQLSvc/sqlsrvtest.domain.local
MSSQLSvc/sqlsrv.domain.local:1433		        <- Production SQL Server for NAV, no relevance for 3-Tier (yet)
MSSQLSvc/sqlsrv.domain.local
MSSQLSvc/sqlsrv2.domain.local:1433		<- another production SQL Server, not relevant
MSSQLSvc/sqlsrv2.domain.local

Also running on "sqlsrvtest.domain.local":

Dynamics NAV Server and Web Services for local database "TestDB" as domain account "navsvc@domain.local". These NAV services are fine and aren't my problem.


Yesterday, I installed a second Dynamics NAV Server and Web Services on a different machine. This machine will be the production NAV Server in the future. For now, the services connect to the same database "TestDB" on SQL Server "sqlsrvtest.domain.local".

Server #2

srvnavapp.domain.local

Dynamics NAV Server and Web Services both run as "navsvc@domain.local".

SPNs for user "navsvc@domain.local" as shown in Active Directory:

DynamicsNAV/srvnavapp.domain.local:7046
DynamicsNAV/srvnavapp:7046
DynamicsNAV/sqlsrvtest.domain.local:7046
DynamicsNAV/sqlsrvtest:7046
HTTP/srvnavapp.domain.local
HTTP/srvnavapp
HTTP/sqlsrvtest.domain.local
HTTP/sqlsrvtest

Furthermore, I configured delegation on user "navsvc@domain.local" for MSSQLSvc services. I selected "Trust this user for delegation to specified services only" and "Use any authentication protocol" as we use NTLM for the web services.

As services I chose the MSSQLSvc mentioned above (without the last server):

MSSQLSvc/sqlsrvtest.domain.local:1433		
MSSQLSvc/sqlsrvtest.domain.local
MSSQLSvc/sqlsrv.domain.local:1433		
MSSQLSvc/sqlsrv.domain.local

The domain functional level is "Windows 2003".

So basically I cannot see any flaws here. I got the SPNs, domain functional level is OK and delegation is (theoretically) in place. Any ideas?

Mallikarjuna

Hi Sh,

Delete the MSSQLSvc from the following line in the CusstomSettings.config file and try to run the RTC.

<add key="DatabaseInstance" value="MSSQLSvc"></add>

Thanks,
Mallikarjuna

sh

Today, I had the same problem on a different system, so I plunged deep into the world of Kerberos, SPNs and delegation. Several Technet and MSDN articles later I finally found the solution!

Actually, it's totally easy ](*,)
The MSSQLSvc SPN was already registered on another object (in my case the SQL Server's computer object). Unfortunately, you can only see Kerberos errors but they don't tell you the root cause. So you have to search for them yourself step by step.

You can find a very good article about this here. In the end the ldp tool showed me the duplicated SPN. After deleting the MSSQLSvc SPN on the computer object I had to reboot the SQL Server. Dunno if it's really necessary but after that it worked!

So, if your SQL Server runs as "Network Service" or "Local System" and you change it to a domain account, you should check for duplicated SPN entries if you want to use Kerberos!
(Of course this applies to other services as well)