Share via

‘Cannot Generate SSPI Context’ and Service Account Passwords

  • Article

Was working with Keith Elmore on one of our internal processes and he was hitting a “Cannot generate SSPI context” when trying to connect from Management Studio.  I also saw this come up in a double hop situation (IIS to SQL) when I setup a local repro.

image

We went through the normal check list for Kerberos Troubleshooting, but really that just consisted of validating the SPN in the case of Management Studio as it was a single hop and we were just trying to do a direct connection without any delegation.  The SPN checked out, and there was only one SPN.  No duplicates.

image

We have an internal tool called SSPIClient which will go through the motions of just trying the Windows API calls for Kerberos authentication (IntializeSecurityContext).

2009-12-30 21:11:16.185 Connecting via ODBC to [DRIVER=SQL Server;Server=tcp:passsql\demo;Trusted_Connection=Yes;]

2009-12-30 21:11:16.232 ENTER InitializeSecurityContextA
2009-12-30 21:11:16.232 phCredential = 0x0055ffb4
2009-12-30 21:11:16.232 phContext = 0x0055ffc4
2009-12-30 21:11:16.232 pszTargetName = 'MSSQLSvc/PASSSQL.pass.local:59256'
2009-12-30 21:11:16.232 fContextReq = 0x00000003 ISC_REQ_DELEGATE|ISC_REQ_MUTUAL_AUTH
2009-12-30 21:11:16.232 TargetDataRep = 16
2009-12-30 21:11:16.232 pInput = 0x0018d55c
2009-12-30 21:11:16.232 pInput->ulVersion = 0
2009-12-30 21:11:16.232 pInput->cBuffers = 1
2009-12-30 21:11:16.232 pBuffers[00].cbBuffer = 112
2009-12-30 21:11:16.232 pBuffers[00].BufferType = 2 SECBUFFER_TOKEN
2009-12-30 21:11:16.232 pBuffers[00].pvBuffer = 0x03753870
2009-12-30 21:11:16.232 03753870 a1 6e 30 6c a0 03 0a 01 01 a2 65 04 63 60 61 06 .n0l......e.c`a.
2009-12-30 21:11:16.232 03753880 09 2a 86 48 86 f7 12 01 02 02 03 00 7e 52 30 50 .*.H........~R0P
2009-12-30 21:11:16.232 03753890 a0 03 02 01 05 a1 03 02 01 1e a4 11 18 0f 32 30 ..............20
2009-12-30 21:11:16.232 037538a0 30 39 31 32 33 30 32 31 31 31 31 36 5a a5 05 02 091230211116Z...
2009-12-30 21:11:16.232 037538b0 03 01 0d b4 a6 03 02 01 29 a9 0c 1b 0a 50 41 53 ........)....PAS
2009-12-30 21:11:16.232 037538c0 53 2e 4c 4f 43 41 4c aa 17 30 15 a0 03 02 01 01 S.LOCAL..0......
2009-12-30 21:11:16.232 037538d0 a1 0e 30 0c 1b 0a 73 71 6c 73 65 72 76 69 63 65 ..0...sqlservice
2009-12-30 21:11:16.232 phNewContext = 0x0055ffc4
2009-12-30 21:11:16.232 pOutput = 0x0018d574
2009-12-30 21:11:16.232 pOutput->ulVersion = 0
2009-12-30 21:11:16.232 pOutput->cBuffers = 1
2009-12-30 21:11:16.232 pBuffers[00].cbBuffer = 12256
2009-12-30 21:11:16.232 pBuffers[00].BufferType = 2 SECBUFFER_TOKEN
2009-12-30 21:11:16.232 pBuffers[00].pvBuffer = 0x03759d68
2009-12-30 21:11:16.232 pfContextAttr = 0x00000000
2009-12-30 21:11:16.232 ptsExpiry = 0x0018d548 -> 1601-01-01 00:00:00 *** EXPIRED *** (3585189:11:16 diff)
2009-12-30 21:11:16.232 EXIT InitializeSecurityContextA returned 0x80090322 SEC_E_WRONG_PRINCIPAL (The target principal name is incorrect)
2009-12-30 21:11:16.232
2009-12-30 21:11:16.232 ******************** ODBC Errors ********************
2009-12-30 21:11:16.232 Return code = -1.
2009-12-30 21:11:16.232 SQLError[00] SQLState 'S1000'
2009-12-30 21:11:16.232 SQLError[00] NativeError 0
2009-12-30 21:11:16.232 SQLError[00] Message '[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context'
2009-12-30 21:11:16.232 ******************** ODBC Errors ********************

It was saying that the principal was incorrect, but you can see in the output that it is showing sqlservice, which is correct.  We had rebooted the SQL Server in question, at which point the SQL Service wouldn’t even start.  Keith asked if the password had been changed recently.  We took a look, and sure enough, the password was changed yesterday.  This happens to be an account that we use for multiple things. 

We changed the service account password through SQL Server Configuration Manager and restarted SQL.  SQL could start at that point, and the SSPI error disappeared.  We were able to successfully connect to SQL at that point.

I’m sure other people have known about this type of condition, but in the years that I’ve been here, along with the number of Kerb issues that I’ve troubleshot in the past, this was the first time I had run across this.  Thought I would throw it out there to share with everyone in case they maybe run across something like this that they can’t explain. 

If you change your service password, be sure to recycle the SQL Service so that Kerberos can function properly.

 

Adam W. Saxton | Microsoft SQL Server Escalation Services

Comments

  • Anonymous
    December 31, 2009
    I have gotten this error also when I was supporting Altiris software.  Usually I had to switch over to named pipes and it was resolved. I will have to try this out next time.  Thanks.

  • Anonymous
    January 05, 2010
    Adam, Will you make the SSPIClient available to the public?  I see this error all the time because non-admin-based accounts are used to run the Db servers. That'd be a great tool to add to my utility belt as an IT Pro! Thanks, Herman

  • Anonymous
    January 06, 2010
    Adam, I would second the request for making this tool available.  It looks like it could save a great deal of time in dealing with sql and kereberos. Thank you, Jeremy

  • Anonymous
    January 18, 2010
    Thanks Adam We get these errors quite regularly and it would be a great help to use your SSPI tool. Thanks

  • Anonymous
    January 21, 2010
    The tool that is currently available I cannot release.  But I will look at putting something together that can go onto Codeplex for general availablility.  The main issue is I don't know that the source is available for the SSPIClient tool anymore and it is pretty old. Thanks, Adam

  • Anonymous
    January 24, 2010
    We've seen this recently on 3 different customer servers...all resolved by restarting the service of the installed monitoring tool. We hadn't seen it in the 3 years I;ve been here prior to this week

  • Anonymous
    June 13, 2011
    The comment has been removed

  • Anonymous
    June 18, 2011
    ok! Now the 'Microsoft SQL Server Escalation Services' are sharing the ways to troubleshoot the kereros issues but they can not share the ONLY tool that they used. Adam you can do better with ur life.

  • Anonymous
    July 07, 2011
    Check out my blogs http://sqlserver-help.com/

  • Anonymous
    January 15, 2015
    If you have a cluster and the SQL Network Name is the same name as the SQL Startup account - using setspn -A will likely put the spn on the SQL Network Name account (which is a computer account) instead of the SQL Startup account (which is a user account).  This will generate the exact errors as shown in the article SSPI / SEC_E_WRONG_PRINCIPAL  etc.  I have seen this several times.  The trick is to add the spn by using setspn -A -U which will add the spn to the user account or use adsiedit to add it manually. If your setspn -L  (to search for spns) comes back like this; C:>setspn -L abcsql04 Registered ServicePrincipalNames for CN=ABCSQL04,OU=db-servers,OU=servers,OU=computers,OU=Resources, <--- Then your spn is not on the user account. It must look like this; Registered ServicePrincipalNames for CN=ABCsql04,CN=Users, <---

  • Anonymous
    March 17, 2016
    After renaming a SQL 2012 server we were getting these errors for windows connections. Read here or somewhere to change SQL service to start as (local system) , then reboot server. Then change back to original AD/Windows acct and reboot. This worked for us.