locked
SA Password changing automatically RRS feed

  • Question

  • Dear Experts,

    'sa' password is changing everyday automatically. What would be the reason behind this ?

    How can we stop this ? Kindly help.

    Regards

    Senthil.K


    Senthil

    Wednesday, July 20, 2016 6:21 AM

Answers

  • If you are running a modern version of SQL Server Enterprise Edition, you can use an audit to capture password changes:

    USE master;
    
    CREATE SERVER AUDIT [Password_Change_Audit]
    TO FILE (	FILEPATH = N'c:\Temp\Audit' ) --folder for audit files 
    WITH
    (	QUEUE_DELAY = 1000
    	,ON_FAILURE = CONTINUE
    );
    
    CREATE SERVER AUDIT SPECIFICATION [Password_Change_Audit_Specification]
    FOR SERVER AUDIT [Password_Change_Audit]
    ADD (USER_CHANGE_PASSWORD_GROUP);
    
    ALTER SERVER AUDIT [Password_Change_Audit]
    WITH (STATE = ON);
    
    ALTER SERVER AUDIT SPECIFICATION [Password_Change_Audit_Specification]
    WITH (STATE = ON);
    GO
    
    

    Audit data can be displayed with the script below.  The audit can be managed from SSMS under the Security-->Audits node in Object explorer.

    --select password changes
    SELECT *
    FROM fn_get_audit_file(N'C:\Temp\Audit\*', default, default);

    In lesser editions, a server side SQL trace can do the job.  Below is an example scripted from Profiler and modified for the task at hand:

    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 5 
    
    exec @rc = sp_trace_create @TraceID output, 2, N'C:\Audits\sa_password_change', @maxfilesize, NULL, 5
    if (@rc != 0) goto error
    
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 107, 1, @on
    exec sp_trace_setevent @TraceID, 107, 9, @on
    exec sp_trace_setevent @TraceID, 107, 3, @on
    exec sp_trace_setevent @TraceID, 107, 11, @on
    exec sp_trace_setevent @TraceID, 107, 4, @on
    exec sp_trace_setevent @TraceID, 107, 6, @on
    exec sp_trace_setevent @TraceID, 107, 7, @on
    exec sp_trace_setevent @TraceID, 107, 8, @on
    exec sp_trace_setevent @TraceID, 107, 10, @on
    exec sp_trace_setevent @TraceID, 107, 12, @on
    exec sp_trace_setevent @TraceID, 107, 14, @on
    exec sp_trace_setevent @TraceID, 107, 21, @on
    exec sp_trace_setevent @TraceID, 107, 23, @on
    exec sp_trace_setevent @TraceID, 107, 26, @on
    exec sp_trace_setevent @TraceID, 107, 28, @on
    exec sp_trace_setevent @TraceID, 107, 29, @on
    exec sp_trace_setevent @TraceID, 107, 34, @on
    exec sp_trace_setevent @TraceID, 107, 35, @on
    exec sp_trace_setevent @TraceID, 107, 37, @on
    exec sp_trace_setevent @TraceID, 107, 40, @on
    exec sp_trace_setevent @TraceID, 107, 41, @on
    exec sp_trace_setevent @TraceID, 107, 42, @on
    exec sp_trace_setevent @TraceID, 107, 43, @on
    exec sp_trace_setevent @TraceID, 107, 49, @on
    exec sp_trace_setevent @TraceID, 107, 50, @on
    exec sp_trace_setevent @TraceID, 107, 51, @on
    exec sp_trace_setevent @TraceID, 107, 60, @on
    exec sp_trace_setevent @TraceID, 107, 64, @on
    
    --filter for sa login
    exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sa'
    --start trace
    exec sp_trace_setstatus @TraceID, 1
    
    -- display trace id for future references
    select TraceID=@TraceID
    goto finish
    
    error: 
    select ErrorCode=@rc
    
    finish: 
    GO
    

    The trace file(s) can be viewed with Profiler or via T-SQL:

    SELECT *
    FROM fn_get_audit_file(N'C:\Audits\sa_password_change.trc', default, default);
    GO
    
    

    The server-side trace can be managed with sp_trace_setstatus:

    --stop and delete trace: specify TraceID returned by create script
    EXEC sp_trace_setstatus @traceid=2, @status=0; 
    EXEC sp_trace_setstatus @traceid=2, @status=2;
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 20, 2016 12:45 PM
  • You can query it by LOGINPROPERTY (Transact-SQL)

    select LOGINPROPERTY('sa', 'PasswordLastSetTime')


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 20, 2016 8:29 AM

