다음을 통해 공유

Kerberos Delegation to SQL Server

  • 아티클

One of the most difficult issues to troubleshoot sometimes is a Kerberos Delegation issue to SQL Server.

A customer had a web application that is configured to access a remote back end SQL server under the security context of the end user. When users access the web application, the application displays an error similar to the following:

“Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.”

On the web server, when we browse the web application, it works as expected.

Fortunately, we were able to deduce very quickly that the middle tier is configured correctly for Kerberos Delegation. The customer had another SQL Server hosting the same database. When they pointed it to this server, it worked as expected. SQL Profiler showed that access is being made under the end user's account. So the issue appeared specific to Kerberos Delegation to the targeted SQL Server. What is it that is causing Kerberos Delegation to fail against this one SQL Server?

There are a couple of things to check out, quite a few actually! The following link provides a comprehensive checklist for the SQL Server backend as well as troubleshooting tips:

Troubleshooting Kerberos Delegation
https://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

I believe that the above article provides all the information you need to be able to troubleshoot and resolve most of your Kerberos Delegation problems on the Windows platform.

In the customer's environment, both the working and the failing SQL Server services are running under the same domain account. My immediate suspicion is an SPN problem. During setup, SQL Server can be configured to run under the Local System account. When SQL Server Services are started on a machine, it tries to register a corresponding SPN in AD against the account that it is running under. If SQL Server is running under local system, the SQL Server SPN would be registered against the machine name. If SQL Server is running under a domain account, provided that account has enough rights to AD, SQL Server will register the SPN against that account.

We verified that the correct SPNs are registered against the domain account that SQL Server services is running under on the problem machine. I then had the customer check the SPNs against the machine name and found SQL Server SPNs registered against the machine account:

    MSSQLSvc/<Machine FQDN>:1433
MSSQLSvc/<Machine>:1433

Okay, so that is one configuration problem that we need to rectify. Using SETSPN, we deleted these entries but the problem persisted. What else could it be? Well, were not done with looking for duplicate SPN entries yet. There may be other accounts that the SPN may be registered to. We used the LDIFDE tool as per the article given to query AD for SPNs in an effort to weed out the problem. The following is the query that we used:

ldifde -f ldif.txt -j c:\ -d <your domain> -l serviceprincipalname -r (serviceprincipalname=MSSQL*)"

An example of <your domain> is "DC=microsoft,DC=com,DC=au" (including the quotes)

The resulting ldif.txt file showed that the SPN assigned to the account that SQL Server services are running under is also assigned to another domain account. We removed that SPN as well and after that change we had success!

Comments