In this article, you will be able to see how to troubleshoot when you are unable to connect to the database and getting an error message "Logon Login failed for user 'USER01'. Reason: Password did not match that for the login provided. [CLIENT: 10.xxx.xxx.xxx]."
Please allow me to explain you as per the error message it seems that Password you are providing for user "USER01" is not matching with actual one.
You can follow the below steps as a troubleshooting :
1. If you have SQL Server Management Studio, please re-try and test connectivity with the login and correct password.
2. If you are checking from Application Server then check the user / password settings with an udl file from the application server; just follow the steps below :
2.1) Create a new empty text file like "test.txt" (For example, right mouse click on an empty place on your desktop, select "New" and "Text Document")
2.2) Rename the file as "test.udl"
As soon as you renamed the file, text file icon should change to a UDL icon
2.3) Double click on test.udl file and you'll receive a window titled "Data Link Properties"
2.4) Provide the correct username and password; Test Connectivity from application server
3. You can check if there is any orphan user by the same login using below command:
-- command to check orphan user, this will give you the SID:
Use [YourDB_Name]
GO
EXEC sp_change_users_login 'Report'
GO
If that is the orphan user, than to fix the orphan user using below command:
-- First drop the user from login and then re-create using below script.
-- get the sid using above command for corresponding user
use master
go
CREATE LOGIN login name WITH PASSWORD = 'login password', sid='' -- use the same SID given by above script
GO
