CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services
Common Tasks
Search
  • Advanced search options...
Recent Posts
Tags
Locations of visitors to this page
Archives
Archives

‘Cannot Generate SSPI Context’ and Service Account Passwords

MSDN Blogs > CSS SQL Server Engineers > ‘Cannot Generate SSPI Context’ and Service Account Passwords

‘Cannot Generate SSPI Context’ and Service Account Passwords

30 Dec 2009 1:28 PM
  • Comments 9

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

, ,
Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
Comments
  • John Dunleavy
    31 Dec 2009 11:03 AM

    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.

  • Herman
    5 Jan 2010 2:04 PM

    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

  • 6 Jan 2010 10:04 PM

    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

  • Brennan Gordon
    18 Jan 2010 10:56 PM

    Thanks Adam

    We get these errors quite regularly and it would be a great help to use your SSPI tool.

    Thanks

  • 21 Jan 2010 8:18 AM

    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

  • Kevin3NF
    25 Jan 2010 7:09 AM

    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

  • lil_kid
    13 Jun 2011 6:37 PM

    ADAM - Its super lame to talk about a show off a tool which is not available to the public. Sucky post.

  • Dave Whitney Sr
    18 Jun 2011 12:05 PM

    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.

  • Balmukund
    7 Jul 2011 9:37 PM

    Check out my blogs http://sqlserver-help.com/

Page 1 of 1 (9 items)