‘Cannot Generate SSPI Context’ and Service Account Passwords

Published Jan 15 2019 12:36 PM 589 Views
Microsoft
First published on MSDN on Dec 30, 2009

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.

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.

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

%3CLINGO-SUB%20id%3D%22lingo-sub-315927%22%20slang%3D%22en-US%22%3E%E2%80%98Cannot%20Generate%20SSPI%20Context%E2%80%99%20and%20Service%20Account%20Passwords%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315927%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2030%2C%202009%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EWas%20working%20with%20Keith%20Elmore%20on%20one%20of%20our%20internal%20processes%20and%20he%20was%20hitting%20a%20%E2%80%9CCannot%20generate%20SSPI%20context%E2%80%9D%20when%20trying%20to%20connect%20from%20Management%20Studio.%26nbsp%3B%20I%20also%20saw%20this%20come%20up%20in%20a%20double%20hop%20situation%20(IIS%20to%20SQL)%20when%20I%20setup%20a%20local%20repro.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F66333iEF6CA69248549333%22%20%2F%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EWe%20went%20through%20the%20normal%20check%20list%20for%20Kerberos%20Troubleshooting%2C%20but%20really%20that%20just%20consisted%20of%20validating%20the%20SPN%20in%20the%20case%20of%20Management%20Studio%20as%20it%20was%20a%20single%20hop%20and%20we%20were%20just%20trying%20to%20do%20a%20direct%20connection%20without%20any%20delegation.%26nbsp%3B%20The%20SPN%20checked%20out%2C%20and%20there%20was%20only%20one%20SPN.%26nbsp%3B%20No%20duplicates.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F66334iA2D445731F8532B0%22%20%2F%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EWe%20have%20an%20internal%20tool%20called%20SSPIClient%20which%20will%20go%20through%20the%20motions%20of%20just%20trying%20the%20Windows%20API%20calls%20for%20Kerberos%20authentication%20(IntializeSecurityContext).%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E2009-12-30%2021%3A11%3A16.185%20Connecting%20via%20ODBC%20to%20%5BDRIVER%3DSQL%20Server%3BServer%3Dtcp%3Apasssql%5Cdemo%3BTrusted_Connection%3DYes%3B%5D%3C%2FP%3E%0A%20%20%20%3CP%3E2009-12-30%2021%3A11%3A16.232%20ENTER%20InitializeSecurityContextA%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20phCredential%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x0055ffb4%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20phContext%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x0055ffc4%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pszTargetName%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%20'MSSQLSvc%2FPASSSQL.pass.local%3A59256'%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20fContextReq%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x00000003%20ISC_REQ_DELEGATE%7CISC_REQ_MUTUAL_AUTH%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20TargetDataRep%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%2016%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pInput%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x0018d55c%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pInput-%26gt%3BulVersion%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pInput-%26gt%3BcBuffers%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%201%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pBuffers%5B00%5D.cbBuffer%26nbsp%3B%26nbsp%3B%20%3D%20112%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pBuffers%5B00%5D.BufferType%20%3D%202%20SECBUFFER_TOKEN%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pBuffers%5B00%5D.pvBuffer%26nbsp%3B%26nbsp%3B%20%3D%200x03753870%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%2003753870%26nbsp%3B%20a1%206e%2030%206c%20a0%2003%200a%2001%2001%20a2%2065%2004%2063%2060%2061%2006%26nbsp%3B%26nbsp%3B%20.n0l......e.c%60a.%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%2003753880%26nbsp%3B%2009%202a%2086%2048%2086%20f7%2012%2001%2002%2002%2003%2000%207e%2052%2030%2050%26nbsp%3B%26nbsp%3B%20.*.H........~R0P%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%2003753890%26nbsp%3B%20a0%2003%2002%2001%2005%20a1%2003%2002%2001%201e%20a4%2011%2018%200f%2032%2030%26nbsp%3B%26nbsp%3B%20..............20%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20037538a0%26nbsp%3B%2030%2039%2031%2032%2033%2030%2032%2031%2031%2031%2031%2036%205a%20a5%2005%2002%26nbsp%3B%26nbsp%3B%20091230211116Z...%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20037538b0%26nbsp%3B%2003%2001%200d%20b4%20a6%2003%2002%2001%2029%20a9%200c%201b%200a%2050%2041%2053%26nbsp%3B%26nbsp%3B%20........)....PAS%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20037538c0%26nbsp%3B%2053%202e%204c%204f%2043%2041%204c%20aa%2017%2030%2015%20a0%2003%2002%2001%2001%26nbsp%3B%26nbsp%3B%20S.LOCAL..0......%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20037538d0%26nbsp%3B%20a1%200e%2030%200c%201b%200a%2073%2071%206c%2073%2065%2072%2076%2069%2063%2065%26nbsp%3B%26nbsp%3B%20..0...sqlservice%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20phNewContext%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x0055ffc4%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pOutput%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x0018d574%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pOutput-%26gt%3BulVersion%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pOutput-%26gt%3BcBuffers%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%201%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pBuffers%5B00%5D.cbBuffer%26nbsp%3B%26nbsp%3B%20%3D%2012256%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pBuffers%5B00%5D.BufferType%20%3D%202%20SECBUFFER_TOKEN%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pBuffers%5B00%5D.pvBuffer%26nbsp%3B%26nbsp%3B%20%3D%200x03759d68%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20pfContextAttr%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x00000000%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20ptsExpiry%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200x0018d548%20-%26gt%3B%201601-01-01%2000%3A00%3A00%20***%20EXPIRED%20***%20(3585189%3A11%3A16%20diff)%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20EXIT%26nbsp%3B%20InitializeSecurityContextA%20returned%200x80090322%20SEC_E_WRONG_PRINCIPAL%20(The%20target%20principal%20name%20is%20incorrect)%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20********************%20ODBC%20Errors%20********************%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20Return%20code%20%3D%20-1.%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20SQLError%5B00%5D%20SQLState%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'S1000'%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20SQLError%5B00%5D%20NativeError%200%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20SQLError%5B00%5D%20Message%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'%5BMicrosoft%5D%5BODBC%20SQL%20Server%20Driver%5DCannot%20generate%20SSPI%20context'%20%3CBR%20%2F%3E%202009-12-30%2021%3A11%3A16.232%20********************%20ODBC%20Errors%20********************%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EIt%20was%20saying%20that%20the%20principal%20was%20incorrect%2C%20but%20you%20can%20see%20in%20the%20output%20that%20it%20is%20showing%20sqlservice%2C%20which%20is%20correct.%26nbsp%3B%20We%20had%20rebooted%20the%20SQL%20Server%20in%20question%2C%20at%20which%20point%20the%20SQL%20Service%20wouldn%E2%80%99t%20even%20start.%26nbsp%3B%20Keith%20asked%20if%20the%20password%20had%20been%20changed%20recently.%26nbsp%3B%20We%20took%20a%20look%2C%20and%20sure%20enough%2C%20the%20password%20was%20changed%20yesterday.%26nbsp%3B%20This%20happens%20to%20be%20an%20account%20that%20we%20use%20for%20multiple%20things.%3C%2FP%3E%0A%20%20%3CP%3EWe%20changed%20the%20service%20account%20password%20through%20SQL%20Server%20Configuration%20Manager%20and%20restarted%20SQL.%26nbsp%3B%20SQL%20could%20start%20at%20that%20point%2C%20and%20the%20SSPI%20error%20disappeared.%26nbsp%3B%20We%20were%20able%20to%20successfully%20connect%20to%20SQL%20at%20that%20point.%3C%2FP%3E%0A%20%20%3CP%3EI%E2%80%99m%20sure%20other%20people%20have%20known%20about%20this%20type%20of%20condition%2C%20but%20in%20the%20years%20that%20I%E2%80%99ve%20been%20here%2C%20along%20with%20the%20number%20of%20Kerb%20issues%20that%20I%E2%80%99ve%20troubleshot%20in%20the%20past%2C%20this%20was%20the%20first%20time%20I%20had%20run%20across%20this.%26nbsp%3B%20Thought%20I%20would%20throw%20it%20out%20there%20to%20share%20with%20everyone%20in%20case%20they%20maybe%20run%20across%20something%20like%20this%20that%20they%20can%E2%80%99t%20explain.%3C%2FP%3E%0A%20%20%3CP%3EIf%20you%20change%20your%20service%20password%2C%20be%20sure%20to%20recycle%20the%20SQL%20Service%20so%20that%20Kerberos%20can%20function%20properly.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EAdam%20W.%20Saxton%20%7C%20Microsoft%20SQL%20Server%20Escalation%20Services%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-315927%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2030%2C%202009%20Was%20working%20with%20Keith%20Elmore%20on%20one%20of%20our%20internal%20processes%20and%20he%20was%20hitting%20a%20%E2%80%9CCannot%20generate%20SSPI%20context%E2%80%9D%20when%20trying%20to%20connect%20from%20Management%20Studio.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315927%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econnectivity%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 12:36 PM
Updated by: