Welcome back to both our Kerberos coverage and to another topic for SQL University's Security and Auditing Week. In today's lesson we're going to cast some light on what is likely the most used tool in managing Service Principal Names (SPNs) for Kerberos: SETSPN. If you're not familiar with SETSPN, the following link will provide you with the means of obtaining the tool. It is a command line utlity because there's really no reason for a GUI one.

Now here's where I pause and tell you that what you can do with the tool will depend on your rights within Active Directory. Two of the switchs, -A to add an SPN and -D to delete an SPN, require that you be a member of the Domain Admins group or that you've been delegated the rights to modify the account (computer or user) to which the SPN is being added. More on this in a minute. Most DBAs and database developers don't have this sort of access. However, you do have the ability to use the -L switch, which lists out all SPNs belonging to a particular account. Since that's what most folks have, let's look at that:

SETSPN -L:

The syntax for the -L switch is SETSPN -L *Account*. So if I wanted to see all services registered to my computer's account (BrianPC in the Contoso.com domain), I would issue:

SETSPN -L BrianPC

Here's what I get back:

Registered ServicePrincipalNames for CN=BrianPC, OU=Workstations,DC=contoso,DC=com:

        TERMSRV/BrianPC.contoso.com
        TERMSRV/BrianPC
        RestrictedKrbHost/BrianPC
        HOST/BrianPC
        RestrictedKrbHost/BrianPC.contoso.com
        HOST/BrianPC.contoso.com

These are all registered for me and I see two service principal name types of interest: TERMSRV and HOST. TERMSRV tells me I can RDP into the computer. HOST is the generic SPN for every computer in the domain.

But what if I wanted to find out if I had the correct SPNs for my SQL Server instance? Here the account would be the service account SQL Server runs under. For instance, if my SQL Server ran under the account CONTOSO\SQLSvc:

SETSPN -L SQLSvc

And the results I'd get back are:

Registered ServicePrincipalNames for CN=SQLSvc,OU=Users,DC=contoso,DC=com:

        MSSQLSvc/SQLServer01.contoso.com
        MSSQLSvc/SQLServer01
        MSSQLSvc/SQLServer01.contoso.com:1433
        MSSQLSvc/SQLServer01:1433

If you don't recognize the MSSQLSvc/*, look part to Part II to understand how the SPNs are written.

So why should you care? Quite simply, because you can use the tool to determine if the SPNs were set up correctly. Most of the time the creation of SPNs is handed off to the team that administers the domain. As a result, a lot of DBAs and database developers aren't the ones who do the work. Using the SETSPN tool allows you to verify it's not an SPN setup issue by use of the -L switch. Even though I no longer sit as a domain admin in my organization because I changed roles to be a full time DBA again, I still use SETSPN to check to make sure the team responsible puts the SPNs in correctly when I request them.

SETSPN -A:

If you have the appropriate permissions in Active Directory, you can add an SPN to permit Kerberos authentication to your SQL Servers. The key is to get the service part right. Again, this is talked about quite a bit in Part II.

• Database Engine: MSSQLSvc
• Analysis Services: MSOLAPSvc.3
• Reporting Services: HTTP

Specific instructions on how to add SPNs for each of these services can be found at:

• Database Engine - Registering a Service Principal Name
• Analysis Services - How to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication
• Reporting Services - How to: Register a Service Principal Name (SPN) for a Report Server

If I wanted to create the SPNs listed above, I'd issue the following commands:

SETSPN -A MSSQLSvc/SQLServer01.contoso.com SQLSvc

SETSPN -A MSSQLSvc/SQLServer01 SQLSvc
SETSPN -A MSSQLSvc/SQLServer01.contoso.com:1433 SQLSvc
SETSPN -A MSSQLSvc/SQLServer01:1433 SQLSvc

SETSPN -D:

There are rare instances when you might run across the need to delete a service principal name. Again, the rights you have in Active Directory are important. You need the same rights to delete an SPN as you would to add one. The format is the same as for the -A switch, except you're using -D. For instance, let's consider that Internal Audit came by and said, "The use of SQLSvc as the service account is too obvious for an attacker. You need to change the service account name!" In order to ensure that I had the proper configuration for Kerberos authentication, I would have to ensure I didn't have any duplicate SPNs. So I would need to get rid of the ones assigned to SQLSvc. I would do this with the following commands:

SETSPN -D MSSQLSvc/SQLServer01.contoso.com SQLSvc

SETSPN -D MSSQLSvc/SQLServer01 SQLSvc
SETSPN -D MSSQLSvc/SQLServer01.contoso.com:1433 SQLSvc
SETSPN -D MSSQLSvc/SQLServer01:1433 SQLSvc

What about the other switches?

There are some other useful switches, but they aren't present in all versions of SETSPN. If you're interested in these other switches, I suggest you check the SETSPN documentation or simply execute:

SETSPN -?