Log et al – Peter Curd

An irreverent peek into the inner rumblings of Peter Curd



Renaming Microsoft SQL Server servers and the effects on SPNs

By pcurd

Last week I saw a post on Simon Sabin’s blog about SQL Server service accounts and SPNs and made a comment about the importance of SPNs when renaming a SQL Server or migrating several servers into .. less than several. I felt this an area worth expanding a little more.

I’ll describe a hypothetical SQL Server infrastructure, changes to be made and how I would resolve the Kerberos issues that would result.

The base infrastructure:

A simple SQL Server environment with two Microsoft SQL Servers – SQLA and SQLB. The dataset on these two servers is different, no databases are shared. The domain for this company is “example” and the DC is “example.com” and so their usernames are formed “example/UserName”.

The new infrastructure:

A new SQL Server is purchased with power enough to run the entire dataset and it is to be called SQLA. The service account is to be a domain user called “sqlservice”.

However, there are a lot of applications that link to SQLB by name and recoding them all is considered too much work. (A classic example is Access which doesn’t make changing the source of tables easy without relinking)

A solution:

Migrate the total dataset to the new server, assign it a name of SQLA and take account of Simon’s SPN advice – i.e. use Network Service or a domain account to run the SQL Service. Use DNS to create a record for “SQLB” pointing to SQLA. If you want to be really fancy, assign the original IP address of SQLB as an additional IP on SQLA.

The result:

Connecting to “SQLA” via NTLM, SQL Database logins (assuming they were migrated too) and Kerberos works fine – as you’d expect. However, connecting to “SQLB” only works for NTLM and SQL Database logins – Kerberos fails along the lines of “Cannot generate SSPI Context”.

The solution:

As you may have guessed from the title of this article, the solution lies with SPNs. When you register for a Kerberos token you are doing so against a server name – and in this case, when talking to “SQLB”, that server name is wrong. So how does one send the correct name? Well in the example above, you can’t. You are stuck sending “SQLB”. The only solution is to make the name not wrong. To do this, we register another SPN against SQLA – effectively allowing it to understand and use the tokens made against “SQLB”.

The syntax is along these lines: (Please note, written from memory so some tweaking may be in order)

SetSPN -a mssqlsrv/SQLB.example.com:1433 example\sqlservice
SetSPN -a mssqlsrv/SQLB.example.com example\sqlservice
SetSPN -a host/SQLB.example.com
SetSPN -a host/SQLB

More details on SetSPN can be found on MSDN at http://msdn.microsoft.com/en-us/library/ms178119.aspx (Registering Kerberos Service Principle Names by Using Http.sys).

2 Responses so far

Log et al – Peter Curd » Renaming Microsoft SQL Server servers and the effects on SPNs…

Kudos for a great Sql Server article – Trackback from SqlServerKudos…

[…] names are wrong, however I had checked this and knew it was not an authentication issue. See my post on Server Principal Names for more details on this. Plus I was able to add other users. The error was “Error 15401 […]

Leave a comment