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.

1. Windows Authentication Mode
2. 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.

Solution

So, to fix this SQL Server error 18456 ( Login failed error 18456 ), we have two options:

1. Either Change Server Authentication mode of this SQL Server Instance to Mixed mode (SQL Server and Windows Authentication Mode)
2. 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:

1. Right click on SQL Server Instance name in SSMS and choose properties.
2. Select Security from left side pane.
3. You can see Server Authentication mode is set to Windows Authentication mode in below screenshot.
   
4. Change this to SQL Server and Windows Authentication mode that is second option in above screenshot and click on OK button.
5. Restart SQL Server and SQL Server Agent services to apply the changes to database engine.
6. 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.

Related Articles

• 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

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

• Author
• Recent Posts

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.

Follow me:

Latest posts by Manvendra Deo Singh (see all)

• 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

Summary

Article Name

Fix Error 18456: Login failed for user 'User_Name'.

Description

Initially, I thought login failed 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.

Author

Manvendra Deo Singh

Publisher Name

www.techyaz.com