FAQs Around Kerberos and SQL Server

[MarkSQLSAP]

First published on MSDN on May 03, 2013

Recommended Audience: Developers, DB Administrators and System Administrators

Estimated Depth Level: 200 {100 easiest, 400 hardest}

This blog post is a rehashing of information that can be found in other locations on the web in regards to Kerberos and SQL Server.  In our SAP world we occasionally encounter issues related to this.  The intention here is just as a smaller repository of things from our perspective.

Questions covered in this post

1) What is Kerberos?

2) Why does SQL Server need and use Kerberos?

3) What does SQL Server use it for?

4) How does it work?

5) When did SQL Server first begin to use Kerberos?

6) What components are necessary for it to work?

7) How do you know it is being used?

8) Other miscellaneous things

A) What is meant by SPN?

B) What happens on stopping an auto-registered SPN?

C) What happens when port numbers are changed?

D) Does the DAC use an SPN?

E) What about SPN registration on multiple IPs?

F) What permissions are needed to register SPNs?

G) What happens during SQL Server startup?

H) What about MSAs and Domains Less than Windows 2008 R2?

I) SPN formats for SQL Server 2008 and onward?

J) SQL Server version differences?

9) Any unique things in regards to Clusters?

10) What errors can be seen, and how to fix them?

11) How do I troubleshoot it when it doesn’t work?

Resources
for this information

Answers

1) What is Kerberos?

http://en.wikipedia.org/wiki/Kerberos_(protocol)

Kerberos i s a computer network authentication protocol which works on the basis of "tickets" to allow nodes communicating over a non-secure network to prove their identity to one another in a secure manner. Its designers aimed primarily at a client–server model, and it provides mutual authentication —both the user and the server verify each other's identity. Kerberos protocol messages are protected against eavesdropping and replay attacks . Kerberos builds on symmetric key cryptography and requires a trusted third party , and optionally may use public-key cryptography during certain phases of authentication. ^[1] Kerberos uses port 88 by default.

Books Online (BOL)

Kerberos is a network authentication protocol that provides a highly secure method to authenticate client and server entities (security principals) on a network. These security principals use authentication that is based on master keys and encrypted  tickets.

In the Kerberos protocol model, every client/server connection begins with authentication.  Client and server, in turn, step through a sequence of actions designed to verify to the party on each end of the connection that the party on the other end is genuine. If authentication is successful, session setup completes and a secure client/server session is established.

Among the key benefits of Kerberos authentication are:

• Mutual authentication . The client can validate the identity of the server principal, and the server can validate the client. Throughout this documentation, the two entities are called the "client" and the "server" even though secure network connections can be made between servers.

• Secure authentication tickets . Only encrypted tickets are used, and passwords are never included in the ticket.

• Integrated authentication . Once a user is logged on, he does not need to log on again to access any service that supports Kerberos authentication as long as the client ticket has not expired. Every ticket has a lifetime, which is determined by the policies of the Kerberos realm that generates the ticket.

Kerberos provides a mechanism for mutual authentication between entities before a secure network connection is established. Kerberos uses a trusted third party,  the Key Distribution Center (KDC), to facilitate the generation and secure distribution of authentication tickets and symmetric session keys. The KDC runs as a service on a secure server and maintains a database for all the security principals in its realm. In the Kerberos context, a realm is the equivalent to a Windows domain.

2)      Why does SQL Server need and use Kerberos?

BOL

SQL Server supports Kerberos indirectly through the Windows Security Support Provider Interface (SSPI) when SQL Server is using Windows Authentication. SSPI allows an application to use various security models available on a computer or network without changing the interface to the security system.

SQL Server allows SSPI to negotiate the authentication protocol to use; if Kerberos cannot be used, Windows will fall back to Windows NT Challenge/Response (NTLM) authentication.  The Security Principal Name (SPN) is sent to the Key Distribution Center to obtain a security token for authenticating the connection.

SQL Server 2008 supports Kerberos authentication on the following protocols:

• TCP/IP


• Named pipes


• Shared memory

As a best practice, we recommend that you use Kerberos authentication whenever possible for connections to an instance of SQL Server.

3)      What does SQL Server use it for?

BOL

Kerberos is used for mutual authentication between entities before a secure network connection is established.

4)      How does it work?

BOL

When an application opens a connection and uses Windows Authentication, SQL Server Native Client passes the SQL Server server name, instance name, and optionally an SPN. If the connection passes an SPN it is used without any changes.

If the connection does not pass an SPN, a default SPN is constructed based on the protocol used, server name, and the instance name.

In both of the preceding scenarios, the SPN is sent to the Key Distribution Center to obtain a security token for authenticating the connection. If a security token cannot be obtained, authentication uses NTLM.

MSDN http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx

