Error 18456 Severity 14 State 40
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, error 18456 severity 14 state 38 nt authority system performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft error 18456 severity 14 state 38 sql 2008 r2 MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide. Troubleshooting Error error 18456 severity 14 state 11 nt authority anonymous logon 18456 I think we've 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 18456 severity 14 state 5 login failed for user 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 conditions appear the same to the end user. In order to figure out what is really going wrong, you need to have alternative access
Error 18456 Severity 14 State 8 But Password Is Correct
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 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 i
> Security Problem Every once in a while you may run into an issue where a user cannot login to SQL Server, because the logins default database may not be available. This could be for several reason such as that database was dropped, the database may
Sql Server Error 18456 Severity 14 State 5
be corrupt, the database is offline, the user no longer has access to the database or even sql server error 18456 severity 14 state 16 because the database was renamed. When this happens the user will see an error message like the following on SQL Server 2005 and get a error 18456 severity 14 state 58 login failed for user 4064 error. Or this error message for SQL Server 2008 and get a 18456 error. Or if you are using a command line tool, you may see a text error like this: "Error: 18456, Severity: 14, State: 40. Login failed for user http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx 'sqldbpool'. Reason: Failed to open the database specified in the login properties. [CLIENT: 10.10.10.10]". Solution This issue may not come up that often, but if it does here is a simple fix. The user can connect to another database, such as the master and then the default database can be changed. Using SQL Server Management Studio Step 1: Open SSMS and click on "File" and then "Connect Object Explorer..." Step 2: Enter the server name, select the authentication mode, in this case I am https://www.mssqltips.com/sqlservertip/2131/sql-server-login-issue-with-default-database/ using SQL Server Authentication where I also need to enter a login and password. Then click on the "Options >>" button Step 3: On the "Connection Properties" tab, type a different database name into the "Connect to database" drop down. You should be able to just connect to the master database, since all users have access to that database by default or you enter another database that you know the user has access to. Here is what this looks like for both SQL Server 2005 and SQL Server 2008. Step 4: At this point you can work with SQL Server as normal. If you want to change the default database you can open a query window and execute the below query to change the default database for the login Note: sp_defaultdb will be removed from the future version of SQL Server so you will need to use the ALTER command. Be careful with the database you select. You can pick any database you want as the default database, but if the user does not have access to the database they will get the same error. EXEC sp_defaultdb 'sqldbpool', 'master' OR ALTER LOGIN sqldbpool with DEFAULT_DATABASE = master Using SQL CMD You can use SQLCMD or OSQL to login to SQL Server. You can use the -d switch, as shown below, to specify the database name. In this example I am connecting to the msdb database. Once you are connected you can do the same as Step 4 above to c
Related Tips: More > Error Logs Problem SQL Server Error Logs often show a message related to error 18456. Although it https://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/ generally means a login attempt from a client connection has failed, different State numbers associated with the error can mean different reasons for the failure. One of the error States is 38, which was added with SQL Server 2008, means the database being accessed cannot be found or does not exist. Unfortunately the Error Log entry error 18456 does not show what database it is, so in this tip 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 error 18456 severity 11:22:08.76 Logon Error: 18456, Severity: 14, State: 38. 2011-12-15 11:22:08.76 Logon Login failed for user '