Error 17806 Severity 20 State 2

Dipanjan BanikDecember 8, 20101 0 0 0 I see a lot of issues related to SQL Server connectivity. One common error I see in the SQL Server logs is the SSPI error. Logon Error: logon error 17806 severity 20 state 2 17806, Severity: 20, State: 2. Logon SSPI handshake failed with error code 0x8009030c while

Error 17807 Severity 20 State 2

establishing a connection with integrated security; the connection has been closed. [CLIENT:192.168.0.5] Logon Error: 18452, Severity: 14, State: 1. Logon error 18452 severity 14 state 1 Login failed for user ". The user is not associated with a trusted SQL Server connection. [CLIENT:192.168.0.5] We normally see two kinds of SSPI errors. One is “Cannot generate SSPI context” and the other is sspi handshake failed with error code 0x8009030c “SSPI Handshake Failed”. The first error is commonly because the client is trying a Kerberos authentication and that failed, but it did not fall back to NTLM. The second one happens usually when the user is not authenticated. So I looked into the SQL Server Security Event Logs and I can see this entry:

Sspi Handshake Failed With Error Code 0x80090311

Category: Logon Level: Information Keywords: Audit Failure User: N/A Computer: SQLMACHINE.corp.mydomain.com Description: An account failed to log on. Subject: Security ID: NULL SID Account Name: - Account Domain: - Logon ID: 0x0 Logon Type: 3 Account For Which Logon Failed: Security ID: NULL SID Account Name: APPSERVER$ Account Domain: CORP.MYDOMAIN.COM Failure Information: Failure Reason: Unknown user name or bad password. Status: 0xc000006e Sub Status: 0x0 Process Information: Caller Process ID: 0x0 Caller Process Name: - Network Information: Workstation Name: - Source Network Address: - Source Port: - Detailed Authentication Information: Logon Process: Kerberos Authentication Package: Kerberos Transited Services: - Package Name (NTLM only): - Key Length: 0 In this scenario, the app was trying to connect with the machine account. Typically when using windows authentication, if the application is running in the context of Local System or Network Service, the application will connect using the machine account. If you take a Profiler trace, the account name is shown as MachineName$. If we add the APPSERVER$ account to the Administrators group of the SQL Server machine, we don’t see the problem. Looking into this error code: err 0xc000006e # for hex 0xc000006e / decimal -1073741714 STATUS_ACCOUNT_RESTRICTION ntstatus.h # Indicates a referenced user name and authenticatio

hell The infamous SSPI Failed error strikes again! One of our SQL servers was generating these errors for “some” Windows logins but not all. Error: 17806, Severity: 20, State: 2. SSPI error 18456 severity 14 state 11 handshake failed with error code 0x8009030c while establishing a connection with integrated security; sspi handshake failed with error code 0x8009030c, state 14 the connection has been closed. [CLIENT: 192.168.1.1] Error: 18452, Severity: 14, State: 1. Login failed for user ". The user

Error 17806 Severity 20 State 14

