Getting Error 18456 - Severity 14, State 38,How do I Fix SQL Server error 18456?

2.9K Asked by AnneBell in SQL Server , Asked on Jul 19, 2021

The 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 Here I want to ask, 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. hat is sql server 18456? How do I Fix SQL Server error 18456?

SQL Server 18456 Error: The generic message "Login Failed for User (Microsoft SQL Server, Error: 18456)" means you entered invalid credentials when logging into SQL Server. Here are the Troubleshooting Microsoft SQL Server 18456 Error

Answered by Ankit yadav

The 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 Here I want to ask, 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. What is sql server 18456? How do I Fix SQL Server error 18456? Keyword: sql server 18456

SQL Server 18456 Error: The generic message "Login Failed for User (Microsoft SQL Server, Error: 18456)" means you entered invalid credentials when logging into SQL Server.

Here are the Troubleshooting Microsoft SQL Server 18456 Error

Step 1: Log In with Remote Desktop.
Step 2: Run Microsoft SQL Server Management.
Step 3: Checking the Server Authentication Mode.
Step 4: Restart the SQL Service.
Step 5: Checking SQL User Permissions.
Step 6: Mapping the User to the Database.

Here are the 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. To find the status code, you need to inspect the SQL Server Error Log after ensuring that failed logins are recorded in the error log.

Your Answer

Answer (1)

Ranjana

SQL Server Error 18456 with Severity 14, State 38 typically indicates an issue related to the login process for the SQL Server. This specific state code means that the login request failed because the database requested by the login was not available. Here’s a step-by-step guide to troubleshoot and fix this error:

Step 1: Verify Database Availability

Check Database Status: Ensure that the database specified in the login request is online and accessible.

Open SQL Server Management Studio (SSMS).

Expand the "Databases" node and check the status of the database.

If the database is offline, bring it online by right-clicking on the database and selecting "Tasks" > "Bring Online".

Step 2: Check Default Database Settings

Check Default Database for the User:

The user’s default database might be set to a database that doesn’t exist or is not accessible.

Open SSMS and connect to the SQL Server.

Run the following query to check the default database for the login:

  SELECT name, default_database_nameFROM sys.server_principalsWHERE name = 'YourLoginName';

If the default database does not exist or is offline, change it to an accessible database using:

  ALTER LOGIN YourLoginName WITH DEFAULT_DATABASE = master;

Step 3: Review SQL Server Logs

Review Error Logs:

SQL Server logs can provide more detailed information about the error.
In SSMS, go to "Management" > "SQL Server Logs".
Look for the specific Error 18456 entries to get more context about the issue.

Step 4: Permissions and Access Rights

Verify User Permissions:

Ensure the login has the necessary permissions to access the specified database.
In SSMS, right-click the database, go to "Properties" > "Permissions", and ensure the login is listed and has the required permissions.

Step 5: Network and Connectivity Issues

Check Network Connectivity:

Verify that there are no network issues preventing access to the database.
Ensure that the SQL Server instance is reachable and that there are no firewall rules blocking the connection.

Step 6: SQL Server Configuration

Check SQL Server Configuration:

Ensure the SQL Server instance is configured to allow the type of authentication (Windows or SQL Server) being used by the login.
In SSMS, right-click on the server, go to "Properties" > "Security", and verify the authentication mode.

Step 7: Restart SQL Server

Restart SQL Server Instance:

Sometimes, restarting the SQL Server instance can resolve transient issues.
In SSMS, right-click on the server and select "Restart".

By following these steps, you should be able to diagnose and fix SQL Server Error 18456 with Severity 14, State 38. If the problem persists, further investigation into the specific server and environment configuration may be necessary.

6 Months