How to Fix SQL Server Login Failed Error 18456 State 38

One of the most common issues faced while connecting to a SQL Server instance is "Login failed for user 'username'. (Microsoft SQL Server, Error: 18456)". In addition to the Error No. SQL Server will provide an Error State no. which will be helpful to identify in more detail about the Login Authentication failure. Majority of the Authentication related errors might arise due to incorrect username or password, disabled SQL Authentication mode, expired password, deleted user account or non-existent database.
Please find below the various Error State no. for Error no. 18456 from MSDN below.


In this article, we’ll take a closer look at State no. 38 for error 18456 while trying to authenticate on Microsoft SQL Server and address how to fix it. As shown in the Error State above, we can receive Error State 38 whenever the database requested wasn't available in that instance and hence can happen for even SA or any domain administrator account. For example, if you run Microsoft SQL Server Management Studio and try to login to the SQL server with a domain administrator account, the following error may appear:

TITLE: Connect to Server
------------------------------
Cannot connect to MySQLServer\SQLInstance.
------------------------------
ADDITIONAL INFORMATION:
2020-12-15 12:19:34.56 Logon      Error: 18456, Severity: 14, State: 38.
2020-12-15 12:19:34.56 Logon      Login failed for user '<SQLDomain>\<Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: XXX.XX.XX.XXX]

Fixing the error 18456, Severity: 14, State:38
As explained above, error state number 38 clearly mentions that the Specified database does not exist or inaccessible. Please be noted that the Error State no and Description can vary across SQL Servers like States 38 and 40 (in SQL 2008 and later) and States 16 and 27 (before version 2008) to indicate that the specified database does not exist. Few possible reasons include the database could be offline, shutdown, deleted, dropped, renamed, auto-closed, or inaccessible for some other reason like corruption.
Now to fix this error, we have to attempt to log into the SQL Server to understand what is going on with the specified database. You will notice in the error that the name of the database is not specified. What if you do not know the database the log on user is attempting to connect to? In such scenarios, in SQL Server Management Studio (SSMS) connect to that instance using an Admin account and in object explorer expand databases to see if that database exists and what state is in.

We can also achieve the same using T-SQL by opening a new query window and running the below code and check the State_Desc column as explained in my previous article SQL Server Database Health Checks(link in reference section)

SELECT * from sys.databases

Depending on the results, we can identify how we need to fix the problem.
For example
1. If the database was renamed, then connect to the correct database.
2. If the database doesn't exist, then check with your DBA to find out why your database was missing.
3. If the database is OFFLINE, then check with your DBA to bring the database ONLINE.
4. If the database is corrupted and couldn't start properly, then we can encounter the below issue as well. In order to fix the corruption issues, we need to run DBCC CHECKDB to identify what levels of database corruption was there. Command used to perform DBCC CHECKDB provided below for reference and precautionary steps to be taken while executing DBCC CHECKDB was explained in my previous article Estimate Execution time of DBCC CHECKDB(link provided in reference).

DBCC CHECKDB

Executing DBCC CHECKDB provided the below results indicating clearly that there was a database corruption.


To recover from the corruption, we can try recovering the database from recent backups if available or else execute DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS option.

DBCC CHECKDB(MyTestDB, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Execution of DBCC CHECKDB completed successfully resolving the corruption issues and showed no errors bringing the database back online allowing users to login successfully.
Conclusion
As you would have noticed, understanding the Error Message and State no. provided by SQL Server is critical and once identified properly, we can resolve a complex Database corruption issue successfully. As explained in my article about DBCC CHECKDB command, we should be more careful while executing it as it has potential risks to lose data in case of complex corruptions. What will happen if DBCC CHECKDB execution wasn't successful or the server doesn't have enough resources to execute DBCC CHECKDB? If you ever encounter those scenarios, then try to restore from the scheduled Full and Transactional Log Backups (which is a must responsibility for every DBA).
If by some chances, you don't have a Scheduled Backup in place, then I would like to introduce to you a robust SQL Database recovery tool that will not only fix your database with the least minimal data loss and potentially help you restore deleted records from the database. Click on the product to learn more about the new and improved Stellar SQL Database Recovery Tool.

References:
1. MSDN Article to show various possible Error States - https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver15
2. SQL Server Database Health Checks - https://www.experts-exchange.com/articles/35491/SQL-Server-Database-Health-Checks.html
3. Estimate Execution time of DBCC CHECKDB - https://www.experts-exchange.com/articles/35174/Estimate-Execution-time-of-DBCC-CHECKDB.html