Error 18456 Error State 11
Contents |
of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform error 18456 state 16 and VMware. He has been blogging here at sqlblog.com since 2006, focusing
Error 18456 Sql Server
on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP
Token Based Server Access Validation Failed With An Infrastructure Error
since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide. Troubleshooting Error 18456 I think we've all dealt with error 18456,
Error 18456 Severity 14 State 11
whether it be an application unable to access SQL Server, credentials changing over time, or a user who can't type a password correctly. The trick to troubleshooting this error number is that the error message returned to the client or application trying to connect is intentionally vague (the error message is similar for most errors, and the state is always 1). error 18456 severity 14 state 11 nt authority anonymous logon In a few cases, some additional information is included, but for the most part several of these conditions appear the same to the end user. In order to figure out what is really going wrong, you need to have alternative access to the SQL Server and inspect the log for the true state in the error message. I helped our support team just today solve a client's 18456 issues - once we tracked down the error log and saw that it was state 16, it was easy to determine that their login had been set up with a default database that had been detached long ago. In SQL Server 2012, there is a new feature called "contained databases" - I've blogged about it here and here. With this feature comes a new layer of security that may creep onto your radar if you use this functionality: contained user authentication failures. There are a variety of things that can go wrong here. If you connect with a contained user but forget to specify a database name, SQL Server will at
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Non-SysAdmins get error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based error 18456 severity 14 state 11 sql 2008 r2 server access validation failed with an infrastructure error. SQL Server > SQL Server error 18456 state 1 Security Question 0 Sign in to vote I have a SQL 2008 R2 system (10.50.4000) where I'm having problems connecting error 18456 state 5 any user that is not a SysAdmin. Example: I setup a new SQL Login to use Windows Authentication and grant that user db_datareader on the target database. The user attempts to connect using http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx Excel client or Access or SQL Management Studio and receives Error 18456. The SQL Server Logs shows Error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error. The strange part is that if I temporarily grant the user the sysadmin server role then the user can connect successfully and retrieve data. But, if I take away https://social.msdn.microsoft.com/Forums/sqlserver/en-US/220cee33-3e51-414e-bb89-0619589fbf69/nonsysadmins-get-error-18456-severity-14-state-11-login-failed-for-user-reason-tokenbased-server?forum=sqlsecurity thatsysadmin server role then the user can no longer connect but again receives theError 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error. We've turned off UAC on the client machine to see if that was the problem, but no change. I have dropped and re-added the user's SQL Login (and the related database user login info). No success. The Ring Buffers output shows: TheCalling API Name: LookupAccountSidInternal API Name: LookupAccountSid Error Code: 0x534 Thanks for any help. -Walt Monday, March 17, 2014 2:20 PM Reply | Quote Answers 0 Sign in to vote The issue here is most certainly lack of CONNECT permmission somewhere. Try this: GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public This connect is granted by default, but it can be revoked. If this does not work out, please post the output of SELECT * FROM sys.server_permissions Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator Tuesday, March 18, 2014 3:55 AM Marked as answer by Walt 5 Tuesday, March 18, 2014 3:51 PM Monday, March 17, 2014 10:37 PM Reply | Quote All replies
2016 (3) July, 2016 (1) December, 2015 (1) July, 2015 (1) October, 2014 (1) July, 2014 (1) January, 2013 (1) October, 2012 (1) July, 2012 (1) June, 2012 (4) April, 2012 (2) March, 2012 http://weblogs.sqlteam.com/billg/archive/2014/10/08/error-18456-severity-14-state-11.aspx (1) December, 2011 (1) October, 2011 (2) September, 2011 (1) August, 2011 (1) July, 2011 (2) June, 2011 (5) May, 2011 (1) April, 2011 (1) March, 2011 (2) February, 2011 (12) January, 2011 (4) http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/ December, 2010 (2) November, 2010 (3) October, 2010 (2) September, 2010 (1) July, 2010 (1) March, 2010 (2) February, 2010 (2) January, 2010 (2) December, 2009 (1) November, 2009 (1) October, 2009 (5) September, 2009 error 18456 (4) August, 2009 (1) July, 2009 (5) June, 2009 (4) May, 2009 (8) April, 2009 (3) March, 2009 (2) January, 2009 (1) October, 2008 (3) June, 2008 (1) April, 2008 (2) February, 2008 (2) January, 2008 (3) December, 2007 (1) November, 2007 (1) October, 2007 (2) September, 2007 (3) August, 2007 (2) July, 2007 (2) June, 2007 (4) May, 2007 (1) April, 2007 (5) February, 2007 (3) January, 2007 error 18456 state (2) November, 2006 (1) October, 2006 (1) September, 2006 (1) July, 2006 (2) June, 2006 (2) May, 2006 (1) April, 2006 (2) March, 2006 (5) February, 2006 (5) January, 2006 (2) December, 2005 (1) November, 2005 (4) October, 2005 (2) September, 2005 (4) July, 2005 (3) June, 2005 (1) May, 2005 (5) April, 2005 (3) March, 2005 (2) February, 2005 (1) January, 2005 (1) December, 2004 (2) November, 2004 (1) October, 2004 (2) September, 2004 (1) June, 2004 (2) May, 2004 (1) April, 2004 (3) March, 2004 (3) February, 2004 (1) January, 2004 (4) December, 2003 (2) November, 2003 (4) October, 2003 (9) September, 2003 (5) Post Categories Events (rss) Miscellaneous (rss) SQL Server Stuff (rss) Software Development (rss) Database Builds (rss) Site Related (rss) Utilities (rss) PASS (rss) ClearTrace (rss) KCTechBlog Syndication (rss) Consulting SQL Server Consulting SQL Server About SQLTeam.com SQLTeam.com Weblog << SQL Saturday 300 BBQ Crawl | Home | ClearTrace Build 47 Available >> Error: 18456, Severity: 14, State: 11 I’ve seen this error off and on in our environment for a while. The full error looks like this: Login failed for user DOMAIN\login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: X.X.X.X]
= 18456 Output: Login failed for user ‘%.*ls'.%.*ls%.*ls This is one of the infamous error message (and number) that most of the DBAs and developers have come across while working on SQL server. This message simply denotes that the client call was able to reach the SQL server and then an ACCESS was denied to the particular login for a reason. To figure out the exact reason, this error number 18456 with its STATE number is logged into the SQL server error log file, if SQL server was allowed or configured to capture the failed logins. Configuring SQL server for capturing login failures: By default, SQL server is configured to capture only failed logins but it can be changed to any of the options as mentioned in this books online link http://technet.microsoft.com/en-us/library/ms188470.aspx Below figure shows these options to have login failed messages written into error log Accessing Error log: Now that, we know SQL server logs all login failed messages into its error log and windows event viewer but how do I access them? Windows event viewer: GO to start –> Run –> Eventvwr –> open up the application logs, and now we could see the login failed error message with computer name, instance name, date and time and finally the reason for the login failed SQL Error log: SQL server error log can be viewed from multiple places If we have gained access or can gain access to SQL server with different logon credentials then always use sp_readerrorlog, xp_readerrorlog or use the GUI option of opening up Management node –> SQL server Logs –> View –> SQL server log. Same sample shown above looks like In a case where we cannot gain access to SQL server, then we may use the actual error log path and open the txt file physically from the file system. Use SQL server configuration manager to find the error log path and from there you could open the file. Get the value next to –e parameter and that gives the actual error log file location