Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
Kerberos is a widely accepted network authentication protocol that is used to provide a highly secure method to authenticate users. Reliance is placed upon a trusted third party called the Key Distribution Center (KDC) to facilitate the generation and secure distribution of authentication tokens and symmetric session keys. In a Windows environment, the operation of the KDC is assumed by the Domain Controller (DC) using the Active Directory (AD). Furthermore, all Windows domain users are effectively Kerberos principals and are capable of engaging in Kerberos authentication.
Among the key benefits of Kerberos authentication that make it popular are:
Kerberos with SQL Server
SQL Server 2005 (and 2000) supports Kerberos indirectly through the Windows SSPI interface when using Windows integrated authentication (as opposed to SQL authentication). However, Kerberos will only be used under certain circumstances as SQL Server allows SSPI to negotiate the authentication protocol to use; if Kerberos cannot be used, then Windows will fall back NTLM authentication. Kerberos authentication is far more desirable than NTLM from a security (and, to a lesser degree, performance) point of view and I think it’s important to understand how to ensure Kerberos is used for remote connections when possible. If you're using integrated auth, you need to make sure that the follow are done:
Registering an SPN
The SPN is essentially a mapping between a principal name and the Windows account that started the server instance service. This is needed because the client will use the server’s hostname and the TCP/IP port to which it connects to compose an SPN. If the SPN mapping has not been performed, then the Windows security layer will be unable to determine the account associated with the SPN and Kerberos authentication will not be used. In an attempt to facilitate this, the SQL Server 2005 instance will automatically try to register the SPN with the AD at startup if TCP/IP is enabled. The problem is, however, that only a domain administrator or a Local System account has the authority to register an SPN. Therefore, in the majority of cases where the service is started under a normal account, SQL Server will be unable to register the SPN for the instance. This will not prevent the instance from starting but you will see the following entry in the error log:
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
The alternative is to have a domain admin manually register the SPN for the instance. The format for an SPN is MSSQLSvc/FQDN:tcpport, where FQDN is the fully qualified domain name of the server and tcpport is the TCP/IP port number. To register the SPN, the administrator will need to use the SetSPN.exe tool which is available from the Windows server resource kit (for Windows 2K3, you can find it here, http://support.microsoft.com/default.aspx?scid=kb;en-us;892777). An example of the command is:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname
If an SPN already exists, then it must be deleted before it can be re-registered. This is accomplished by a domain admin using the setspn -D command.
To verify that Kerberos authentication is being used, you may query the sys.dm_exec_connections DMV and look under the auth_scheme column, e.g.
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
If Kerberos is being used, then it will display “KERBEROS”.
I should also mention that if the instance automatically registered an SPN at startup, then it will unregister it when the instance is stopped.
If instance is configured to listen to a different port, then the SPN will need to be deleted and recreated using the new port number. The problem is worse if the server is configured to use dynamic IP addresses as, potentially, a new SPN must be configured every time the server is started. I recommend that a static IP address be used so that the SPN need only be registered once. In addition, using a static IP address provides the additional benefit that the client can specify the TCP/IP directly in the connection string. Doing this will prevent the need to rely upon SQL Browser to determine the port number (SQL Browser communication uses an unauthenticated UDP channel).
A couple of other considerations that are documented in the BOL are (i) the Dedicated Admin Connection (DAC) only uses NTLM so no SPN is registered for the DAC connection, and (ii) if the instance is configured to listen to multiple IP addresses, then the server will only attempt to automatically register the SPN using the first port that it identifies.
Il-Sung LeeProgram Manager, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
We have the same SSPI issue and asked the system admin to add SPN to AD, which he did successfully. But we still get "Cannot generate SSPI context" error message.
Do we need to restart SQL Server service?
PingBack from http://sqldbpool.wordpress.com/2008/09/05/%e2%80%9ccannot-generate-sspi-context%e2%80%9d-error-message-more-comments-for-sql-server/
Hi, is there anything else should be done if client is a JDBC application?
Please refer to the documentation of the JDBC driver you are using. If you are using the Microsoft SQL Server 2005 JDBC Driver, Kerberos support is currently only supported if the JDBC application is running on a Windows machine. Please look at the "integratedSecurity" connection property at http://msdn.microsoft.com/en-us/library/ms378988(SQL.90).aspx for additional information.
I am getting error 0x20b5/8373 "Failed to assign SPN on account..."
Does anyone have suggestions for solving this issue?
I am getting the SSPI error only when users are connected to the Network via Cisco VPN. Would the fix mentioned above fix my problem?
The above statement works in SQL 2005. What is the way to know the auth scheme in SQL 2000 for connections.
Not using a system table or DMV.
There are several ways you can determine what is used. The easiest is to enable security auditing for successful logons on the server side, this will create an event log for each logon and this event log entry will tell you whether or not Kerberos was used.
This article tells you how to turn it on:
PingBack from http://www.keyongtech.com/2202082-spn-requirement
I'm using SQL Server 2008 for MOSS 2007. We're using Kerberos. Is there any reason why I shouldn't disable all protocols on the server side other than TCP/IP? There is no SQL Server internal requirements for the Shared Memory or Named Pipes protocols to be enabled are there?
We've successfully set up Kerberos on our SQL Servers by manually setting the SPN's for each. All of our SQL Servers are linked using window authentication. This all seems to be working correctly, however, we have a old VB6 app that runs under a domain account that executes a distributed query and we are getting an anonymous login error on the hop to the second sql server. This worked previously when the linked servers used a sql login to connect. Also, when running the query from the same machine the app runs on with the same credentials (from query analyzer) the query runs without any problems. Does anyone have any ideas?
Also, with manually registered SPN's... do they persist if the DC is rebooted? Is there any point at which manually registered SPN's need to be re-registered.
Hi Il-Sung Lee, thanks for such extreme good article!
Client Tier is Management Studio or Query Analyzer
Middle Tier x64 2003 SP1 - SQL 2000 SP4
Destination is x86 2003 SP1 - SQL 2000 SP4
SPN is set for Domain Service Account on middle tier & destination, TCP/IP is the only protocol enabled, linked server does not work on double-hop. However, if I remote to the middle tier and use linked server it works (obviously) and while that "ticket" is still live, I am able to successfully double-hop from the Client. What am I missing???
I am having double hop issue connecting linked server. So i registered it using the statement
setspn MSSQLSvc/machinename.redmond.corp.microsoft.com:1433 domainname\<account under which sql service runs>
when i execute--C:\Program Files\Support Tools>setspn -l domainname\<account under which sql service runs>
Registered ServicePrincipalNames for CN=ESM Nagbot 2000,OU=UserAccounts,DC=domainname,DC=corp,DC=<x>,DC=com:
The Query results NTLM still
I am still getting the error. We use SQL 2005. Can some one suggest.
Kiran, try with.
setspn MSSQLSvc.3/machinename.redmond.corp.microsoft.com:1433 domainname\<account under which sql service runs>
For SQL 2005 you need to use MSSQLSvc.3, to work properly.