In a Windows environment, the operation of the KDC is assumed by the Domain Controller (DC) using the Active Directory (AD).

All Windows domain users are effectively Kerberos principals and are capable of engaging in Kerberos authentication.

5)      When did SQL Server first begin to use Kerberos?

Kerberos was first started to be used on SQL Server 2000 running on Windows 2000 and later domains  {2000 SP3 and later is needed to support Kerberos on Windows Clusters}

MSDN http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx

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.

http://support.microsoft.com/kb/319723

You can use Kerberos authentication with Microsoft SQL Server 2000. SQL Server 2000 supports this functionality as part of a typical Microsoft Windows 2000 or Microsoft Windows Server 2003 Active Directory domain installation. With Microsoft Windows 2000 Service Pack 3 (SP3) and Windows Server 2003, you can enable Kerberos authentication on server clusters.

6)      What components are necessary for it to work?

BOL

To use Kerberos authentication with SQL Server the following conditions are required:

• The client and server computers must be part of the same Windows domain, or in trusted domains.


• The server’s Service Principal Name (SPN) must be registered with Active Directory.  This assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

FOR SQL Server 2005 {and SQL Server 2000)

The instance of SQL Server 2005 must enable the TCP/IP protocol.

The client must connect to the instance of SQL Server 2005 using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client's protocol order. Or you can add the prefix "tcp:" in the connection string to specify that the connection will use the TCP/IP protocol.

For SQL Server 2008 and onward, this requirement is relaxed because both Named Pipes and Shared memory protocols are supported

The timing of all components (Server, Client, and Active Directory) must remain in time synch with each other, for there is a real time component to the operation of Kerberos. In excess of 5 minutes and the Kerberos certificates will expire.   Inspecting of the Windows Application event logs should provide notice of that situation.

NOTE: If the server cannot automatically register the SPN, the SPN must be registered manually.

In Summary what is needed:

1. Active Directory (AD) and a viable Windows Domain Controller (DC).


2. The functioning role of a Key Distribution Center (KDC) in the domain.


3. Existence of SQL Server service and client in either the same or trusted domains.


4. Viable SPNs for the SQL Server service and client or application owning user within AD.


5. Time consistency across Client, Server, and authenticating Domain Controller in AD.

7)      How do you know it is being used?

BOL

You can verify that a connection is using Kerberos by querying the sys.dm_exec_connections dynamic management view.

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

If Kerberos is being used, then it will display “KERBEROS”.

8)      Other miscellaneous things

A) What is meant by SPN?

It appears to be an overloaded term that can mean either “Server”, “Service”,  or “Security”  Principal Name.  It is used to describe entries in a network domain, which in the Microsoft Windows platform would be the Active Directory (AD) Domain

The following are from:

MSDN http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx

B) What happens on stopping a service in regards to auto-registered SPNs?

If the instance automatically registered an SPN at startup, then it will attempt to unregister it when the instance is stopped.

C) What happens when port numbers are changed?

If a SQL Server instance has its configuration changed 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.  For this reason it is beneficial to use a static IP address 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).

D) Does the DAC use an SPN?

Dedicated Admin Connection (DAC) only uses NTLM so no SPN is registered for the DAC connection.

E) What about SPN registration of SQL Server listening on multiple IPs?

If the SQL Server 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.   Manual registration should be used for multiple port registration.

And the following are from:

http://support.microsoft.com/kb/319723

F) What permissions are needed to register SPNs?

If you want the Domain user account that owns the SQL Server service to automatically register SPNs dynamically, the following access control permissions must be set in Active Directory for the service account:

• Read servicePrincipalName


• Write servicePrincipalName

Details for manually creating SPNs can be found in this KBA

http://msdn.microsoft.com/en-us/library/ms191153.aspx

G) What happens during SQL Server startup?

When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended), or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account (also discouraged).  When SQL Server is running on the Windows 7 or Windows Server 2008 R2 operating system, you can run SQL Server using a virtual account or a managed service account (MSA).   More about virtual accounts and MSAs in Windows can be found here:

http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx

Both virtual accounts and MSAs can register an SPN.  If SQL Server is not running under one of these accounts or a properly permissioned account, the SPN may not be registered at startup and the domain administrator would have to
register the SPN manually  {see 11 a below}.

H) What about MSAs and Domains Less than Windows 2008 R2?

When the Windows domain is configured to run at less than the Windows Server 2008 R2 functional level, then the Managed Service Account will not have the necessary permissions to register the SPNs for the SQL Server Database Engine service. If Kerberos authentication is required, the Domain Administrator should manually register the SQL Server SPNs on the Managed Service Account {again see 11 a below}.

I) SPN formats for SQL Server 2008 and onward?

Further information about Automatic SPN Registration.  Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory.  As of SQL 2008, the supported SPN formats for named and default instances are as follows.

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is
registered in the format:

