23

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.

25

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.

  • @PeteOakly you're on the right trail here for state 38. See the post below about a missing database as one of the reasons for state 38. – John Dyer Oct 29 '18 at 18:02
4

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

2

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

2

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.

2

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.

2

@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.

1

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.

1

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

  • 2
    This could be a pretty good answer if you could put the focus on the possibility of a missing SQL Server Login to Database User relationship or the possibility that the SQL Server Login's default database is no longer available. Currently your answer is just a generalised recommendation on how to create an account. I'm not sure how the first part of your answer would help resolve OP's issue. Could you explain that part? – John aka hot2use Dec 10 '18 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.