Login failed for user - Error 18456 - Severity 14, State 38

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 1

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.

Answer 2

I had the same error with state code 38 caused by a typo of the name of the database in the connection string.

Answer 3

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

Answer 4

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.

Answer 5

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 (SERVERNAME\INSTANCENAME), I managed to connect to the server.

Answer 6

Well, this is database permission related error. I am adding my solution in this question because, it is very important to understand the role of DBA. So, there are my suggestions:

• Go to Server Manager
• Select Tools and then Computer Management
• Check the Local Users and Groups
• Check the admin name with description ‘Built-in account for administering the computer/domain’

Now, login with that username and you can grant access to the user. To do so:

Go to Security, right click on Logins and select New Login

In login window, you can type the user name or search it. Select the user from the list and click OK and OK again.

On the same login window, click on the Server Roles, and grant the access to the selected user like: sysadmin, serveradmin, decreator etc; click OK.

If you want to grant access to an individual database then, go to User Mapping and provide the access as per your requirement.

Check the Status of the database: Permission: Grant Login: Enabled

Click OK

Answer 7

@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 'CORP\anyuser'.

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.

Answer 8

In our case in the SQL Server Configuration Manager | SQL Server Network Configuration | TCP/IP | IP | The TCP Port was NOT set.

Without we could establish an ODBC connection and even use a remote SQL Management Studio connection and login with the user. However each time we accessed the url of our Tomcat application it launched an jdbc connection , then we spotted in the SQL Server Log the error : Login failed for user 'xxxx' : reason Failed to open the explicit defined database, error 18456 , Severity 14, State 38

Here an abstract of the jdbc connection string

"jdbc:sqlserver://10.10.10.10:1433;databaseName=zzzzzzz" username="yyyyyy" password="xxxxxx" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" defaultAutoCommit="true"/>
<Resource name="jdbc/application" 

This to say that also in the SQL Server Network Configuration | Protocols for < instance name> | Named Pipes has to be ENABLED & the Pipe Name \\.\pipe\MSSQL$<instance name>\sql\query has to be set correctly.

Answer 9

Another possible cause for this error: a login has just been added to an Active Directory group that has the correct permissions, but the AD token for the login hasn't been updated yet. Refreshing the token will fix the issue; one way to accomplish this is ask the user to log out of his or her workstation.