SPN - Service Principal Name Registration

SPN is a attribute of an Active Directory computer account. It ties together the machine name, a service account and the port number.

We need a SPN established to allow Kerberos authentication with SQL.
Kerberos allows pass through authentication eg user > web server > sql server

You want to create an spn for a sql instance when the account that sql server runs from does not have rights to create an SPN (most cases really, in secure environments).

To manually configure an SPN you need 'setspn', which is part of 'Windows 2000 / 2003 Resource Kit'. Once installed >
C:\Program Files\Resource Kit>setspn
Usage: setspn [switches data] computername Where "computername" can be the name or domain\name

Switches: -R = reset HOST ServicePrincipalName Usage: setspn -R computername
-A = add arbitrary SPN Usage: setspn -A SPN computername
-D = delete arbitrary SPN Usage: setspn -D SPN computername
-L = list registered SPNs Usage: setspn [-L] computername

Examples:
setspn -R daserver1 It will register SPN "HOST/daserver1" and "HOST/{DNS of daserver1}"
setspn -A http/daserver daserver1 It will register SPN "http/daserver" for computer "daserver1"
setspn -D http/daserver daserver1 It will delete SPN "http/daserver" for computer "daserver1"

Adding a SPN > setspn - A SERVICENAME/FQDN serviceaccount
C:\Program Files\Resource Kit>setspn -A MSSQLSvc/livesql001.domain.co.uk SqlServiceAccount
Registering ServicePrincipalNames for CN=SqlServiceAccount,OU=Service Accounts,DC=domain,DC=co,DC=uk MSSQLSvc/livesql001.domain.co.uk Updated object
C:\Program Files\Resource Kit>setspn -A MSSQLSvc/livesql001.domain.co.uk:1433 SqlServiceAccount Registering ServicePrincipalNames for CN=SqlServiceAccount,OU=Service Accounts,DC=domain,DC=co,DC=uk MSSQLSvc/livesql001.domain.co.uk:1433 Updated object

Checking an SPN > setspn -l serviceaccount
C:\Program Files\Resource Kit>setspn -l SqlServiceAccount
Registered ServicePrincipalNames for CN=SqlServiceAccount,OU=Service Accounts,DC=domain,DC=co,DC=uk: MSSQLSvc/livesql001.domain.co.uk:1433 MSSQLSvc/livesql001.domain.co.uk MSSQLSvc/livesql002.domain.co.uk:1433 MSSQLSvc/testsql002.domain.co.uk:1433 MSSQLSvc/livesql002:1433 MSSQLSvc/devsql001.domain.co.uk:1433

You can also check an spn via adsi edit (Active Directory editor tool).

Technet explanation on SPNs >
http://technet2.microsoft.com/windowsserver/en/library/8127f5ed-4e05-4822-bfa9-402ceede47441033.mspx?mfr=true

So, SPN ties together -

• machine name
• port number
• account name

for active directory.