Those who are my regular clients would know that I am very active in replying to emails. My average time of response is around 24 minutes. Many of the emails are for suggestions and I don’t get much time to help everyone, but I do reply to them letting them know the alternatives. If you are following my blog, you would know that I do provide “On Demand” services to help critical issues. This blog is an outcome of one of such short engagement about login failed.
One of my client was worried about login failed messages which they were seeing in the SQL Server ERRORLOG file.
2017-09-11 04:53:19.880 Logon Error: 18456, Severity: 14, State: 38.
2017-09-11 04:53:19.880 Logon Login failed for user ‘GLOBAL\PORTAL01$’. Reason: Failed to open the explicitly specified database. SharePoint_Config’ [CLIENT: ]
As per them, there is no complaint from anyone about any issue, but those messages are not looking good.
WORKAROUND/SOLUTION
First, we needed to figure out the account which is trying to access. If you look at the account which is shown in the error message is ending with “$” which means a machine account. In our case PORTAL01 was a front server in the SharePoint farm. This comes when there is some service, running under Local System account, it is trying to connect. After digging further, I found that this was SharePoint server which was trying to connect.
State 38 of Login failed, is logged when the account is having insufficient access to the database (SharePoint_Config). To fix it, we connected to SQL Server using SSMS and navigated to the Security > Logins > Right click on the account, and went to properties. We clicked on “User Mapping” tab and there we saw that the login was not mapped with the database SharePoint_Config. We noticed that it was only mapped to the master database. Now, this explains the cause of the error messages in ERROLROG.
Once we mapped that login to that mentioned database, we stopped receiving login failed error messages.
If you are having any quick issue to resolve, you can also avail the same kind of services. Click here to read more about it.
Reference: Pinal Dave (https://blog.sqlauthority.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
I have this problem as well – NT AUTHORITYNETWORK SERVICE is defined as DBAdmin on the specified database yet I still get a login failed.
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!
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.