SQL User password stopped working with no trace of what happened

Question

SQL User password stopped working with no trace of what happened

APA0876

Hall of Fame

Points: 3853
More actions
March 14, 2012 at 3:14 pm

#391800

Hi
I have a sql User password that stopped working with no trace of what happened. Suddenly I could not log in to SQL using that user with the following error:
Msg 18456, level 14, State 1, Line 1
Login Failed for user ''
I checked the syslogins table and this user has never been changed or updated since 2009 (password was never touched), NO policies specified, the user was not locked as this is a SQL user.
I restarted the SQL Server service and still I can't login so I had to reenter the password and it worked.
I'm really concerned about this because I have no clue of what could have caused this situation.
I need your help
APA0876

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Login Failed for user '' looks a lot more like a blank username was passed to the server, rather than the password changing.

otherwise i'd expect the error to say Login Failed for user 'webdev' for example, isntead of an empty string.

does the username/password info come from a config file, like web.config, or some other file? maybe the file is missing or overwritten?

that's why i think when you changed the password, you rebuild the config/ini file with teh correct values.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

APA0876 Hall of Fame Points: 3853 More actions · Answer 2

Thanks for your prompt response,

The sql user password was not changed and was not working at all and the error was login failed. I tried log in using the SQL management studio with the password this user has always had since 2009 and I was not able to log in and as I mentioned I did not change the password and suddenly I could not log in with login fail and the password was never changed I checked all logs and syslogin table and the user was not updated since 2009.

So how can this happen? How a sql user password can get corrupted ?

How a SQL user password can changed without a user intervention?

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

have you removed the username from the error message or is the error saying its a blank user?

Msg 18456, level 14, State 1, Line 1

Login Failed for user ''

or

Msg 18456, level 14, State 1, Line 1

Login Failed for user 'MyUserName'

Also State1 is usually the client side error, if you take a look in the SQL server error log, what state does it say in there? As its the server state that will help in finding the error

APA0876 Hall of Fame Points: 3853 More actions · Answer 4

Hi

The error says:

Msg 18456, level 14, State 1, Line 1

Login Failed for user 'MyUserName'

and below what the SQL error log says:

Date3/13/2012 6:30:00 PM

LogSQL Server (Current - 3/14/2012 9:37:00 AM)

SourceLogon

Message

Error: 18456, Severity: 14, State: 8.

What do you think? As I mentioned I did not change the passoword since 2009 and now in 2012 almost 3 years later I could not log in with this password.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

State 8 is a password mismatch error, so something somewhere changed the password, unsure if this information is stored in the default trace but will most likely not be there so without some kind of auditing on SQL you wont find out what cause the password change.

only option is to change the password back to the old password or use the new password

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

anthony.green (3/15/2012)
have you removed the username from the error message or is the error saying its a blank user?
Msg 18456, level 14, State 1, Line 1
Login Failed for user ''
or
Msg 18456, level 14, State 1, Line 1
Login Failed for user 'MyUserName'
Also State1 is usually the client side error, if you take a look in the SQL server error log, what state does it say in there? As its the server state that will help in finding the error

Unfortunately the error indicates that there is a passwor mismatch. Only thing I can suggest is resetting the password.

APA0876 Hall of Fame Points: 3853 More actions · Answer 7

Hi,

Yes I reset the password to the same password I had and it worked but i need to know what happened as critical jobs failed because of this, I have remote users that connect to this server using a linked server and it has been working for years and suddenly this thing happened.

So do you think this is a bug?

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 8

something somewhere changed the password

take a look in the default trace file as it might still be in there if your lucky

but if not, without some sort of auditing on the server you wont know what changed the password, unless you setup auditing and wait for it to happen again

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I have to agree with Anthony. Something changed the password, it isn't going to change itself. I'm not sure if the default trace will be of help as I don't know exactly what it tracks. All you can do at this point is work on implementing processes (auditing) to capture and/or prevent it from happening again.

Ant-Green SSC Guru Points: 113507 More actions · Answer 10

looking at a default trace from my test bed it doesnt capture password changes.

it captures the audit login change property event and the details are "records modifications on login property except passwords for sp_defaultdb and sp_default_language"

so you will need to build in custom auditing to capture this in the future

APA0876 Hall of Fame Points: 3853 More actions · Answer 11

Yes I agree but if something or someone changed the password the syslogins table will be updated on regards that user and that table was reflecting on all dates the 2009 dates.

I was checking the default trace but it is to late I could not found anything.

this is so weird that I'm still so lost about it - still no clue

SS999 SSCommitted Points: 1663 More actions · Answer 12

You can create a trigger too, to prevent from having these kind of changes in the near future.