Here on this article, I will demonstrate how to track SQL login Password changes on SQL Server 2005, on SQL Server 2008 we can rely on newly introduced feature called SQL Server Auditing to achieve this. Since SQL Server 2005 does not have Auditing feature, so let’s see how to track these changes in SQL Server 2005.
By creating custom trace on Event Class: Audit Login Change Password Event Class we can track password changes to the SQL Logins. By default this event class is not a part of default trace.
This is how it’s done:
1. Create a stored procedure to create custom trace on Event Class: Audit Login Change Password Event Class.
2. Mark the above created stored procedure as Startup procedure
1. Creating Stored Procedure to Create Custom Trace.
Here I am using two system stored procedure
1. sp_trace_Create :- This is used to create new trace definition.
2. sp_trace_setevent:- Once the trace is defined, we need to add events to the trace that we created in above step. This stored procedure is used to Add or Remove Events to a trace. Remember this may be executed only on existing trace having status ‘stopped’.
Script
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_password_change_trace]
AS
BEGIN
DECLARE @TraceID INT,
@FileName NVARCHAR(245) ,
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT,
@maxfilesize BIGINT
BEGIN TRAN
BEGIN TRY
-- Specify the location and file name to which the trace will be written
SELECT @filename = 'D:\SQLTrace\Password_Change_Trace_'
+ CONVERT ( CHAR(8), GETDATE(), 112 )
+ '_' +REPLACE(CONVERT (CHAR(18), GETDATE(), 108), ':', '')
,@maxfilesize = 50
-- Add Event Class to the trace.
*/
/*
EXEC sp_trace_setevent @TraceID -- TraceID
@EventID = 107, -- 107 : Audit Login Change Password Event, Occurs
when a SQL Server login password is changed.
Passwords are not recorded.
@columnID, -- Columns to be addedd
@On = 1 -- 1 = Add, 0 = Remove
EXEC sp_trace_setevent @TraceID, 107, 6, 1
-- Adds Column NTDomainName: Windows domain to which the user belongs.
EXEC sp_trace_setevent @TraceID, 107, 7, 1
-- Adds Column HostName : Name of the computer on which the client is running.
EXEC sp_trace_setevent @TraceID, 107, 8, 1
the connection to an instance of SQL Server. This column is populated with
the values passed by the application rather than the displayed name of the
program.*/
EXEC sp_trace_setevent @TraceID, 107, 10,1
-- Add Column LoginName : Name of the login of the user.
EXEC sp_trace_setevent @TraceID, 107, 11,1
-- Add Column StartTime : Time at which the event started, if available.
EXEC sp_trace_setevent @TraceID, 107, 14,1
/* Add Column EventSubClass: 1=Password self changed, 2=Password changed ,3=Password self reset 4=Password reset,5=Password unlocked,6=Password must change*/
EXEC sp_trace_setevent @TraceID, 107, 21,1-- Add Column ServerName : Name of the instance of SQL Server being traced.
EXEC sp_trace_setevent @TraceID, 107, 26,1
-- Add Column ObjectTyoe : Value representing the type of the object involved in the event.
EXEC sp_trace_setevent @TraceID, 107, 28,1
-- Add Column ObjectName : Name of the object being referenced.
EXEC sp_trace_setevent @TraceID, 107, 34,1
-- Add Column DatabaseName : Name of the database in which the user statement is running.
EXEC sp_trace_setevent @TraceID, 107, 35,1
-- Adds Column DBUserName : SQL Server database user name of the client.
EXEC sp_trace_setevent @TraceID, 107, 40,1
/* Adds Column LoginSid : Security identification number (SID) of the logged-in user. You can find this information in the sys.server_principals catalog view. Each SID is unique for each login in the server.*/
EXEC sp_trace_setevent @TraceID, 107, 41,1
/* Add Column TargetLoginName: For actions that target a login (for example, adding a new login), the name of the targeted login. */
EXEC sp_trace_setevent @TraceID, 107, 42,1
/* Add Column IsSystem : Indicates whether the event occurred on a system process or a user process. 1 = system, 0 = user. */
EXEC sp_trace_setevent @TraceID, 107, 60,1
/* Adds Column SessionLoginName :Login name of the user who originated the session.
For example, if you connect to SQL Server using Login1 and execute
a statement as Login2, SessionLoginName shows Login1 and LoginName
shows Login2. This column displays both SQL Server and Windows logins.*/EXEC sp_trace_setevent @TraceID, 107, 64,1
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
Execute the following query to check the trace we created
SELECT * FROM ::fn_trace_getinfo(default)
OR
SELECT * FROM sys.fn_trace_getinfo
The column property with ‘5’ shows the current status of the trace
The value can be 0 = Stopped or 1 = Started2. Mark the stored procedure to run while SQL Server Starts.
By using sp_procoption you can set a stored procedure for auto execution, this makes the stored procedure to execute automatically whenever the SQL SERVER instance starts.
Before that check the “Scan for Startup Procs” is enabled by using sp_configure.
-- Check run value "scan for startup procs"
sp_configure 'scan for startup procs'
If it is disabled enable it
sp_configure 'scan for startup procs', 1
RECONFIGURE WITH OVERRIDE
-- make the sp to run at startup
EXEC sp_procoption @procname = 'dbo.usp_password_change_trace', @optionname = 'startup', @optionvalue = 'true'
After making any password change, you can test this by two ways – By opening the trace file created or by using the function fn_trace_gettable
To Modify the state of the trace
By using sp_trace_setstatus, you can modify the current state of the trace.
-- To run the trace.
sp_trace_setstatus @traceID, 1
-- To stop the trace.
sp_trace_setstatus @traceID, 0
-- To delete the trace, you need to first stop if the trace running.
-- if trace is running, stop.
sp_trace_setstatus @traceID, 0
-- Delete its definition
sp_trace_setstatus @traceID, 2
No comments:
Post a Comment