SQL Connectivity errors related to SPN and troubleshooting them.

You might get the following error when there is any issue related to a SPN in the environment:

1) Login failed for user ‘null’

2) Login failed for user ‘’

3) Login failed for user 'NT Authority\Anonymous Logon‘

4) Cannot generate SSPI context

We get these errors when we use windows authentication and there is more than one hop involved in the scenario before passing the authentication to the final destination. For example while browsing a web site which use windows authentication or in case of a linked server.

Let’s take the most common example, when you are browsing a website that is hosted in IIS server and stores information in SQL database. In this case the data is stored in SQL , The web application which is hosted in a separate IIS server talks to this SQL server through windows authentication. Now when a user logs in to the web site on a third box which is also in the same domain as the IIS and SQL server and tries to browse the website, his user account is passed to SQL server via the IIS box in between.

This kind of scenario is called a double hop where the authentication is passed from the originating box (where the user actually opens the website in IE) to the final box (SQL server) in two hops (1. originating box to IIS 2. IIS box to SQL box). In these types of scenarios, Kerberos is a must. Because NTLM cannot delegate authentication in multiple hops.

For Kerberos to work we need proper SPN. If Proper SPNs are not found in active directory, we will encounter the above errors. When we say proper SPN, it means that the SPN should be in proper format, should contain the right information and should not be duplicate.

Troubleshooting :

Prerequisites for Kerberos delegation to work.

1. All computers must be running Windows 2000 or later.

2. All computers must be in the same domain, or domains that trust one another.

3. The domain(s) must be using Active Directory.

4. Kerberos depends on synchronized timing between computers. Out-of-sync computer clocks may cause Kerberos authentication to fail. So all the machines involved in this scenario should have their time synchronised time.

Active Directory Settings

1. Domain users who will be connecting through IIS to SQL Server must not have their domain user accounts marked as "Account is sensitive and cannot be delegated" or should have their “Account is trusted for delegation” checked in the Active Directory Users and Computers tool.

2. The IIS server (the computer that will be delegating) must have "trust computer for delegation" Checked in the Active Directory Users and Computers tool.

3. The IWAM account in case of IIS 5 and in case of IIS 6 and higher, the Log on account for the application pool under which the web application is running must also have “Trust this user for delegation” Checked in the Active Directory Users and Computers tool.

4. It is necessary to register a "Service Principal Name" (SPN) for SQL Server if SQL Server is not running under the LocalSystem account or a domain admin account. If you are running the SQL service under LocalSystem it will automatically create a SPN for the machine account in active directory and if you are running the SQL service in a domain admin account even in that case it will create its own SPN. But if you are running the SQL account in a different domain account you need to manually create the SPN .

To Create SPN you need to have the domain admin privileges. You can use the SETSPN tools to create or delete SPNs. The SETSPN tool comes with the Windows 2000 Resource Kit Tool.

First run the command “SETSPN –L <domain\SQLService account>” to find out if there is any improper or duplicate SPN(this command does not require domain admin privilages). A valid SQL SPN should be in the format “MSSQLSvc/<FQDN of SQL Server>:<SQL Port>” ex: MSSQLSvc/SQLServer.MyDomain.Com:1433. If you find any improper or duplicate SQL SPN then delete them using the command “SETSPN –D <SPN> < domain\SQLService account >”

(You can also use a tool called Delegconfig to find out if there is a duplicate SPN. You can download this tool from the link https://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1434)

Then add the appropriate SPN with the command “SETSPN –A MSSQLSvc/<FQDN of SQL Server>:<SQL Port> < domain\SQLService account >”

If you are trying to connect to a SQL cluster you need to have two SPNs. One with the port and one without. With thefollowing format:

 

MSSQLSvc/<virtual server name>.<rest of FQDN>:<port>

MSSQLSvc/<virtual server name>.<rest of FQDN>

5. For Active Directory to work correctly, DNS must also function correctly. If you are having DNS problems, Kerberos will fail, due to name lookup problems. You can do a ping –a to find out if the IP address and FQDN is being resolved properly.

Client Computers

1. Client computers must be running Internet Explorer 5.0 or later

2. In IE, it may be necessary to turn on "Enable Windows Integrated Authentication" in the browser's Advanced Options.

3. Browsers can't be going through a proxy server. If using a proxy must select option in IE to "bypass proxy for local addresses"

IIS Server

1. Only Windows Authentication, or Windows and Basic, if necessary (see below), should be enabled on the web site or virtual folder. Digest authentication does not support delegation

2. Specify a trusted connection to SQL Server with "Integrated Security=SSPI" in the connection string.

 

 

Author : Snehadeep , SQL Developer Engineer , Microsoft

Reviewed by : SMAT , SQL Escalation Services , Microsoft

Comments

• Anonymous
  February 17, 2010
  We are having this very issue and I just passed a link to the areticle to our server Manager.

• Anonymous
  May 07, 2010
  Do we really need to register SPNs for both of these ? MSSQLSvc/<virtual server name> MSSQLSvc/<virtual server name>:<port> Don't both of these mean the same ? It may appear to be a novice question but I understand 1433 is the default port, so why specify both ?

• Anonymous
  May 10, 2010
  The comment has been removed