is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.1] After exhausting all of the normal troubleshooting for this error (accounts locked, disabled, Sql Service accts, bad connection strings, SPN’s, etc.) https://blogs.msdn.microsoft.com/dipanb/2010/12/08/sspi-handshake-failed-could-result-when-the-security-event-log-has-reached-the-maximum-log-size/ I spent the next few hours learning more about the way SQL handles authentication requests than I had ever wanted to know. The Scenario – A couple of separate individual Windows ID’s started generating these errors while attempting connections, all other windows logins were working properly. The connections were initially happening through applications, but also occurred through sqlcmd. When logged in to the server locally with http://www.allenkinsel.com/archive/2010/06/sql-server-and-sspi-handshake-failed-error-hell/ the offending ID’s the connections to SQL would succeed. The Troubleshooting process – Check all the regular SSPI issues, I wont bore you with the details as they are easily searchable A relatively easy way of checking the “easy” authentication issues If possible/appropriate is to log into the SQL Server locally with the offending ID and fire up sqlcmd and connect to the server via sqlcmd –Sservername,port –E  (by specifying the port you force TCP/IP instead of LPC, thereby forcing the network into the equation) Verify whether the login is trying to use NTLM or Kerberos (many ways to do this but simplest is to see if there are any other KERBEROS connections on the machine) SELECT DISTINCT auth_scheme FROM sys.dm_exec_connections If Kerberos is in use, there are a few additional things to verify related to SPN’s, since only NTLM was in use on this server I skipped that Determine if the accounts were excluded from connecting to the machine through the network through a group policy or some other AD setting After all of these checked out OK, I began to try and figure out what the error code 0x8009030c meant, turns o

Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 7,2000 » Administration » Error: http://www.sqlservercentral.com/Forums/Topic996539-5-1.aspx 17806, Severity: 20, State: 2. Error: 17806, Severity: 20, State: 2. Rate Topic http://www.sqlserver-dba.com/network/ Display Mode Topic Options Author Message Minto Minto(quendans)Minto Minto(quendans) Posted Friday, October 1, 2010 5:11 AM SSC Journeyman Group: General Forum Members Last Login: Wednesday, May 11, 2016 12:43 AM Points: 85, Visits: 533 HiI got this error .Please help me to solve this.Error: 17806, Severity: 20, State: 2.SSPI handshake failed with severity 20 error code 0x80090311 while establishing a connection with integrated security; the connection has been closed.After this i got again another error...Error: 18452, Severity: 14, State: 1.Login failed for user ''. The user is not associated with a trusted SQL Server connection.we are using sql server 2005,standard eddition.Thanks in Advance.... Post #996539 AdigaAdiga Posted Sunday, October 3, 2010 10:53 PM SSCommitted Group: General Forum Members Last Login: severity 20 state Sunday, January 17, 2016 1:26 AM Points: 1,618, Visits: 21,012 The first error code indicates that SQL Server is unable to authenticate with the Active Directory. Check if the SPN is created properly. Refer thisAre you able to connect using SQL Server authentication? Pradeep Adiga Blog: sqldbadiaries.comTwitter: @pradeepadiga Post #997352 mohajeezmohajeez Posted Monday, June 3, 2013 4:29 AM Grasshopper Group: General Forum Members Last Login: Wednesday, August 19, 2015 6:32 AM Points: 21, Visits: 17 THanks adiga.who will do "register an SPN " activity? Is it windows team, right? Post #1459172 « Prev Topic | Next Topic » Permissions You cannot post new topics. You cannot post topic replies. You cannot post new polls. You cannot post replies to polls. You cannot edit your own topics. You cannot delete your own topics. You cannot edit other topics. You cannot delete other topics. You cannot edit your own posts. You cannot edit other posts. You cannot delete your own posts. You cannot delete other posts. You cannot post events. You cannot edit your own events. You cannot edit other events. You cannot delete your own events. You cannot delete other events. You cannot send private mes

by FeedBurner SQLServer-DBA.com Links Recent Posts How to schedule Powershell Script with Task Scheduler Lots of NULL values can cause trouble ALTER INDEX REBUILD clears sys.dm_db_index_usage_stats 7 mistakes DBAs make on CV or resume 5 ways globalisation has impacted IT Is a LOG BACKUP allowed on a SIMPLE RECOVERY database How to turn AUTO_SHRINK off 7 Essential Steps to Recover a Corrupt SQL Server Database DBA vacancy at Travelers - UK based Get Last Windows boot up time with Powershell Powered by TypePad July 20, 2016 How to list Domain Controllers in a Domain with nltest Question:How can I list the Domain Controllers for a domain?  There have been some Domain Controller issues and trusted authority login failures - and I'm using Troubleshooting Error: 18452, Severity: 14, State: 1 (SQL Server DBA).  The Domain Controller team would like to see a list of Domain Controllers available from a server.I’m using Windows 2008 R2 Enterprise Server Answer: The nltest command line tool  is useful for Domain Controller information. It arrives as part of Windows 2008 . The tool allows the operator to complete a number of tests such as trust status and domain controller replication status. Nltest will also allow you to list Domain Controllers for a domain. This is an example to list all the available domain controllers within a domain. In the example I’m calling the domain ‘MYDOMAIN’. Replace with your domain name Nltest /DCLIST:MYDOMAIN   Read More SQL Server – How to Ask for support and troubleshoot problems SQL Server - SSPI handshake failed with error code 0x80090304 ... Posted at 01:33 AM in Network, SQL Error Logs | Permalink | Comments (0) May 12, 2015 SQL Server Configuration Manager from Command Line Question: How can I start SQL Server Configuration  Manager from Command Line? Answer: To start SQL Server Configuration Manager from Command line is a slightly different reference for every  SQL Server Version. You can access information normally obtained therough SQL Server Configuration Manager in alternative ways such as SQL Server – Find SQL Server tcp port with Powershell . But SQL Server Configuration Manager remains my primary method to manager configurations   For 2005 - SQLServerManager.msc For 2008/R2 - SQLServerManager10.msc For 2012  - SQLServerManager11.msc For 2014 - SQLServerManager12.msc   Read more on command line access through SSMS and other SSMS topics SQL Server - Open SSMS on the Command Line - SQL Server DBA Clear list of server names on SQL Server Management Studio SQL Server – Open SSMS without the splash screen graphic - SQL ... P