Question :
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 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.
Answer :
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.
To find the status code, you need to inspect the SQL Server Error Log after ensuring that failed logins are recorded in the error log.
I had the same error with state code 38 caused by a typo of the name of the database in the connection string.
Here is what I found when I resolved this error: I had created some SQL connections using windows login and not user name and password. Not really sure what or how it happened but it did. I deleted them and then rebuilt my entity models. I used another connection string that I knew to be good, and all is joy. Key is Persist Security Info=True, which will not work on a network or external web site
I had 18456 with state 38 as well. Turned out that the connection string had ‘integrated security=true’ which was not what I wanted. The connection string contained the userid and password that were to be used. When I changed the setting to false, all was well.
I got this error trying to log on to an SQL Server with multiple instances. The connection string didn’t name an instance. Once I named the instance (SERVERNAMEINSTANCENAME), I managed to connect to the server.
@Pete Oakly is on the right trail with the list of state code. Specifically, this is the code we’re all interested in:
38 ‘Login valid but database unavailable (or login not
permissioned)’
My problem started in a C# error log:
Exception|PublishedException|000161|10/29/2018|11:06:40.210|16284|InnerException|Cannot
open database “InterestingDataBaseName” requested by the login. The
login failed. Login failed for user ‘CORPanyuser’.
My eyes were focused on the “Login failed” part. My user was sa for this DB server, how could the login possibly fail?
I stumbled into this post by a trio of programmers that had the answer:
https://justaprogrammer.net/2012/12/09/a-misleading-sql-error-message-error-18456-severity-14-state-38/
It’s not the login failing per se, it’s the database that I’m trying to access. It does NOT EXIST!
Bingo! I had changed the database name for other reasons and didn’t update the configuration file. Fixed the DB name and things are working like a charm once again.