Post reply

How to track a sqlserver login if password changed how to know who has changed its a 2005 standard edition

  • May 15, 2015 at 3:36 am

    #148895

    hi geeks

    How to track a sqlserver login if password changed how to know who has changed its a standard edition

    we just want to check how and by whom sqlserver authenticated login has been changed???

    i think in sqlserver 2005 standard edition in this audit level can not be enabled right ...

    is there any alternative way to track all the logins password change and its timings and also who has changed the password.

    Thanks

    Rohitkumar

    Thanks
    Naga.Rohitkumar

  • May 16, 2015 at 4:44 pm

    #1798470

    naga.rohitkumar (5/15/2015)

    hi geeks

    How to track a sqlserver login if password changed how to know who has changed its a standard edition

    we just want to check how and by whom sqlserver authenticated login has been changed???

    i think in sqlserver 2005 standard edition in this audit level can not be enabled right ...

    is there any alternative way to track all the logins password change and its timings and also who has changed the password.

    Thanks

    Rohitkumar

    Kind of depends, you can start by reading Aaron's article Tracking Login Password Changes in SQL Server[/url]

    😎

  • May 16, 2015 at 9:17 pm

    #1798475

    yes i have followed this but not working and its throwing errors

    our environment has 2008 and 2005 sqlservers and they are standard versions in that case how can we move froward

    thanks

    rohitkumar

    Thanks
    Naga.Rohitkumar

  • May 17, 2015 at 6:59 pm

    #1798513

    It's a funny thing... If most people took the time to lookup the real original definition of "geek", they'd never use the word ever again.

    It's also funny that "hacker" was the word that meant someone was good at computers way before "geek" became popular.

    Shifting gears back to the problem at hand, if you have to set up a monitoring system to find out who changed an SQL Server login password, there may be a couple of problems that are even more interesting and should probably be corrected. Note that I'm NOT saying that auditing for SQL Server Login password changes is a bad thing. I'm saying that the sudden requirement for auditing such password changes may be due to a larger problem that needs a resolution.

    1. If the password change broke something, you need to find out why because SQL Server logins shouldn't be used for applications nor anything having to do with humans or the code they've written outside of the server. They should be reserved (if at all) for the likes of creating logins for linked servers or for jobs to make it easier to tell through auditing which job made a change to rows in a table.

    2. If the password change broke something and you don't actually know who changed the password, then your group is out of control. No one should ever change an SQL Server Login password unexpectedly.

    3. If you want to monitor such password changes to ensure it wasn't a hacker that did it, then you have MUCH larger problems than tracking password changes. And, don't forget that a hacker isn't going to break in as themselves. They're going to try to break in as someone with sysadmin privs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 17, 2015 at 7:06 pm

    #1798515

    naga.rohitkumar (5/16/2015)

    yes i have followed this but not working and its throwing errors

    our environment has 2008 and 2005 sqlservers and they are standard versions in that case how can we move froward

    thanks

    rohitkumar

    It would help if you posted what you tried and the errors (full error messages). What you posted above is like going to the mechanic and saying your car is broke.

  • May 17, 2015 at 7:56 pm

    #1798521

    naga.rohitkumar (5/16/2015)

    yes i have followed this but not working and its throwing errors

    our environment has 2008 and 2005 sqlservers and they are standard versions in that case how can we move froward

    thanks

    rohitkumar

    So, which of the methods from the article did you try and what are the errors?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 18, 2015 at 12:04 am

    #1798540

    I tired to check by using this command and we can see the time changed but not who changed

    [ the servers is " 10.50.4000.0SP2Standard Edition (64-bit)] in version.

    USE [database]

    GO

    SELECT sid, [name], createdate, updatedate

    FROM master.dbo.syslogins

    WHERE [name] = 'login'

    GO

    error message which we got repeatedly is ...

    Error: 18456,Severity:14,state:8

    the

    Login failed for user "username"reason:password did not match that for the login provided

    Thanks
    Naga.Rohitkumar

  • May 18, 2015 at 9:04 am

    #1798666

    naga.rohitkumar (5/18/2015)

    I tired to check by using this command and we can see the time changed but not who changed

    [ the servers is " 10.50.4000.0SP2Standard Edition (64-bit)] in version.

    USE [database]

    GO

    SELECT sid, [name], createdate, updatedate

    FROM master.dbo.syslogins

    WHERE [name] = 'login'

    GO

    error message which we got repeatedly is ...

    Error: 18456,Severity:14,state:8

    the

    Login failed for user "username"reason:password did not match that for the login provided

    Is this all you did?

  • May 18, 2015 at 9:18 am

    #1798671

    I googled, and it appears that the Default Trace can be used to track several types of changes to login properties... but not a password change, which would seem to be the most important. So, you would have to create a custom trace.

    Anyone who knows the password for a SQL Server account can change the password; it doesn't require special permissions. What you can do is monitor logins under this account. I think it's safe to assume that whomever is logging in after the password change probably changed it, or at least they know who did.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 9 posts - 1 through 8 (of 8 total)

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