blog.sqlauthority.com
Open in
urlscan Pro
35.208.130.246
Public Scan
Submitted URL: http://blog.sqlauthority.com/2017/09/19/sql-server-login-failed-error-18456-severity-14-state-38-reason-failed-open-explicitl...
Effective URL: https://blog.sqlauthority.com/2017/09/19/sql-server-login-failed-error-18456-severity-14-state-38-reason-failed-open-explicitl...
Submission: On March 22 via manual from US — Scanned from DE
Effective URL: https://blog.sqlauthority.com/2017/09/19/sql-server-login-failed-error-18456-severity-14-state-38-reason-failed-open-explicitl...
Submission: On March 22 via manual from US — Scanned from DE
Form analysis 4 forms found in the DOM
GET https://blog.sqlauthority.com/
<form class="w-form-row for_text" action="https://blog.sqlauthority.com/" method="get">
<div class="w-form-row-field"><input type="text" name="s" id="us_form_search_s" placeholder="Search" aria-label="Search" value=""></div><a class="w-search-close" aria-label="Close" href="javascript:void(0);" data-wpel-link="internal"></a>
</form><form id="commentform" class="comment-form"> <iframe title="Comment Form"
src="https://jetpack.wordpress.com/jetpack-comment/?blogid=107185061&postid=179794&comment_registration=0&require_name_email=1&stc_enabled=1&stb_enabled=1&show_avatars=1&avatar_default=identicon&greeting=Leave+a+Reply&greeting_reply=Leave+a+Reply+to+%25s&color_scheme=light&lang=en_US&jetpack_version=10.7&show_cookie_consent=10&has_cookie_consent=0&token_key=%3Bnormal%3B&sig=a27ccb193cac8d593a1cb908f666351499bbedc6#parent=https%3A%2F%2Fblog.sqlauthority.com%2F2017%2F09%2F19%2Fsql-server-login-failed-error-18456-severity-14-state-38-reason-failed-open-explicitly-specified-database%2F"
name="jetpack_remote_comment" style="width:100%; height: 430px; border:0;" class="jetpack_remote_comment" id="jetpack_remote_comment" sandbox="allow-same-origin allow-top-navigation allow-scripts allow-forms allow-popups" scrolling="no">
</iframe> <!--[if !IE]><!-->
<script>
document.addEventListener('DOMContentLoaded', function() {
var commentForms = document.getElementsByClassName('jetpack_remote_comment');
for (var i = 0; i < commentForms.length; i++) {
commentForms[i].allowTransparency = false;
commentForms[i].scrolling = 'no';
}
});
</script> <!--<![endif]-->
</form>POST https://www.getdrip.com/forms/1200538/submissions
<form action="https://www.getdrip.com/forms/1200538/submissions" method="post" data-drip-embedded-form="1200538"><input style="width: 230px; height: 45px;" name="fields[email]" required="" type="email" value="">
<p></p>
<div style="display: none;" aria-hidden="true"><label for="website">Website</label><br> <input id="website" tabindex="-1" autocomplete="false" name="website" type="text" value=""></div>
<p><input class="w-btn us-btn-style_1 icon_none" name="submit" type="submit" value="Sign Up" data-drip-attribute="sign-up-button"></p>
</form><form id="drip-form-11714">
<div style="display: none">
<input type="hidden" name="form_id" value="11714">
</div>
<dl class="">
<dt class="label-for-type-email"> Email Address <span title="Required"> *</span>
</dt>
<dd>
<input type="email" name="fields[email]" value="" placeholder="" class="drip-text-field" id="drip-email-field-11714" tabindex="0">
<div id="drip-errors-for-email-11714" class="drip-errors"></div>
</dd>
<div style="display: none;" aria-hidden="true">
<dt for="website">Website</dt>
<dd>
<input type="text" id="website" name="website" placeholder="Website" class="drip-text-field" tabindex="-1" autocomplete="false" value="">
</dd>
</div>
</dl>
<div class="form-controls">
<input type="submit" name="submit" value="Send Me FREE Scripts NOW" id="drip-submit-11714" class="drip-submit-button">
</div>
<div id="drip-errors-for-base-11714" class="drip-errors dfwid-error"></div>
</form>Text Content
MAR 2022 DISCOUNT: COMPREHENSIVE DATABASE PERFORMANCE HEALTH CHECK |
TESTIMONIALS
* Consulting
* Training
* Free Videos
* All Articles
* Interview Questions and Answers
* SQL Tips and Tricks
* SQL Performance
* SQL Puzzle
* Big Data
* Blog Stats
* SQL Books
* Search SQLAuthority
* Downloads
* Hire Me
*
* Health Check
* Training
*
SQL SERVER – LOGIN FAILED – ERROR: 18456, SEVERITY: 14, STATE: 38 – REASON:
FAILED TO OPEN THE EXPLICITLY SPECIFIED DATABASE
September 19, 2017
Pinal Dave
SQL
21 Comments
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)
SQL SERVER – SHAREPOINT NOT WORKING AFTER FAILOVER TO ANOTHER NODE
Anything that can go wrong will go wrong while Murphy is out of town. While I
was onsite to help a client, one such issue had cropped up in their SharePoint
environment where SQL Server was used as backend and was a part of Windows
failover cluster. When SQL Server…
April 23, 2018
In "SQL"
SQL SERVER – LOGIN FAILED FOR USER . REASON: TOKEN-BASED SERVER ACCESS
VALIDATION FAILED
This is one of the most common error searched on my blog search
https://blog.sqlauthority.com/search-sqlauthority/ and lately I realized that I
have not written any blog about the cause of such error and fixing that. Let us
learn about how to fix the error login failed.
August 19, 2015
In "SQL"
SQL SERVER – ERROR AFTER CLUSTER PATCHING – ERROR: 5184, SEVERITY: 16, STATE: 2
During my last consulting engagement, I was pulled by my client to consider an
issue which they were facing. They informed that they have applied service pack
on one of their clustered environment and since than SQL Server is not coming
online. I asked to share ERRORLOG from the SQL…
December 13, 2016
In "SQL"
SQL Error Messages, SQL Login, SQL Server, SQL Server Security
Previous Post
SQL SERVER – The Cluster Resource ‘SQL Server’ Could Not be Brought Online Due
to an Error Bringing the Dependency Resource
Next Post
SQL SERVER – FIX: Msg 5123, Level 16 – CREATE FILE Encountered Operating System
Error 5
RELATED POSTS
SQL SERVER – FINDING LAST BACKUP TIME FOR ALL DATABASE
November 4, 2010
SQL SERVER – EXTENDED EVENTS TO WATCH FOR USING COLUMNSTORE INDEX
March 22, 2016
SQL SERVER – FINDING FRAGMENTATION IN FORWARDED RECORDS
February 9, 2021
21 COMMENTS. LEAVE NEW
* JD
September 20, 2017 5:14 pm
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.
Reply
* Ciril
September 26, 2017 11:18 am
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.
Reply
* Hemant Barot (Success)
January 25, 2019 3:23 am
what was the solution ?
Reply
* Andy
September 26, 2017 1:15 pm
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!
Reply
*
* Ciril
September 26, 2017 4:56 pm
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.
Reply
* advisorgee
October 21, 2017 2:57 am
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.
Reply
* Hiedi
November 1, 2017 7:16 am
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
Reply
* Megna
May 11, 2018 1:27 am
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.
Reply
* sam
December 20, 2021 7:40 pm
Did you find a solution for this??
Reply
* Jack Whittaker
June 28, 2018 2:26 pm
Error: 18456, Severity: 14, State: 38. also happens if the database is
offline
Not very likely, but worth a check
Reply
* Pinal Dave
August 16, 2018 10:51 pm
Agree, Thanks for adding this.
Reply
* Jonathan Pittman
July 25, 2018 9:46 pm
I have this problem as well – NT AUTHORITYNETWORK SERVICE is defined as
DBAdmin on the specified database yet I still get a login failed.
Reply
* Pinal Dave
August 16, 2018 10:51 pm
What is the exact error in ERRORLOG?
Reply
* Spencer Klein
September 4, 2019 8:14 pm
Login failed for user ‘NT AUTHORITYSYSTEM’ Reason: Failed to open the
explicitly specified database ‘database_Name’. [CLIENT: ]
* Tamer
December 9, 2018 8:18 pm
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!
Reply
* Pinal Dave
January 1, 2019 5:20 pm
Thanks for sharing the solution. You could also use SQL Login to connect to
SQL Server from application,
Reply
* Dan
March 11, 2019 5:46 pm
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.
Reply
* Daniel Serrano
July 25, 2019 4:56 pm
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.
Reply
* Uzer
August 22, 2019 5:43 pm
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]
Reply
* Filipe Pina
August 26, 2019 2:07 am
What client means?
Reply
* Devendra Sahu
April 21, 2021 9:32 pm
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: ]
Reply
*
LEAVE A REPLY CANCEL REPLY
Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant
with over 17 years of hands-on experience. He holds a Masters of Science degree
and numerous database certifications.
Pinal has authored 13 SQL Server database books and 40 Pluralsight courses. To
freely share his knowledge and help others build their expertise, Pinal has also
written more than 5,500 database tech articles on his blog at
https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to
flawless customer service. If you need help with any SQL Server Performance
Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer
(CF-L2).
--------------------------------------------------------------------------------
Nupur Dave is a social media enthusiast and an independent consultant. She
primarily focuses on the database domain, helping clients build short and long
term multi-channel campaigns to drive leads for their sales pipeline.
--------------------------------------------------------------------------------
EXCLUSIVE NEWSLETTER
Website
Is your SQL Server running slow and you want to speed it up without sharing
server credentials? In my Comprehensive Database Performance Health Check, we
can work together remotely and resolve your biggest performance troublemakers in
less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the
future.
--------------------------------------------------------------------------------
Have you ever opened any PowerPoint deck when you face SQL Server Performance
Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST
popular training with no PowerPoint presentations and 100% practical
demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
* SQL Interview Q & A
* Testimonials
* Search
* Privacy Policy
© 2006 – 2022 All rights reserved. pinal @ SQLAuthority.com
Menu
Go to mobile version
3 FREE SCRIPTS TO INSTANTLY
IMPROVE SQL SERVER PERFORMANCE
3 FREE SCRIPTS TO INSTANTLY
IMPROVE SQL SERVER PERFORMANCE
Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.
Email Address *
Website
CHECK YOUR EMAIL!
It has 3 Free Scripts to Instantly Improve SQL Server Performance.