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

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

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 (8)
  1. Anonymous says:

    Nice work, this post probably saved us several more frustrating days (weeks?) of trouble shooting. In particular the use of the ldife.exe tool.

    In our case, a recently disabled admin account still had the mssql service attached to it! deleting those spn suddenly had everything working fine. (To make matters worse, we had a different problem on our test box’s so we couldn’t even determine the point of failure!)

    Kerberos is a wonderful thing, but it sure can be very very very painful as well.

    Thanks heaps!

  2. Anonymous says:

    As shown in payday loan uk online payday loan application

  3. Anonymous says:

    Anrufen klingelt�ne f�r handy sonneries de t�l�phone

  4. Anonymous says:

    Send cash advance until pay day direct merchant credit card

  5. Anonymous says:

    Troubleshooting Kerberos Delegation

    That link is apparently dead, but I sure would like to read it. Been trying for 5 days to get delegation with iis and sql to work.

    I put in a ticket with MS, because they still reference the page on one of their other pages. I reported it as a dead link from their own page, so maybe they will find and fix it…

  6. Anonymous says:

    <a href= >www maldives sex com www maldives</a> <a href= >www hd-bomba com</a> <a href= >www sexy dz</a> <a href= >www trevesti tv com</a> <a href= ></a>

Comments are closed.

Skip to main content