Working with SPN's and SQL Server

In this post I will address SPN's and the relationship they have with SQL Server.

What are SPNs?

A service principal name (SPN) is the name by which a client uniquely identifies an instance of a computer account or service that runs on the computer account. The Kerberos authentication service can use an SPN to authenticate a service. When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.

Where are SPN's stored?

SPN's are stored on the computer account itself in Active Directory. You can register or view SPN's using the setspn.exe tool from the windows 2003 support tools pack which can be downloaded from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en

You can use this tool to view SPN's associated with a server by typing:

setspn -l servername



Notice in the above screenshot that not all servers have SPN's. By default no server will have an SPN. Some applications automatically register an SPN record for the computer account (only if the application runs as a domain admin account). Other times you will need to create SPN's manually using the setspn.exe utility.

To allow computers to dynamically create their own SPN please read Microsoft KB 319723 (specifically Step 3). You need to grant "SELF" permissions to a few attributes in the schema.:

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

Also in the example above, if you see it say "HOST/computer name", this means the SPN references the entire computer object for kerberos authentication. If you see it say "SOMETHING ELSE/computer name" it means its registered specifically to a service.

The SPN is stored on the computer account objects themselves under an attribute called "servicePrincipalName":



Please note SPN's can also be used for user accounts!

How can I use SPN's for client connections into my SQL Server?

In SQL you create "Logins" used for authentication under the security container in SQL Management Studio. When creating Logins you can use windows authentication or SQL authentication accounts. With windows authentication you can only use the following methods for authentication:
- User accounts
- Group accounts
- Service Principal Names

The following screenshot shows this:



Service accounts can be used as an SPN. They are specified through the connection attribute for the Kerberos authentication and take the following formats:

• username@domain or domain\username for a domain user account

• machine$@domain or host\FQDN for a computer domain account such as Local System or NETWORK SERVICES.

Here is an account in SQL using an SPN for a computer domain account:



The SQL Server Itself

The SQL Server itself also needs SPN's registered for all its services. For some additional reading please look at:

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

You can configure automatic registration of SPN's for SQL service accounts. If your interested in doing this please see this blog post I wrote:

http://clintboessen.blogspot.com/2010/02/dynamically-set-spns-for-sql-service.html