locked
SQL Login Password Change in Audit (SQL 2016) RRS feed

  • Question

  • I have setup server audit to trace all the SQL login changes in my DB servers and included USER_CHANGE_PASSWORD_GROUP for the password change of SQL Login.

    I tried to use login properties to change the password but it did not write to my audit files. Any idea i can fix it?

    Thursday, August 17, 2017 9:06 AM

Answers

  • The reason nothing gets logged is that you picked the wrong group. USER_CHANGE_PASSWORD_GROUP is for users in a contained database who authenticate directly against the database. You audit regular logins, you should use LOGIN_CHANGE_PASSWORD_GROUP.

    Overall, when working in SQL Server, it's important to be aware of the terminology. Although we often say user also about a login, in the SQL Server lingo, "user" always refer to a user on database level, whereas commands, groups etc that refer to server principals always use "login".

    Friday, August 18, 2017 9:11 PM

All replies

  • Hi xia0lin,

    Have you configured Login Auditing? If not, please see https://docs.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 17, 2017 10:33 AM
  • Thanks for the reply. However my question is on the PASSWORD CHANGE but not failed or success login.

    I only can get the record if i execute query below.

    alter LOGIN [testlogin] WITH PASSWORD='password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    I would like to know how i get this write into audit file if i change password using login properties as screenshot above.

    Friday, August 18, 2017 1:19 AM
  • Thanks for the reply. However my question is on the PASSWORD CHANGE but not failed or success login.

    I only can get the record if i execute query below.

    alter LOGIN [testlogin] WITH PASSWORD='password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    I would like to know how i get this write into audit file if i change password using login properties as screenshot above.

    Hi xia0lin,

    Sorry for my misunderstanding.

    According to the message above, I know that you want to get the statement of execution record from the audit logs.

    For this purpose, you may try to execute the following code and see if it works.

    USE MASTER
    GO
    -- Create the server audit
    CREATE SERVER AUDIT login_perm_audit
    TO FILE ( FILEPATH ='C:\DATA2\' );
    GO
    -- Enable the server audit
    ALTER SERVER AUDIT login_perm_audit 
    WITH (STATE = ON) ;
    GO
    CREATE SERVER AUDIT SPECIFICATION login_audit_spec
    FOR SERVER AUDIT login_perm_audit
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
    WITH ( STATE =  ON )
    
    
    --alter LOGIN [testlogin] WITH PASSWORD='password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    By the way, here is an article which is talking about SQL Server Audit Action Groups and Actions. Please refer:

    SQL Server Audit Action Groups and Actions

    Hope these are helpful to you.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 18, 2017 7:19 AM
  • The reason nothing gets logged is that you picked the wrong group. USER_CHANGE_PASSWORD_GROUP is for users in a contained database who authenticate directly against the database. You audit regular logins, you should use LOGIN_CHANGE_PASSWORD_GROUP.

    Overall, when working in SQL Server, it's important to be aware of the terminology. Although we often say user also about a login, in the SQL Server lingo, "user" always refer to a user on database level, whereas commands, groups etc that refer to server principals always use "login".

    Friday, August 18, 2017 9:11 PM