SA Password changing automatically

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

Answer 1

check if these check boxes selected by default :

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

Answer 2

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]

Answer 3

Hi Helper,

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

Regards

Senthil.K

---

Senthil

Answer 4

You can query it by LOGINPROPERTY (Transact-SQL)

select LOGINPROPERTY('sa', 'PasswordLastSetTime')

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Thanks Helper.

---

Senthil

Answer 6

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%'

Answer 7

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

Answer 8

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]

Answer 9

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