Fix SQL Server Error 18456: Login failed for user ‘User_Name’.
Yesterday, a developer has reported an SQL Server error 18456 Login failed for user ‘username’. (Microsoft SQL Server, Error: 18456) on one of the SQL Server instance that are running in lower life cycle. The details about SQL Server error 18456 are given below with the screenshot.
Cannot Connect to ‘SERVERNAME’.
Login failed for user ‘username’. (Microsoft SQL Server, Error: 18456)
SQL Server Error 18456 – Login failed
Initially, I thought login failed SQL error 18456 was reported because user might have entered wrong password, but when I tried it with correct password, it displayed same error again. Read this article to fix this error.
When I received same MSSQL error 18456 even after entering correct login id and password then we have decided to check the server authentication mode configured for this instance. Here we got the answer because this SQL Server instance was configured to run in only Windows Authentication mode, that is why SQL Server database engine was not allowing us to login using SQL Server login that comes in to Mixed authentication mode.
As we know there are two types of authentication mode in SQL Server.
- Windows Authentication Mode
- SQL Server and Windows Authentication Mode (Mixed Authentication Mode)
Only windows login accounts can connect to SQL Server if your instance is configured in Windows Authentication mode but when Mixed Authentication mode is configured you can connect using windows and SQL Server logins both way.
So, to fix this SQL Server error 18456 ( Login failed error 18456 ), we have two options:
- Either Change Server Authentication mode of this SQL Server Instance to Mixed mode (SQL Server and Windows Authentication Mode)
- Or, always use windows login or Domain login account to connect to SQL Server Instance.
Here, we have decided to change the Server Authentication mode from Windows Authentication to Mixed mode authentication. Below are the steps:
- Right click on SQL Server Instance name in SSMS and choose properties.
- Select Security from left side pane.
- You can see Server Authentication mode is set to Windows Authentication mode in below screenshot.
- Change this to SQL Server and Windows Authentication mode that is second option in above screenshot and click on OK button.
- Restart SQL Server and SQL Server Agent services to apply the changes to database engine.
- Now you would be able to login to your SQL Server Instance using SQL logins as well.
Here, i have shown you how to fix a SQL Server error 18456 by changing its authentication mode. Read more articles in related section given below.
- Fix Error 15170: Login owns one or more Agent Jobs
- Fix Login failed Error 4064: Cannot open user default database
- How to fix error 28 – Server doesn’t support requested protocol
- Fix Error 18452: Login Failed
- 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