SetSPN for SQL

Default Instance: Default SQL Install

setspn -A MSSQLSvc/SERVERNAME:PORTNUMBER DOMAIN\SERVICEACCT
setspn -A MSSQLSvc/SERVERNAME.FQDN:PORTNUMBER DOMAIN\SERVICEACCT


Non-Clustered Named Instance:

setspn -A MSSQLSvc/SERVERNAME:PORTNUMBER DOMAIN\SERVICEACCT
setspn -A MSSQLSvc/SERVERNAME.FQDN:PORTNUMBER DOMAIN\SERVICEACCT
setspn -A MSSQLSvc/SERVERNAME.FQDN DOMAIN\SERVICEACCT

Clustered Named Instance:

setspn -A MSSQLSvc/SERVERNAME:PORTNUMBER DOMAIN\SERVICEACCT
setspn -A MSSQLSvc/SERVERNAME.FQDN:PORTNUMBER DOMAIN\SERVICEACCT
setspn -A MSSQLSvc/SERVERNAME.FQDN DOMAIN\SERVICEACCT

By default the PORTNUMBER for SQL is 1433. If there are multiple instances of SQL running on the same box, one will normally be the default of 1433 and the others will choose dynamically assigned ports. These do not change on reboots. To find the assigned ports you will want to look in the following key:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.LYNC\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

The highlighted section above will be different for each instance. I have highlighted the port number below.

Tips:

  • If you have named pipes enabled you will have to connect to a server using “tcp:servername,portnumber”,  “tcp:clustervirtualname\instance,portnumber” or disable named pipes.
  • If you want to check if a service account is already associated to a set of SPN’s, run setspn -L DOMAIN\SERVICEACCT.
  • If you want to check for duplicate SPN’s run setspn -X.
  • When you need to delete an SPN use replace the -A above with –D.

Once you have completed the setspn commands it adds a delegation tab to both the server and the service account. On each, go to the delegation tab and click on Trust this (computer/user) for delegation to any service (Kerberos only).

One thought on “SetSPN for SQL

Leave a Reply

Your email address will not be published. Required fields are marked *