When setting up a new SQL Server, one of the important step is to register the SPN of the service account.This registration is not required if the service account is domain administrator or if you give explicit permissions to self register the SPN for the service account. Both options are not wise, so anytime a new SQL Server is set up or service account is changed – we have to manually register the SPN on the domain. This task needs to be done by someone who has domain admin rights.
Registering SPN’s enables kerberos authentication for delegation and for double hop scenarios such as linked server, you can impersonate the actual user other wise you have to specify SQL Account and this can become security loophole in your system.
Below are the steps to enable kerberos delegation:
1. Register SPN for serviceaccount with all possible combinations
SetSPN -A MssqlSvc\ComputerA.domainname.com domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA.domainname.com:1433 domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA:1433 domain\serviceaccount
In case of a clustered instance, specify the Virtual SQL Cluster Name(without the instance name). You have to mention the TCP port the SQL is running on.
2. Enable the service account to trust for delegation. This is a setting in the AD.You can choose either to trust for all delegation or if you want, you can specify which service to delegate.
3. Make sure TCP/IP protocol is enabled and named pipes is disabled.
If you have any other SQL components such as Analysis service or Reporting service, you can register them as well to use Kerberos.
Example of SSRS SPN registration:
http/computername.domainname.com:80 domainname\serviceaccount
http/computername.domainname.com domainname\serviceaccount
http/computername domainname\serviceaccount
http/computername:80 domainname\serviceaccount
If you use performance dashboard reports, you need to have kerberos authentication for SSRS. These dashboards are very useful and you can download from github.
Also, If you want to list all the SPN’s registered for a service account, you can use
SetSPN -L domainname\serviceaccount
If you want to delete a spn, you can use
SetSPN -D MssqlSvc\ComputerA.domainname.com domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA.domainname.com:1433 domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA:1433 domain\serviceaccount
SQL Server Repository 