All replies

  • check if these check boxes selected by default :

    Enforce password policy
    Enforce password expiration
    User must change password at next login


    Wednesday, July 20, 2016 6:29 AM
  • Hello Senthil,

    SQL Server don't change any Password on it's own, that must be an other process or a user. Try to audit Logins & pwd changes.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 20, 2016 7:44 AM
  • Hi Helper,

    Is there any query to find out what time changed the Password ? 

    Regards

    Senthil.K


    Senthil

    Wednesday, July 20, 2016 8:25 AM
  • You can query it by LOGINPROPERTY (Transact-SQL)

    select LOGINPROPERTY('sa', 'PasswordLastSetTime')


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 20, 2016 8:29 AM
  • Thanks Helper.

    Senthil

    Wednesday, July 20, 2016 8:33 AM
  • If the password is being changed automatically then this could be on a SQL Server Agent Job

    Please click "Mark As Answer" if my post helped. Tony C.


    use

    msdb


    go

    select

    * from sysjobsteps where command like '%ALTER%LOGIN%'

    Wednesday, July 20, 2016 8:46 AM
  • Dear Antony,

    Thanks for the hint. I have noticed , there are many unwanted jobs inside the SQL. I have deleted some.

    But again the same jobs are inside the SQL. what would be the reason for this ?

    How can i Solve this ?

    Regards

    Senthil.K


    Senthil

    Wednesday, July 20, 2016 9:12 AM
  • Wich date time value returned the query on Login property? Has the password to a different or to the same one?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 20, 2016 12:34 PM
  • If you are running a modern version of SQL Server Enterprise Edition, you can use an audit to capture password changes:

    USE master;
    
    CREATE SERVER AUDIT [Password_Change_Audit]
    TO FILE (	FILEPATH = N'c:\Temp\Audit' ) --folder for audit files 
    WITH
    (	QUEUE_DELAY = 1000
    	,ON_FAILURE = CONTINUE
    );
    
    CREATE SERVER AUDIT SPECIFICATION [Password_Change_Audit_Specification]
    FOR SERVER AUDIT [Password_Change_Audit]
    ADD (USER_CHANGE_PASSWORD_GROUP);
    
    ALTER SERVER AUDIT [Password_Change_Audit]
    WITH (STATE = ON);
    
    ALTER SERVER AUDIT SPECIFICATION [Password_Change_Audit_Specification]
    WITH (STATE = ON);
    GO
    
    

    Audit data can be displayed with the script below.  The audit can be managed from SSMS under the Security-->Audits node in Object explorer.

    --select password changes
    SELECT *
    FROM fn_get_audit_file(N'C:\Temp\Audit\*', default, default);

    In lesser editions, a server side SQL trace can do the job.  Below is an example scripted from Profiler and modified for the task at hand:

    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 5 
    
    exec @rc = sp_trace_create @TraceID output, 2, N'C:\Audits\sa_password_change', @maxfilesize, NULL, 5
    if (@rc != 0) goto error
    
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 107, 1, @on
    exec sp_trace_setevent @TraceID, 107, 9, @on
    exec sp_trace_setevent @TraceID, 107, 3, @on
    exec sp_trace_setevent @TraceID, 107, 11, @on
    exec sp_trace_setevent @TraceID, 107, 4, @on
    exec sp_trace_setevent @TraceID, 107, 6, @on
    exec sp_trace_setevent @TraceID, 107, 7, @on
    exec sp_trace_setevent @TraceID, 107, 8, @on
    exec sp_trace_setevent @TraceID, 107, 10, @on
    exec sp_trace_setevent @TraceID, 107, 12, @on
    exec sp_trace_setevent @TraceID, 107, 14, @on
    exec sp_trace_setevent @TraceID, 107, 21, @on
    exec sp_trace_setevent @TraceID, 107, 23, @on
    exec sp_trace_setevent @TraceID, 107, 26, @on
    exec sp_trace_setevent @TraceID, 107, 28, @on
    exec sp_trace_setevent @TraceID, 107, 29, @on
    exec sp_trace_setevent @TraceID, 107, 34, @on
    exec sp_trace_setevent @TraceID, 107, 35, @on
    exec sp_trace_setevent @TraceID, 107, 37, @on
    exec sp_trace_setevent @TraceID, 107, 40, @on
    exec sp_trace_setevent @TraceID, 107, 41, @on
    exec sp_trace_setevent @TraceID, 107, 42, @on
    exec sp_trace_setevent @TraceID, 107, 43, @on
    exec sp_trace_setevent @TraceID, 107, 49, @on
    exec sp_trace_setevent @TraceID, 107, 50, @on
    exec sp_trace_setevent @TraceID, 107, 51, @on
    exec sp_trace_setevent @TraceID, 107, 60, @on
    exec sp_trace_setevent @TraceID, 107, 64, @on
    
    --filter for sa login
    exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sa'
    --start trace
    exec sp_trace_setstatus @TraceID, 1
    
    -- display trace id for future references
    select TraceID=@TraceID
    goto finish
    
    error: 
    select ErrorCode=@rc
    
    finish: 
    GO
    

    The trace file(s) can be viewed with Profiler or via T-SQL:

    SELECT *
    FROM fn_get_audit_file(N'C:\Audits\sa_password_change.trc', default, default);
    GO
    
    

    The server-side trace can be managed with sp_trace_setstatus:

    --stop and delete trace: specify TraceID returned by create script
    EXEC sp_trace_setstatus @traceid=2, @status=0; 
    EXEC sp_trace_setstatus @traceid=2, @status=2;
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 20, 2016 12:45 PM