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.
Solution
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.
You can also read the below articles which is a comprehensive guide to fix this error 18456:
- How to Fix SQL Server Error 18456: A Comprehensive Guide
- Fix SQL Error 18456: failed to open the explicitly specified database
Now, let’s come back to this specific issue. 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.
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.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
6 Responses
[…] Fix Error 18456: Login failed for user ‘User_Name’. […]
[…] Fix Error 18456: Login failed for user ‘User_Name’. […]
[…] Fix Error 18456: Login failed for user ‘User_Name’. […]
[…] Fix Error 18456: Login failed for user ‘User_Name’. […]
[…] https://techyaz.com/sql-server/troubleshooting/login-failed–user_name-microsoft-sql… […]
[…] Fix Error 18456: Login failed for user ‘User_Name’. […]