Someone / thing changed password for user?

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  January 16, 2012 at 8:30 am

  #249092

  I have a sql authenticated user on a prod box. The user / password details are used by an app that connects in regularly.

  9 pm ish Sat eve, something changed & the login from the app started failing with 18456 / error state 8 errors (password mismatch). 3 tries later = lockout. I was on call so talking a non dba through what to do on the phone, but ultimately what resolved the problem was:

  @ the sql login properties, to re-enter the password

  this failed with error 'password validation failed ... 15118 (not complex enough).

  cancel this.

  re-open & uncheck 'enforce password policy'

  ok

  re-open & re-enter correct password.

  so I guess there are 2 issues:

  1. What changed the password (if indeed it changed?) Or what else could have caused this?

  There's nothing in the event viewer or sql error logs that seems relevant.

  My default trace logs have already rolled over & past sat (i'll change for the future)

  Is there any other way to look back?

  2. What is it with 'enforce password policy'?

  I did a test. Create SQL auth user with sufficiently complex password. Fine.

  Attempt to add 'x' on the end. It fails with error 15118?

  Thanks for any help.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 16, 2012 at 8:40 am

  #1434108

  snomad (1/16/2012)

  ---

  so I guess there are 2 issues:

  1. What changed the password (if indeed it changed?) Or what else could have caused this?

  There's nothing in the event viewer or sql error logs that seems relevant.

  My default trace logs have already rolled over & past sat (i'll change for the future)

  Is there any other way to look back?

  a password change is not a DDL event, so it will not exist in the default trace...you'll have to consider adding your own trace to capture login events if you think it's worthy.

  if you didn't have a trace in place(c2 auditing, maybe?) then you can't really find anything that wasn't already set to be captured.

  2. What is it with 'enforce password policy'?

  I did a test. Create SQL auth user with sufficiently complex password. Fine.

  Attempt to add 'x' on the end. It fails with error 15118?

  Thanks for any help.

  password policy gets inheritied from the domain; if your domain says a minimum password letch is 6 characters for example, or the rules on password complexity...they all come from the domain.

  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!

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  January 16, 2012 at 9:05 am

  #1434124

  Thanks for the reply.

  Will set up DDL tracers as you suggest. I didn't realise that about the default traces.

  I have got to the bottom of the sequence of events:

  1. I narrowed down the box the login attempts came from via the SQL error log.

  2. I found the box owner & strangled him until he found a rogue service that did 3 login attempts with my user & locked my user out.

  3. Unlocking would have been enough but the guy I talked through the fix didn't wait a sec & try as I told him to.

  Incidentally, it was my error re: password complexity failure.

  If you open up SQL login & just add x onto the end of the password it must just send 'x' through, not the whole password + x.

  A complex series of user errors, idiot admins & 1 flawed test by me.

  Thank you.