Sunday, July 15, 2007

There are multiple accounts with name MSSQLSvc

Having nothing better to do over the Christmas / New Year break (yeah right) we thought we might enjoy a good swing. A swing migration that is. Firstly, all credit to Jeff Middleton for his excellent guide on how to get a new SBS server working without disrupting and old SBS server. It doesn't even have to be an SBS server but we know that it works with SBS. For information about the process see www.sbsmigration.com.

Our only criticism of Jeff's work is that although it is very through we found it a little disjoined. To overcome this we developed our set of custom migration notes that made it clear for dummies like us. During this process we have come across a few interesting tips and issues that aren't mentioned in Jeff's notes.

The first of these is the following error that started appearing in the logs :

There are multiple accounts with name MSSQLSvc/<domain server>:1433 of type DS_SERVICE_PRINCIPAL_NAME.

Turns out what happens was that our initial SBS Premium server ( with SQL 2000 installed ) was set to run the SQL services under a specific user. However, when we installed the new SBS server we set up the new version of SQL to run under another account. This means that two different users in the Active Directory think that they in control the SQL service accounts generating the above error.

The fix is pretty simple. Use the setspn utility to check the accounts and then change the setting so only one is registered for SQL.

Use

setspn -l account1

&

setspn -l account2

to check that both accounts were registered for mssqlsvc. Now use

setspn -d mssqlsvc/<server.domain name>:1433 account2

to remove the suplicate SPN from the second account (in this case account2).

So for all you SBS swingers out there keep this in mind if you are planning to change the service accounts logins for accounts during migration.