Error 18456 Severity 14 State 11 Linked Server
Contents |
27, 201010 0 0 0 A recent discussion on the #sqlhelp hashtag on Twitter got me researching into this issue. The
Sql Server Error 18456 Severity 14 State 5
error message was: 2010-10-19 02:56:59.380 Logon Error: 18456, Severity: 14, State: 11. error 18456 severity 14 state 38. sql server 2008 r2
2010-10-19 02:56:59.380 Logon Login failed for userError 18456 Severity 14 State 11 Nt Authority Anonymous Logon
infrastructure error. Check for previous errors. [CLIENT: ] Starting from SQL Server 2008, the login failed messages have the reasons for the login failure printed in the SQL Errorlog. error 18456 severity 14 state 11 sql 2005 This definitely saves us a lot of trouble of jumping through hoops to find out the corresponding meaning of a State number reported.
Additionally, there are Ring Buffers entries associated with Login Failures which can be very useful when troubleshooting such issues. This has been blogged about in an earlier blog post here: http://blogs.msdn.com/b/psssql/archive/2008/03/24/how-it-works-sql-server-2005-sp2-security-ring-buffer-ring-buffer-security-error.aspx So, why so much fuss about error 18456 severity 14 state 8 this error? The above English text even though it is supposed to be helpful can be more of an evil in this case. To explain this simply, the error message is trying to tell you that the security information contained in the user’s token doesn’t have the necessary privileges to grant access to the SQL instance. State 11 corresponds to “Valid login but server access failure” which again points to the same fact that the login is valid but is missing certain security privileges which would grant it access to the instance. The first option that half the SQL world would suggest to you would be to use “Run As Administrator” option and try the same operation from SSMS or SQLCMD. If disabling UAC or if you don’t have UAC enabled, then here is what you should be trying: 1. Look into the SQL Errorlog and verify that that the login failed message for the user has a State 11. You can alternatively verify the state number from the SQL Server default traces as well (availablof Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging
Error 18456 Severity 14 State 1
here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and error 18456 severity 14 state 6 also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at
Error 18456 Severity 14 State 16. Login Failed For User
major conferences, user group meetings, and SQL Saturday events worldwide. Troubleshooting Error 18456 I think we've all dealt with error 18456, whether it be an application unable to access SQL Server, credentials changing https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/27/troubleshooting-specific-login-failed-error-messages/ 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). In a few cases, some additional information is included, but for the most part several of these http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx 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 attempt to authorize you as a SQL login, and you will fail with state 5 (if there is no SQL login with that name) or state 8 (if there is also a SQL login with
Related Tips: More > Security Problem Recently I experienced a situation where a SQL Server login failed with this error message: Error: 18456, Severity: 14, State: 10. Login failed for user 'SQL_Login'. Reason: Password https://www.mssqltips.com/sqlservertip/2679/sql-server-login-failure-error-18456-severity-14-state-10/ validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX]. Can you help me decipher this SQL Server issue and correct it? Check out this tip to learn more. Solution Error number 18456 indicates a login failure. To troubleshoot the SQL Server login failure we need to determine the state of the error message. In this tip we are going to troubleshoot error messages with an error state of 10. error 18456 Review the SQL Server Error Logs for Login Failures Execute the query below to determine the different login failures. For more information about the xp_readerrorlog extended stored procedure, review Reading the SQL Server log files using T-SQL. EXEC xp_readerrorlog 0,1,"18456",Null In our example, the login failure error message is show below: Error: 18456, Severity: 14, State: 10. Login failed for user 'SQL_Login'. Reason: Password validation failed with an infrastructure error. Check for error 18456 severity previous errors. [CLIENT: XXX.XXX.XXX.XXX]. Error State 10 indicates that password checking could not be performed and the login failed. Let's review the list of steps below to try to fix the issue. Troubleshooting SQL Server Login Failures - Error State 10 Step 1: First let's check whether the SQL Server login is locked, expired or requires a password change. In order to accomplish that, we will use the Login Property function which will give us information about the login policy setting. Login Property function returns 1 or 0 as output. 1 indicates that login is in the specified state (True). 0 indicates that login is not in the specified state (False) select loginproperty('SQLDBPool','isexpired') select loginproperty('SQLDBPool','IsLocked') select loginproperty('SQLDBPool','IsMustChange') If the account is locked, you can use the below script to unlock the account. ALTER LOGIN [sqldbpool] WITH PASSWORD = 'password' UNLOCK If the account is expired, use the below script to set it to an active state. ALTER LOGIN [sqldbpool] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=ON If the login must change password on next login is true, you have to reset the password. Step 2: If there are no issues with the SQL Server login then the next step is to check the SQL Server Domain Service account status. Let's check these properties of the Service Account: Service