Managed Service Accounts and Cannot Generate SSPI context error when the password changes

Question

Managed Service Accounts and Cannot Generate SSPI context error when the password changes

Maddave

SSCertifiable

Points: 6828
More actions
May 22, 2013 at 2:44 am

#394826

Hi,
I regularly (every month or so) get the error "The target principle name is incorrect. Cannot generate SSPI context" when trying to remotely connect to my SQL 2012 instance. The SQL service is running using a managed service account. I understand this error can occur when the service account cannot authenticate with AD properly. Looking at the properties of the managed service account, the password for the account was automatically changed this morning - just when the error started. I am therefore assuming that this problem is something to do with this, but I am not sure.
I can fix the issue by restarting the SQL Service, but that is not ideal. Does anyone have any pointers as to how I can stop this problem from happening again?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

If the password for the service account that SQL Server or the SQL Server Agent uses changes the services have to be restarted in order for the new passwords to take affect. They should also get new Kerberos tickets issued at that time. If they are not restarted to utilized the new passwords the old Kerberos tickets will expire. That is most likely the SSPI error that you got and why restarting the service resolved the problem.

Joie Andrew
"Since 1982"

Maddave SSCertifiable Points: 6828 More actions · Answer 2

Thanks for the reply. I understand what you are saying, but the service is using a managed service account, rather than a normal user with a password. The passwords are maintained by AD automatically and are changed every 30 days. SQL server can use managed sevice accounts so that we dont have to worry about the password at all. I cant see the point to allow SQL Server to use managed service accounts if you have then have to restart the service every 30 days, thereby breaking what ever applications are connected.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

Ah, I see what you're saying now. Looking online it seems that getting MSAs to work with SQL Server 2012 is quite difficult. Question, you are not on a cluster, are you? Also, what happens if you reset the service to point to the MSA in SQL Server Configuration Manager?

Joie Andrew
"Since 1982"

Maddave SSCertifiable Points: 6828 More actions · Answer 4

No problem. Thanks for your help.

We found setting up the MSAs quite easy, so I do worry we have missed something! 😀

The server is not clustered.

It has two SQL instances running on the server, both using the same MSA account. Today both received this error when the password got automatically changed.

I restarted the service for each using SQL Configuration Manager and both came back fine with no issues.

I haven't been able to try repointing the service to the MSA as the problem only happens every 30 days and since restarting the services, this problem is now ok - for the time being!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

I totally understand about not needing to look into it because the problem only crops up every 30 days. What I meant about being difficult to setup is that it seems to have been very hit or miss for people successfully setting it up. Additionally the documentation seems to be all over the place on whether or not SQL is supported with MSAs.

Probably best to add to a checklist and if it does not work during the next month's change look to changing it and then changing it back to see if it works.

Good luck.

Joie Andrew
"Since 1982"

Maddave SSCertifiable Points: 6828 More actions · Answer 6

Thanks for your help.

I've logged a premier support call with Microsoft and they believe it may be an issue with AD, rather than SQL, so have passed me onto their team.

I'll update the thread if they find anything.

Maddave SSCertifiable Points: 6828 More actions · Answer 7

Just to follow this up.

After a number of calls with MS Premier Support, they finally worked out what I was talking about, despite constantly trying to suggest I was using a normal service account. Microsoft dont seem to know about managed service accounts despite themselves recommending their use with SQL Server.

Anyway, there is a hotfix for this issue which should fix the problem. We are going to apply this and see if it sorts our problem.

http://support.microsoft.com/kb/2494158?wa=wsignin1.0

Koenraad Dendievel SSC Veteran Points: 289 More actions · Answer 8

We ran in to a similar problem with a Group Managed Service Account

The solution was found in

http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/

*by giving read servicePrincipleName and write servicePrincipleName rights to the gMSAccount

*removing duplicates

Look whether Kerberos is used on connection

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

Look whether client can get ticket for the service

Klist get MSSQLSvc/server.domain.com:1433

Maddave SSCertifiable Points: 6828 More actions · Answer 9

Not sure how this has got to the top of the list since I asked it to years ago. Just to confirm, the bug was the issue and the hot fix from MS resolved the problem.