| Author |
Topic |
|
homebrew
Posting Yak Master
114 Posts |
 Posted - 2011-02-10 : 12:44:35
|
We want to rollout a new account to use for SQL Services. On a Dev server (SQL 2005 SP3, Windows Server 2003 SP2), I changed the account through SQL Configuration Manager. After restarting the services, from my remote desktop, I get an error "Cannot Generate SSPI Context" when I try to connect with Windows Authentication. 'sa' connects fine remotely, and Windows authentication is fine if I log on to the server and open SQL there.I've tried various combinations of changing services to LocalSystem, then back to the new account, rebooting between changes, doing steps in different sequence. Once or twice, it has then worked, but when I try later, I get the error again.I just changed the Service account to LocalSystem, restarted, changed to my new SQL Service Account, restarted, and it worked - I could connect.Then I rebooted the server and got the error again ?!?!?! Back to square 1  Is there a "best practice" list for changing SQL services to a new account ? MS documentation basically says just change in through Configuration Manager, nothing about "gotchas" or special steps. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2011-02-10 : 15:04:20
|
| Thanks ... that's a little over my head, but I'll see what I can do.Any idea how the problem came about froma simple little account change ? |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-10 : 16:03:06
|
| When SQL Server is run using the LocalSystem account, an SPN record is created for the machine. The machine has the explicit rights to add/edit SPN records.When you changed the service account to a domain account, that domain account does not have privileges to add/edit SPN records. This not only left an invalid SPN record out there (the one associated with the local system account), but prevented SQL Server from creating the new SPN record.An SPN record is needed for Kerberos authentication to work. If the SPN record exists, but is related to a different account then you will get an error. If there is no SPN record, then the connection will drop to NTLM authentication.There are two ways to fix this issue:1) Manually remove the invalid SPN record using the SetSPN utility. You can also manually create the new SPN record(s) as needed.2) Set the privileges on the domain account so it has the rights to add/edit SPN records.Jeff |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-11 : 10:30:11
|
| Also, you need to be a domain admin to set the SPN |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-11 : 15:54:50
|
| Russell - you do not need to be a domain admin to set SPN records. You can have a normal user account that has been granted privileges to read/write SPN records. This is how you would define your service accounts so they can manage the SPN records automatically.Jeff |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-11 : 17:08:26
|
| True. A domain admin can delegate authority to setspn to a non-domain admin user.But it is pretty unlikely that someone who doesn't yet know to set it, has been granted the authority. |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2011-02-14 : 10:26:09
|
| Is this an expected situation when changing the SQL Service account ?Or did I do something wrong initially ?I'd like to avoid any problems when I do the production servers. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-14 : 16:04:20
|
| If you are changing from a privileged account to a non-privileged account, then the privileged account has the rights to create/edit SPN records. Once you change to the non-privileged account those rights no longer exist.In this situation, you will have an orphaned SPN record that is invalid for the new account. This will cause the above error for windows authentication.Yes, it is an expected situation - and no, it is not generally something that people are aware of until it bites them...:)Jeff |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2011-02-15 : 12:57:02
|
| I think both the old Services Account and the new Services Account are privileged accounts .... The new account is a local Admin on the server. What's the definition of "privileged" ? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-15 : 15:50:31
|
| http://technet.microsoft.com/en-us/library/cc772895(WS.10).aspxhttp://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-15 : 16:33:27
|
| The definition of a privileged account is one that has domain level privileges - not local privileges on that server.BTW - you really should not have your service accounts set up as local administrator. This is not recommended and is not needed for SQL Server to run. When you change the service account using SQL Server Configuration Manager, it will add that service account to the appropriate local groups and grant all of the rights needed to run SQL Server.Jeff |
|
|
|
|
New SQL Service Acct: Cannot Generate SSPI Context