SQLactions.com   An action flick on SQL Server

Here is one more interesting situation I came across while working with one of our client.

Whenever our dear friend was trying to connect from Management Studio it was throwing below error.

Error Message:  Login failed for user ‘<SQL Login>’. (Microsoft SQL Server, Error: 18456)

Hmm…Looks to be easy one, right?? But it was not that easy trust me.

Here is my approach starts. We started with ERRORLOG to check the State of Error message.

2012-05-02 14:44:52.76 Logon Error: 18456, Severity: 14, State: 8.

2012-05-02 14:44:52.76 Logon Login failed for user ‘SQL LOGIN’. [CLIENT: <local machine>]

So as per our basic troubleshooting I tried to understand what this error message stands for.

Login failed will always have Error number 18456 but important thing to note here is the State of error message

Error: 18456, Severity: 14, State: 8

Now according to our Connectivity blog

State 8:- Password mismatch. Ah it’s really easy one.

We told our client “Sir, Please enter your password correctly”

Client’s response “are you kidding me, you mean I am not entering password correctly?”

We said “Well, this is what error messages says Sir!!”

Client “it’s impossible I am confident that I am entering password correctly, same thing used to work 2 days before. I can do a copy paste in front of you”

He did the same, copy paste the password and it did not work. Now this is not a JOKE ANYMORE!!!

Here we start with our typical troubleshooting steps.

First thing to note here is, its SQL Login not a windows login, so could be a possibility due to some reason we are getting some junk character in the password?? Can Collation be a problem??

No, because as per him it was working fine 2 days before and suddenly it’s not working. So there could be some other problem. So we suggested him to reset the password.

Resetting the password did not help. So we dropped the user and recreated with a simple password “aaa” . Still it did not work L

We tried to check if we can connect form SQLCMD and It worked!!!

Then we created UDL that worked too!!

In case you are new to UDL, below are the Steps to create UDL.

On Desktop write click and click on New file New Text Document.txt. Rename it to test.udl

Enter the server name, username and Password Click on Text Connection.

This also worked fine for us. So here we got to know there is something to do with Management Studio Only. We decided to collect Process Monitor (you can download this tool from www.sysinternals.com ) to see if we get some clue there. Procmon had some clue but we could not able to identify it at that point (will talk more about this later in this blog).

Since the issue was happening only with Management Studio and according to customer issue started after applying a windows service pack, suddenly it came to my mind, why don’t we try it from Management Studio of other machine??

We tried from Management Studio of other machine and Guess what?? It Worked from there as well!!!

Then the question is why it is not working particularly from original server where we started troubleshooting. We decided to collect one more procmon from both the machine and compared both the procmon (good machine and bad machine)

While comparing procmon we find in the registry, in below key AppData was pointing to the location that does not exists on the server where we had login problem.

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData

This looks like some problem with current user profile. We asked him to create a test user (Windows User) and login with that user.

He created “test” as Windows User and logged in with that user and OH la la it worked fine.

This confirms that this user profile is corrupted and hence it is causing issue. We decided to rename the user profile folder “C:\Users<useraccount>” ( Please do not delete it just rename it, as it will have desktop folder where user could have some useful data), On renaming when user logs in back to the server if logon process does not find the profile folder for that user account it will create the new one with same account name.

So when customer logged in again after renaming the useraccount folder inside “C:\Users\”. The logon process will create new folder with same account name inside “C:\Users\” as mentioned above.

Now when you open Management Studio and try with the <SQLLOGIN> it works like CHARM!!!

This is one of the login failed troubleshooting but this can help you in trouble shoot other login failed issues as well.

Hope you will find it useful!!!