Post reply

SQL User password stopped working with no trace of what happened

  • 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

  • March 14, 2012 at 3:22 pm

    #1459520

    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!

  • March 14, 2012 at 6:40 pm

    #1459568

    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?

  • March 15, 2012 at 3:05 am

    #1459688

    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

  • March 15, 2012 at 7:18 am

    #1459818

    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:

    Date 3/13/2012 6:30:00 PM

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

    Source Logon

    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.

  • March 15, 2012 at 7:23 am

    #1459821

    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

  • March 15, 2012 at 7:27 am

    #1459827

    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.

  • March 15, 2012 at 7:47 am

    #1459852

    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

  • March 15, 2012 at 7:49 am

    #1459856

    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

  • March 15, 2012 at 8:20 am

    #1459880

    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.

  • March 15, 2012 at 8:31 am

    #1459890

    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

  • March 15, 2012 at 9:28 am

    #1459952

    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

  • March 15, 2012 at 9:55 am

    #1459971

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

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

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