Error 18456 Severity 14 State 16 Mirror
Contents |
of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new sql server error 18456 severity 14 state 16 features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; sql server error 18456 severity 14 state 5 tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide. Troubleshooting Error 18456 I think we've error 18456 severity 14 state 38 all dealt with error 18456, 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
Error 18456 Severity 14 State 38 Nt Authority System
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 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 error 18456 severity 14 state 38 sql 2008 r2 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 the same name and the password doesn't match). There is also a new state 65 which occurs if you have specified the correct username and contained database, but entered an incorrect password. The way that the authentication process works is, if SQL Server doesn't find your user in the contained database you specifies, it tries again at the server level, then gives up (it won't
Related Tips: More > Error Logs Problem SQL Server Error Logs often show a message related to error 18456. Although it generally means a login attempt error 18456 severity 14 state 11 from a client connection has failed, different State numbers associated with the error
Error 18456 Severity 14 State 11 Nt Authority Anonymous Logon
can mean different reasons for the failure. One of the error States is 38, which was added with
Error 18456 Severity 14 State 5 Login Failed For User
SQL Server 2008, means the database being accessed cannot be found or does not exist. Unfortunately the Error Log entry does not show what database it is, so in this tip http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx we walk through how you can determine which database is causing the error message. Solution A few days ago I was looking through the Error Log of a database server and noticed a large number of the same error messages that looked like the following: 2011-12-15 11:22:08.76 Logon Error: 18456, Severity: 14, State: 38. 2011-12-15 11:22:08.76 Logon Login failed for user '
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site http://dba.stackexchange.com/questions/29613/login-failed-for-user-error-18456-severity-14-state-38 About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack http://www.dbforums.com/showthread.php?1633954-Error-18456-Severity-14-State-16-Login-failed-for-user-sa Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up error 18456 Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Login failed for user - Error 18456 - Severity 14, State 38 up vote 12 down vote favorite 4 Message that SQL Server Log File Viewer shows: Login failed for user [User] Error: 18456, Severity: 14, State 38 What it actually means: Failed to error 18456 severity open the explicitly specified database My Question: Is there a list somewhere of all the variations of the 18456 errors (Login failed), for each combination of severity and state, with the helpful description text? I've had a Google but can't find anything other than specific combinations. sql-server errors logins share|improve this question asked Nov 30 '12 at 16:30 Pete Oakey 2841210 add a comment| 2 Answers 2 active oldest votes up vote 11 down vote State codes and their meaning. 1 'Account is locked out' 2 'User id is not valid' 3-4 'Undocumented' 5 'User id is not valid' 6 'Undocumented' 7 'The login being used is disabled' 8 'Incorrect password' 9 'Invalid password' 10 'Related to a SQL login being bound to Windows domain password policy enforcement. See KB925744.' 11-12 'Login valid but server access failed' 16 'Login valid, but not permissioned to use the target database' 18 'Password expired' 27 'Initial database could not be found' 38 'Login valid but database unavailable (or login not permissioned)' More detailed information is available in Aaron Bertrand's blog. share|improve this answer edited Dec 1 '12 at 13:27 answered Nov 30 '12 at 16:33 Pete Oakey 2841210 5
for user 'sa'. If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 12 of 12 Thread: Error: 18456, Severity: 14, State: 16. Login failed for user 'sa'. Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 09-16-08,05:37 #1 TanveerAhmed View Profile View Forum Posts Registered User Join Date Jul 2008 Posts 9 Unanswered: Error: 18456, Severity: 14, State: 16. Login failed for user 'sa'. Hi, Since morning I have observed the following errors in sql 2005 error logs Error: 18456, Severity: 14, State: 16. Login failed for user 'sa'. [CLIENT: XXX.XXX.XX.XXX] (where XXX represents Ip address of client machine) After that the client machines are not getting connected to sql server. Actually I have 2 applications connected to the same sql server. One is working perfectly fine while the other is not able to connect. Both the applications are connected through sa. Can someone please help me to solve this issue. Note : SQL Server version is SQL Server 2005 standard Edition with SP2 Reply With Quote 09-16-08,05:45 #2 gvee View Profile View Forum Posts Visit Homepage www.gvee.co.uk Join Date Jan 2007 Location UK Posts 11,422 Provided Answers: 10 Someone correct me if I'm wrong - but I was taught that one of the first things to do when you get a new SQL Server is to password the sa account, disable it, and forget the password... George Home | Blog Reply With Quote 09-16-08,08:03 #3 Pat Phelan View Profile View Forum Posts Visit Homepage Resident Curmudgeon Join Date Feb 2004 Location In front of the computer Posts 15,579 Provided Answers: 54 My guess is that one client has the correct password