MSSQLSvc/<FQDN> : <tcpport> .

Both named instances and the default instance are registered as MSSQLSvc , relying on the <tcpport> value to differentiate the instances.

For other connections that support Kerberos the SPN is registered in the format:

MSSQLSvc/<FQDN> : <instancename>

for a named instance.

The format for registering the default instance is:

MSSQLSvc/<FQDN>  or MSSQLSvc/<FQDN>:<tcpport> .

Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions.

J) SQL Server version differences?

Regarding version differences and referencing

http://msdn.microsoft.com/en-us/library/cc280459(v=SQL.100).aspx

and SQL Server 2012 BOL

SQL 2008 provided support for named pipes and shared memory protocols

SQL 2012 enables per-service SID for each of its services to provide service isolation and defense in depth.

Additionally, there are changes that have been shipped with WINDOWS 2012:

• http://technet.microsoft.com/en-us/library/hh831472.aspx


• http://technet.microsoft.com/en-us/library/hh831553.aspx


• http://technet.microsoft.com/en-us/library/hh831747.aspx

9)      Any unique things in regards to Clusters?

http://support.microsoft.com/kb/319723

We recommend that you do not grant WriteServicePrincipalName right to the SQL service account when the following conditions are true:

• There are multiple domain controllers.


• SQL Server is clustered.

In this scenario, the SPN for the SQL Server may be deleted because of latency in Active Directory replication.  This may cause connectivity issues to the SQL Server instance.



Assume that you have the following:

• A SQL virtual instance named Sqlcluster with two nodes: Node A and Node B.


• Node A is authenticated by domain controller A and Node B is authenticated by domain controller B.

The following may occur:

The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up.

The Sqlcluster instance fails over to Node B when Node A is shutdown normally.

The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.

The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.

When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B.  Since, the SPN still exists Node B does not register the SPN.

After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Note This issue is fixed in SQL Server 2012.

10)   What errors can be seen, and how to fix them?

PROBLEM: Server SPN not being registered properly in AD

SYMPTOMS: Error: 0x2098 or 0x54b or Event ID: 26037

This will not prevent the instance from starting but you will see the following entry in the SQL Server ERRORLOG:

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.

Error: 0x54b and Event ID: 26037 are the same thing as the message here, but are just how the error is recorded in the Windows Application Event Log.

SOLUTION

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 or http://support.microsoft.com/kb/909801 ).  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.

REFERENCE

MSDN http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx

A bit more information on manually registering SPNs can
be found here: http://msdn.microsoft.com/en-us/library/ms191153.aspx

To
register the SPN manually, the administrator must use the Setspn.exe tool that is provided with the Microsoft Windows Server 2003 Support Tools. For more information, see the Windows Server 2003 Service Pack1 Support Tools KB article.

Setspn.exe is a command line tool that enables you to read, modify, and delete the Service Principal Names (SPN) directory property. This tool also enables you to view the current SPNs, reset the account's default SPNs, and add or delete supplemental SPNs.

For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN> : <tcpport> .Both named instances and the default instance are registered as MSSQLSvc , relying on the <tcpport> value to differentiate the instances.

For other connections that support Kerberos the SPN is registered in the format

MSSQLSvc/<FQDN> : <instancename>

for a named instance. The format for registering the default instance is

MSSQLSvc/<FQDN> .

The following example illustrates the syntax used to register manually register an SPN for a TCP/IP connection

setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname

or The following examples illustrate how to manually register a new instance-based SPN. For a default instance, use:

setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountname

or for a Named instance

setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname

11)   How do I troubleshoot it when it doesn’t work?

A) On SQL Server try the following {to inspect if KERBEROS is being used and what programs are using it}:

SELECT COUNT(*) as [Count], auth_scheme, net_transport

FROM sys.dm_exec_connections

WHERE session_id > 50 GROUP BY auth_scheme, net_transport

SELECT DISTINCT

c.auth_scheme, c.net_transport, s.host_name, s.program_name,

s.client_interface_name, s.login_name, s.nt_domain, s.nt_user_name

FROM sys.dm_exec_sessions s

JOIN sys.dm_exec_connections c

ON s.session_id = c.session_id

WHERE s.session_id > 50

B) It may be helpful to obtain a list of SPNs from a particular Active Directory server using LDAP and the ldifde command from the command line, as described at the end of this note:

http://support.microsoft.com/kb/319723

C) There is an internal Microsoft utility that might be available for assistance called KerberosConfigMgr.  Contact support (either Microsoft SQL Server CSS or SAP and the BC-DB-MSS work queue) if this would be necessary to further troubleshoot your problems.

Resources for this information:

BOL

SQL Server 2008 R2

MSDN

Using
Kerb