Fix SQL Error 18456: failed to open the explicitly specified database
This article will help you fixing SQL Server Error: 18456, Severity: 14, State: 38, Reason: Failed to Open the Explicitly Specified Database. We were getting below error in our SQL Server error log for one of our login.
Login Failed – Error: 18456, Severity: 14, State: 38
Reason: Failed to Open the Explicitly Specified Database
Fix Error “Failed to Open the Explicitly Specified Database”
The error log is saying “Failed to Open the Explicitly Specified Database“, it means either your have specified wrong database in your connection string or your login does not have access to the specified database. You can validate below options to isolate this issue.
- Check whether you have specified correct database in your connection string or not
- If you are using correct database then whether the login you are using to establish database connection has access to this specified database or not.
- Correct if you find something in any of the above two options.
- Check login properties whether it is using correct database as default database configuration or not. If correct database is defined then login has access to that database or not. I will explain about this step in below section as well.
- You should also check whether number of connections to the database is exhausted or not. Review your maximum number of connections setting.
- Ensure your database is not set to AUTO CLOSE setting or not inaccessible. Database should be online and healthy state to accept connections.
- Make sure you have a trust between both machines, database server and the machine from where you are trying to connect. I would suggest you to read below article as well where i have explained various steps to troubleshoot SQL Server connection issues.
Read more articles on connection issues:
- How to fix SQL Server error 53 – Not able to open database connection
- Fix SQL Server error 18456 – Login failed for user
Let’s learn how to check default database settings for a SQL Server login and a user mapping for any database to the login.
Check Login Properties
The solution to fix this issue can also come by reviewing the properties of this login. Launch its properties window and change the default database to the correct one if this setting is showing wrong database. Sometimes, we drop the databases from the SQL Server instances but never update other objects. If this login is not showing any database and it is showing blank then click at its drop-down to choose your desired database as the default database for this login. Click on Ok button to apply that change. You will stop getting this issue after making this change.
You can open login properties windows by right click at the identified login and then choose properties from dropdown options.
Sometimes, you have chosen the default database as “master” but your login doesn’t have connect permission on master database so ensure you have at least connect permission on the database which you are choosing as the default database for your login. You can click at “User Mapping” tab from left side pane of the login properties window. You will get all databases in top side of right side pane and level of access in bottom side pane. Click at your database from right side pane along with its level of permission which you want to assign. Click at Ok button to apply this change.
Have a look at below screenshot to understand default database of a login and from where you can access User Mapping tab to provide appropriate access to your database. Your login should have access to both databases, the one mentioned as default database for your login and the specified database in connection strings.
You can fix SQL error “Failed to Open the Explicitly Specified Database” by following above steps. Write your feedback if you have fixed this issue by other way. I will update this article accordingly with credits.
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021