Tracking password changes

From SQLServerPedia

Jump to: navigation, search

Contents

Introduction

Security is an important aspect for any organization small or big. Microsoft promotes several security best practises for SQL Server and this article talks about tracking password changes for sql logins in SQL Server.


SQL Server supports 2 authentication modes, namely Windows Authentication and Mixed Mode Authentication. When Windows Authentication is used, SQL Server is relieved of authentication duties and windows takes care of checking the credentials. The recommended best practise is to use Windows Authentication whenever possible, as passwords are not passed across the network. When Mixed Mode Authentication is used both Windows and SQL Server logins are allowed. With sql logins, SQL Server has to authenticate instead of the Windows. For these sql logins, the windows password policies can be applied using CHECK_POLICY, CHECK_EXPIRATION and MUST_CHANGE options when the logins are created with CREATE LOGIN. It is a good practise to keep track of the password changes to these sql logins in an enterprise.

SQL Server 2005

With SQL Server 2005, Password changes to SQL logins can tracked by looking at the Audit Login Change Password Event Class. Note that this event is NOT part of the default trace and one has to roll a custom trace. Here is the code snippet. 

USE master  
GO  
IF OBJECT_ID('dbo.Trace_Password_Change', 'P') IS NOT NULL  
    DROP PROCEDURE dbo.Trace_Password_Change  
GO  
CREATE PROCEDURE dbo.Trace_Password_Change  
AS  

   DECLARE @rc INT  
           , @TraceID INT  
           , @maxfilesize BIGINT  
           , @filecount INT  
           , @filedate CHAR(8)  
           , @filename NVARCHAR(128)  

    SELECT @maxfilesize = 50  
           , @filecount = 10  
           , @filedate = CONVERT ( CHAR(8), GETDATE(), 112 )  + '_' +REPLACE(CONVERT (CHAR(18), GETDATE(), 108), ':', '')  
           , @filename = 'C:\Trace_Password_Change-' + @filedate  
           --Change the location  
   EXEC @rc = sp_trace_create  
           @TraceID output 
           , @options = 2 
           , @tracefile = @filename 
           , @maxfilesize = @maxfilesize 
           , @stoptime = NULL  
           , @filecount = @filecount  
   IF (@rc != 0) GOTO error  
        -- Set the events  
    DECLARE @on bit  
    SET @on = 1  
   EXEC sp_trace_setevent @TraceID, 107, 7, @on  
   EXEC sp_trace_setevent @TraceID, 107, 23, @on  
   EXEC sp_trace_setevent @TraceID, 107, 8, @on  
   EXEC sp_trace_setevent @TraceID, 107, 40, @on  
   EXEC sp_trace_setevent @TraceID, 107, 64, @on  
   EXEC sp_trace_setevent @TraceID, 107, 1, @on  
   EXEC sp_trace_setevent @TraceID, 107, 9, @on  
   EXEC sp_trace_setevent @TraceID, 107, 41, @on  
   EXEC sp_trace_setevent @TraceID, 107, 49, @on  
   EXEC sp_trace_setevent @TraceID, 107, 10, @on  
   EXEC sp_trace_setevent @TraceID, 107, 26, @on  
   EXEC sp_trace_setevent @TraceID, 107, 34, @on  
   EXEC sp_trace_setevent @TraceID, 107, 42, @on  
   EXEC sp_trace_setevent @TraceID, 107, 50, @on  
   EXEC sp_trace_setevent @TraceID, 107, 3, @on  
   EXEC sp_trace_setevent @TraceID, 107, 11, @on  
   EXEC sp_trace_setevent @TraceID, 107, 35, @on  
   EXEC sp_trace_setevent @TraceID, 107, 43, @on  
   EXEC sp_trace_setevent @TraceID, 107, 51, @on  
   EXEC sp_trace_setevent @TraceID, 107, 4, @on  
   EXEC sp_trace_setevent @TraceID, 107, 12, @on  
   EXEC sp_trace_setevent @TraceID, 107, 28, @on  
   EXEC sp_trace_setevent @TraceID, 107, 60, @on  
   EXEC sp_trace_setevent @TraceID, 107, 21, @on  
   EXEC sp_trace_setevent @TraceID, 107, 29, @on  
   EXEC sp_trace_setevent @TraceID, 107, 37, @on  
   EXEC sp_trace_setevent @TraceID, 107, 6, @on  
   EXEC sp_trace_setevent @TraceID, 107, 14, @on  
    -- Set the Filters  
   DECLARE @intfilter INT  
           , @bigintfilter bigint  
    -- Set the trace status to start  
   EXEC sp_trace_setstatus @TraceID, 1  
    -- display trace id for future references  
   SELECT TraceID=@TraceID  
    GOTO finish  
    error:  
   SELECT ErrorCode=@rc  
    finish:  
GO  
EXECUTE sp_procoption @procname = 'dbo.Trace_Password_Change', @optionname = 'startup', @optionvalue = 'true';  
GO

Note that the above procedure is marked as a startup procedure and that will cover incase the server is re-started. Here is an example of tracing some changes using the above trace.

SQL Server 2008

SQL Server 2008 introduced a new feature to help centralize the auditing of SQL Server in one place with the SQL Server Audit feature. The native auditing capabilities are embedded into the Database Engine and auditing became fairly easy and straightforward and centralized in one place. Here is the code snippet using the Auditing features in SQL Server 2008 to track the password changes. 

USE [master] 
GO 
/****** Object:  Audit [Password_Audit]    Script Date: 09/26/2009 16:09:09 ******/ 
CREATE SERVER AUDIT [Password_Audit] 
TO FILE  
(  FILEPATH = N'C:\' 
   ,MAXSIZE = 10 MB 
   ,MAX_ROLLOVER_FILES = 30 
   ,RESERVE_DISK_SPACE = OFF 
) 
WITH 
(  QUEUE_DELAY = 1000 
   ,ON_FAILURE = CONTINUE 
) 
GO 
CREATE SERVER AUDIT SPECIFICATION Password_Change_Specification 
FOR SERVER AUDIT [Password_Audit] 
    ADD (LOGIN_CHANGE_PASSWORD_GROUP); 
GO 

ALTER SERVER AUDIT [Password_Audit] 
WITH (STATE = ON) 
GO 
ALTER SERVER AUDIT SPECIFICATION Password_Change_Specification 
WITH (STATE = ON) 
GO

Here is a screenshot of Password auditing using the above SQL Server audit code.

Types of password changes

The EventSubClass tracks six different password changes and helps understand the kind of password change performed. This is important for auditing purposes. 

EventSubClass EventSubClassDescription 
     1        Password self changed 
     2        Password changed 
     3        Password self reset 
     4        Password reset 
     5        Password unlocked 
     6        Password must change

Related Reading


Author Credits

Sankar Reddy

This wiki article is created by Sankar Reddy. Sankar is a Database Engineer and an involuntary DBA working for Funmobility, a mobile media and entertainment company where he works with high traffic OLTP systems and over 1 TB data warehouse. Sankar strongly believes in 'Every day is a school day!' and he's thankful for the vibrant SQL Server community. His online presences include:

Retrieved from "http://sqlserverpedia.com/wiki/Tracking_password_changes"