When trying to connect to the SQL Server, you may encounter the SQL Server database error 18456. The complete error message reads as follows.
"Login failed for user 'Username'. (.Net SqlClient Data Provider)"
"Server Name: yourServer"
"Error Number: 18456"
"Severity: 14"
"State: 1"
"Line Number: 65536"
In the above message, “State 1” means that you cannot see more details about the error due to permission restrictions. “Severity 14” is associated with security errors, like permissions.
Causes behind the SQL Server Database Error 18456
This error usually occurs when you try to log in to SQL Server with SQL Server Management Studio (SSMS) or another tool and the login fails. The most common reasons behind this problem are:
- The login is wrong.
- The password is wrong.
- SQL Authentication is not enabled.
Solutions to Resolve the SQL Server Database Error 18456
Here are some solutions to fix the error 18456 in SQL Server
Verify the Login
To check if the login is correct, type the login name again correctly and make sure there are no special characters.
To verify that the login is correct, go to Security > Logins in SSMS and verify that your login name exists.
If you cannot see the login, ask the database administrator for help. Another way to verify the login is using the following query:
select name from sys.sysloginsAlternatively, you can use the stored procedure
sp_helploginsIf you need to modify the existing login name in SSMS, right-click the login and select Rename.
Alternatively, you can change the login using the ALTER LOGIN statements.
ALTER LOGIN User1 WITH NAME = tester;The above statement will change the login name ‘User1’ to tester.
If you have a Windows login and not a SQL Server login, you need to coordinate with the system administrator to modify the Windows Account.
SSMS can detect the current Windows user automatically. However, other tools may require you to enter the login manually. If that is the scenario, there are chances that you can write an incorrect login. Make sure to check that the domain is correct and the user name is correct.
If you are not sure about your login name, you can go to the command-line tool and write whoami and the command prompt will return your Windows login.
Check the Password to fix SQL Server Database Error 18456.
If you’ve entered a wrong password, you only need to enter it again correctly. If not, you will need to coordinate with the system administrator to modify the password if you do not have enough privileges.
If it is a SQL Server login, you can modify the password by right-clicking the login and selecting properties.
In the General page, you can modify the password.
If your login is an Active Directory account, you need to contact the system administrator in charge of the Active Directory accounts.
Another way to change the SQL Server login password is using the T-SQL. The following code illustrates how to do it.
ALTER LOGIN testuser WITH PASSWORD = 'Write$#”paSsw23”ord';Note: If you need to recover and reset multiple passwords in SQL Server, you can use Stellar Password Recovery for MS SQL which is a tool included in Stellar Repair for MS SQL Technician
Enable SQL Authentication
By default, SQL Server is installed with Windows Authentication only. This means that you can only log in with Windows users. However, there is an option to create SQL Server database logins. If this option is not enabled, you may receive the 18456 error.
The following steps show you how to enable SQL and Windows Authentication.
- Right-click the server and select properties in the Object Explorer in SSMS.
- In Properties, go to the Security page and select the SQL Server and Windows Authentication mode.
- SSMS will ask you to restart the service. Confirm to restart the service.
Enable Contained Databases
Sometimes, the login is lost because the database was migrated as a contained database. There is an option in SQL Server to have a database user without login. By default, when you create a login, you also have a user per database associated to that login.
When you migrate a database to a different server, you need to migrate the login as well. That is why Microsoft included the contained databases. This option allows to have an isolated database easier to migrate.
In order to enable a contained database,
- Go to SSMS.
- Right-click the SQL Server and select Properties.
- Go to the Advanced page and set the Enable Contained Databases to True.
Alternatively, you can use the T-SQL commands.
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
Conclusion
The SQL database error 18456 usually occurs due to a security login problem. Above, we’ve mentioned the solutions to resolve the issue, such as verifying and modify the login, check the password, and change the authentication mode. In addition, we mentioned a third-party SQL password recovery tool that can help recover and reset lost passwords of SQL Server database.
