Ever have one of those “I want to kick myself” when you realize something you had been putting off investigating would have saved you a bunch of time?  I did just the other day.

In my role, I am often trying to get SPNs configured properly for SQL Server and Reporting Services (and Analysis Services and IIS application pools on bad days).  Generally, I take the approach of knowing the five SPNs that are necessary (SQL NetBIOS, SQL NetBIOS:port, SQL FQDN, SQL FQDN:port, SSRS URL).  Then, if it still doesn’t work, I am reduced to doing setspn –l against the various service accounts looking for duplicates.

Generally, this is not too hard, but sometimes the list of SPNs is long and the customer uses a naming convention that appears to consist of 29 characters of which 27 are identical and the two ones that are different are scattered in the middle of the name.  Needless to say, this makes finding duplicates really difficult.

However, Windows 2008 shipped with a new and improved version of setspn.  Not only does it exist on every Vista/Windows 2008 or higher machine, but it has some handy new features.  If you run setspn from the command-line with no parameters, this is what you get:

Usage: setspn [modifiers switch] [accountname]
  Where "accountname" can be the name or domain\name
  of the target computer or user account

  Edit Mode Switches:
   -R = reset HOST ServicePrincipalName
    Usage:   setspn -R accountname
   -A = add arbitrary SPN 
    Usage:   setspn -A SPN accountname
   -S = add arbitrary SPN after verifying no duplicates exist
    Usage:   setspn -S SPN accountname
   -D = delete arbitrary SPN
    Usage:   setspn -D SPN accountname
   -L = list SPNs registered to target account
    Usage:   setspn [-L] accountname  

  Edit Mode Modifiers:
   -C = specify that accountname is a computer account
   -U = specify that accountname is a user account
    Note: -C and -U are exclusive.  If neither is specified, the tool
     will interpret accountname as a computer name if such a computer
     exists, and a user name if it does not.

  Query Mode Switches:
   -Q = query for existence of SPN
    Usage:   setspn -Q SPN
   -X = search for duplicate SPNs
    Usage:   setspn -X

    Note: searching for duplicates, especially forestwide, can take
     a long period of time and a large amount of memory.  -Q will execute
     on each target domain/forest.  -X will return duplicates that exist
     across all targets. SPNs are not required to be unique across forests,
     but duplicates can cause authentication issues when authenticating

  Query Mode Modifiers:
   -P = suppresses progress to the console and can be used when redirecting
    output to a file or when used in an unattended script.  There will be no
    output until the command is complete.
   -F = perform queries at the forest, rather than domain level
   -T = perform query on the specified domain or forest (when -F is also used)
    Usage:   setspn -T domain (switches and other parameters)
     "" or * can be used to indicate the current domain or forest.

    Note: these modifiers can be used with the -S switch in order to specify
     where the check for duplicates should be performed before adding the SPN.
    Note: -T can be specified multiple times.

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"
setspn -F -S http/daserver daserver1
   It will register SPN "http/daserver" for computer "daserver1 if no such SPN exists in the forest
setspn -U -A http/daserver dauser
   It will register SPN "http/daserver" for user account "dauser"
setspn -T * -T foo -X
   It will report all duplicate registration of SPNs in this domain and foo
setspn -T foo -F -Q */daserver
   It will find all SPNs of the form */daserver registered in the forest to which foo belongs

A little bit longer than the old list, huh?  :)

To be honest, I am still trying to figure out how to use the various new switches (and I cannot find any good documentation online).  However, the –x parameter caught my eye and indeed saved me a bunch of time the other day.

I had a customer who had started with using one service account and then changed their mind midway (or at least that is what we figured out after the fact :)).  No big deal, but they didn’t keep track of which SPNs they had already created.  Needless to say, we ended up with a bunch of duplicates.

Now comes the cool part.  All we had to do was run “setspn –x” from the command-line.  The output took about three minutes to generate, but the resulting list showed up exactly what duplicates we had.  As soon as my customer saw that list, they remembered they had started out with another account.  Life was easy after that…