2010-10-12

Automatic SPN registration

First read the blog entry „What SPN do I use and how does it get there?“ by the CSS SQL Server Engineers.
Then read the Knowledge Base article „How to use Kerberos authentication in SQL Server“ (KB319723).

All configuration I have done on a Domain Controller. It might be possible to use another server.
You have to be domain Administrator to do this.
Before configuration you can check by the commandline statement
setspn -l sqlaccount01
Which should give a output like
Registered ServicePrincipalNames for CN=SQLACCOUNT01,OU=DBA,DC=SQLADMIN,DC=LAN:

Then configure the SPN registration by „Step 3: Configure the SQL Server service to create SPNs dynamically“ in KB319723.
Restart the SQL Server database service.

Check the configuration by the command line statement
setspn -l sqlaccount01
This should give a output like
Registered ServicePrincipalNames for CN=SQLACCOUNT01,OU=DBA,DC=SQLADMIN,DC=LAN:
MSSQLSvc/SANDY.SQLADMIN.LAN:1433
MSSQLSvc/SANDY.SQLADMIN.LAN

When the database service is restarted two entries are made in the SQL Error Log
2010-09-08 07:06:05.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN ] for the SQL Server service.
2010-09-08 07:06:05.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN:1433 ] for the SQL Server service.


This is a manual process that should be automated...

1 comment:

narayana p said...

It's so nice article thank you for sharing a valuable content. SQL server dba Online Training Bangalore