Error 18456 State 38 is good news dressed as bad news. The login itself worked. What failed is in the log’s own words: failed to open the explicitly specified database. That narrows the problem from anything at all down to four things, and the state number is what tells you.
What the Application Sees, and What You See
The application gets almost nothing:
Login failed for user 'DOMAIN\someone'. (Microsoft SQL Server, Error: 18456)No state, no reason. That is deliberate. Telling a stranger at the door exactly why they were refused helps them guess better next time.
The error log gets the full version:
Login failed for user 'DOMAIN\someone'. Reason: Failed to open the explicitly
specified database 'Sales'. [CLIENT: <client address>]
Error: 18456, Severity: 14, State: 38.So the first move is always the same. Stop reading the application error and go and read the log.
Reading the Log
EXEC xp_readerrorlog 0, 1, N'Login failed', NULL, NULL, NULL, N'DESC';That reads the current log, filtered, newest first. If the server was restarted since, walk back through the older files by changing the first number.
The log path is worth knowing too:
SELECT SERVERPROPERTY('ErrorLogFileName') AS errorlog;What State 38 Actually Means
The login passed. The password was right, the Windows token was accepted, the account is not disabled. Then the connection asked for a specific database and could not have it.
Where did it ask? Usually the Initial Catalog or Database part of the connection string. Sometimes the login’s default database, when the connection string names none.
The Four Causes, in the Order I Check Them
The database does not exist. Nearly always a typo or a name that changed. Check the spelling against the real list.
SELECT name, state_desc, user_access_desc, is_read_only
FROM sys.databases
ORDER BY name;The database exists but is not usable. Offline, restoring, recovering, suspect, or in single user mode with somebody else connected. The state_desc and user_access_desc columns above answer this in one look.
The login has no user in that database. This is the most common one on a server where a database was restored from somewhere else. The login exists at server level, but inside the database there is no matching user, or the user is orphaned because the security identifier no longer matches.
USE Sales;
SELECT dp.name AS database_user, dp.type_desc, sp.name AS server_login
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.type IN ('S','U','G')
ORDER BY dp.name;A row where server_login comes back NULL is an orphaned user. The user is in the database but points at a login that is not there any more.
The user exists but cannot connect. CONNECT permission can be denied on the database even when everything else is in place.
Fixing Each One
For a missing user, create it and map it:
USE Sales;
CREATE USER [DOMAIN\someone] FOR LOGIN [DOMAIN\someone];For an orphaned SQL login, point the existing user back at the login rather than dropping and recreating it, which would lose its permissions:
USE Sales;
ALTER USER AppUser WITH LOGIN = AppUser;For a denied connection:
USE Sales;
GRANT CONNECT TO [DOMAIN\someone];The Default Database Trap
This one wastes an afternoon regularly. A login whose default database was dropped or taken offline fails at connection time even when the application never named a database.
SELECT name, default_database_name, is_disabled
FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE '##%'
ORDER BY name;Pointing a login’s default at master is a reasonable habit for exactly this reason. The application should name the database it wants anyway.
The Other States Worth Recognising
State 5 means the login does not exist. State 7 means it exists but is disabled, or the password is wrong on a disabled account. State 8 is a wrong password. State 11 and 12 mean a valid Windows login with no permission to connect. State 18 means the password is correct but expired.
State 1 tells you nothing, and that is the point. It is what you get when the server declines to say more.
State 38 is not a login problem, it is a door that opened onto a room that was not there.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
22 Comments. Leave new
I had this issue as well but not with a Sharepoint database. I had a dev config database which I wanted to rename but it didn’t work as I the database was in use. I switched the database to single user mode and rename it and the I could not access the database anymore. I also could not view the properties or any data in any table as it said the connection was broken. I then restarted the services and then I could not even start sql anymore.
I had to change the password of the service account, restart sql server, then I could rename the database back to the original name. At that point I could still not view the properties, so I changed my connection to an sa user, used transact sql to change the mode back to multi user, and then everything was back to normal.
I have very similar error:
“Severity 014 – Insufficient Permission’ occurred on \\Server
“Login failed for user ‘WindowsAuthenticationUser’. Reason: Failed to open the explicitly specified database ‘FirstDatabaseInAlphabeticalOrder’. [CLIENT: xx.yy.zz]”
I noticed that this error occured on those production servers that were upgraded from SQL 2008 R2 to SQL 2014. There are many databases on those servers and some users are connecting directly to databases using Windows Authentication via SSMS. They don’t have problems using databases that has permission to. But when connectiong to server, this error is written to the log. And it is always the first database in alphabetical order. Users don’t have permission on this first alphabetical order database, but why SQL Server would throw this error as this database is not listed as users default database?
To put it simple: users successfuly acces database C, but the error for database A is written to the log each time they access this server. Users has permission for database C and they don’t have permission for database A.
what was the solution ?
No comment about what a crazy security setup this is? A service, running under local system credentials, connecting through to the backend database…. terrible, don’t do this!
Hello, Andy.
Thanks for your response but I don’t know where you can see from my post that service is running under local system credentials? And what crazy security setup are you talking about?
We have a few users from IT department which are accessing databases via SSMS. And when those users want to do something on, let’s say, database C (on which they have permission for select and update on certain tables), they can do that, but above error is written to log for database A (on which they don’t have any permission). This is only happening on servers that were upgraded from SQL 2008 R2 to SQL 2014.
I’m having the same issue, but the workaround does not work for me. In our case the computer account is the one accessing the database (yes I know using a local system account to access a database is horrible security, but i didn’t write the app, nor can I change it).
This didn’t happen in SQL2014, it just started happening with SQL2016.
I was receiving the same error because i had deleted the reporting database. I restored that database and stopped receiving that error. but if i want not restore my database what should i do
I am getting same Error “Login failed for user ‘XXX’. Reason: Failed to open the explicitly specified database ‘XXX’. [CLIENT: XXX.XXX.XXX.XXX]”,
Error: 18456, Severity: 14, State: 38
Database is Configured as Mirroring in (Restoring State), Login is SysAdmin on the Server but still i am getting this Error tried to find why this Error is occuring but could not find anything, if you can help that would be great.
Did you find a solution for this??
Error: 18456, Severity: 14, State: 38. also happens if the database is offline
Not very likely, but worth a check
Agree, Thanks for adding this.
I have this problem as well – NT AUTHORITYNETWORK SERVICE is defined as DBAdmin on the specified database yet I still get a login failed.
What is the exact error in ERRORLOG?
Login failed for user ‘NT AUTHORITYSYSTEM’ Reason: Failed to open the explicitly specified database ‘database_Name’. [CLIENT: ]
After about 8 hours with trying all solutions possible, it worked for me, but I did something that I didn’t find on the net. First I granted “IIS APPPOOL\DefaultAppName” a permission to read write and every thing on application root, this I found on microsoft docs page, then I created a user for the specific database under security, and called it “IIS APPPOOL\DefaultAppName” with db owner membership and it finally worked!
Thanks for sharing the solution. You could also use SQL Login to connect to SQL Server from application,
In our case, issue was related to database auto close, when our end user working offline, this error occurred. the error which shows in error log as “starting up database Domain\”, In sqlcmd it shows as “SQL SERVER – Login Failed – Error: 18456, Severity: 14, State: 38”, then we executed “ALTER DATABASE SET AUTO_CLOSE OFF WITH NO_WAIT” to turn off auto close. viola it works perfectly.
In my case, I got a bunch of these errors but no database name reported.
Problem started when changed Windows’s Server physical name (computer name), changed applied, SQL was working ok, but the SQL ServerNameinstance was the old computer’s name, so started to fire all those errors.
Solution was renaming SQL ServerName to new computer’s name
Execute below to drop the current server name
EXEC sp_DROPSERVER ‘oldservername’
Execute below to add a new server name. Make sure local is specified.
EXEC sp_ADDSERVER ‘newservername’, ‘local’
Restart SQL Services.
Verify the new name using:
SELECT @@SERVERNAME
SELECT * FROM sys.servers WHERE server_id = 0
I must point out that you should not perform rename if you are using:
SQL Server is clustered.
Using replication.
Reporting Service is installed.
Mine was a standalone SQL Server 2008
Erros in log gone.
I have lot of such errors but I want to know will these errors affect the SQL performance related to High IO or Memory
Error: 18456, Severity: 14, State: 38.
2019-08-22 12:13:10.03 Logon Login failed for user ‘username’. Reason: Failed to open the explicitly specified database ‘Database Name’. [CLIENT: x.x.x.x]
What client means?
When Mantance plan run that time in log even it showing
Date 21-04-2021 20:48:27
Log SQL Server (Archive #1 – 21-04-2021 21:09:00)
Source Logon
Message
Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: ]
I had this issue when remotely deploying a new database. I thought it was a strange error as the database had not even been created yet.
The issue was the version of DAC Framework (110) used to remote deploy (sqlpackage.exe) was not compatible with the target SQL server version (2019). I updated DAC Framework to 150 and deployment succeeded. Error was misleading.