SQL Server Error 18456: A Solution
SQL Server Error 18456: A Solution
In this article, we will explain how to fix the SQL Server Error 18456 and describe the error and show different ways to solve this error.
Join the DZone community and get the full member experience.Join For Free
In this article, we will explain how to fix the SQL Server Error 18456. We will first describe the error and then we will show different ways to solve this SQL Server error using SQL Server commands, backups or external software.
The SQL Server Error 18456
A typical error message when you try to connect is the following:
TITLE: Connect to Server
Cannot connect to SERVERNAME
Login failed for user "username." (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?%20SQL%20Server&&&
The error 18456, is a typical login error. There are multiple reasons you might see this message. Here, you have some possible reasons:
- The login does not exist or was not typed correctly
- The user forgot the password or login
- The password is incorrect
- The Windows Authentication is not in Mixed mode
- A malicious hacker reset the password
- A virus resets all the passwords
- The database was migrated, but the logins were not migrated
- The administrator modified the passwords by mistake
- The logins were damaged or the master database is damaged
There are many different types of solutions for these scenarios. Here, we will show the most popular solutions.
Reset the Password
If you forget your password, you can ask your DBA to reset your account. The easiest way to reset the password is by using SQL Server Management Studio (SSMS). Go to security and Logins:
Select the login and you can change the password:
If you do not like to use SSMS, you can use T-SQL to create users and change the password:
USE [master] GO ALTER LOGIN [pedro] WITH 'newpassword' GO Change Windows Authentication
Another typical problem for the error is that you are trying to log in with a SQL login, but the SQL login authentication is not enabled. In SQL Server there are two types of authentication:
- Windows Authentication that uses a Windows local or domain account to login. This option is secure and recommended.
- Mixed mode is the second option that supports Windows and SQL Authentication
If you are trying to log in with a SQL login, but the Authentication is set to Windows, you will receive the error message.
You can change the authentication mode using SSMS. Right click the SQL Server and select properties. In the properties go to Security and change the Windows Authentication to SQL Server:
This option will require to restart the SQL Server Service.
If you do not want to use SSMS, there are other options to change the SQL Server authentication.
You can use the registry to modify the authentication mode. Use the regedit to change the registry:
machine\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQLServer
Change the login mode value.
- 2 is mixed mode.
- 1 is Windows Authentication.
Stellar SQL Database Toolkit
Another possible solution is to use the Stellar SQL Database Toolkit. This software contains 3 components. We will use the SQL Password Recovery to recover the password from a specific login. The other tools are used to recover the database and the backups. We will not cover them in this article.
Once downloaded, open the software and open the SQL Password Recovery:
This software is compatible with SQL Server 2012, 2008 and 2005:
This software looks for the master database. This database stores the logins and passwords. You can retrieve the logins information by querying the following view:
USE [master] GO select * from sys.sql_logins
The software will look at the master database data file (master.mdf) and get the password. As you can see, it is a straightforward process.
In this article, we learned the different reasons you might receive the SQL Server Error 18456. One of the reasons is that the login or the passwords are wrong. Another reason to receive this error is that the SQL Authentication is not enabled. We show how to solve this problem and learn how to retrieve the passwords using the Stellar SQL Database Toolkit.
If you have questions or suggestions regarding this article, please feel free to comment.
Opinions expressed by DZone contributors are their own.