Changing the service account credentials in SQL Server

Like most people, when I first start out setting up something new I take the defaults, and that usually is good enough.  Then down the road, while learning more about how the product works, I discover that the defaults don't cut it in some cases.  Microsoft SQL Server is one example.  Out of the box it defaults to using a local account for its services, "nt service\mssql$instancename".  That works fine until you start doing stuff like replication, mirroring, or interacting with active directory with impersonation.  These functions need a domain account, either to share credentials, or to have access to read active directory.

Seems simple enough -- go into the SQL Server Configuration Manager, change the old local account name associated with the service ("nt service\mssql$prod") to a domain account ("ad1\mssql$prod"), and set the account password.  I had previously set up another instance of sql server with this new account, so I know that it was granted all the appropriate permissions at the domain level, and even at the machine level.   Should be a no-brainer to switch over to it right?  Nope.  When you do that, and restart SQL server, it seems fine (locally) until you try and connect from another machine.  Then you get this dreaded error.

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)

Nothing shows up in the sqlserver log files (server side).  Client side, maybe, but I couldn't easily find it.  After googling for what felt like an eternity I stumbled across a forum post that said to do this from an elevated command prompt:
setspn -a mssqlsvc/12data4.ad1 mssql$prod
This fixed it immediately for the entire domain.  It has something to do with kerberos black magic.  So there you have it, I hope this post saves you some time and effort.  It seems to me that changing the account via SQL Server Configuration Manager ought to do this for you, but alas it does not?


Popular posts from this blog

PRB: Windows 8, MSXML4 Application Error 429: ActiveX component can't create object

Installing OTRS 4.0 on Ubuntu Linux 14.04 with a MSSQL Backend

Getting Started with GitHub and Visual Studio 2017