Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development Webcast    ===>    Register Now
 

Automated WMI Alerts for SQL Server Login Property Changes


By:   |   Read Comments   |   Related Tips: More > Security

Problem

As you probably know, you can monitor login or user modifications using SQL Server Audit. The disadvantage of this method is that it requires reviewing the audit logs. We would like to get real-time alerts every time a login's properties change. In our previous WMI tip, we provided steps to configure alerts for login and user creation. Can we use WMI Alerts to monitor login property changes?

Solution

In one of our previous tips, we explained how to setup WMI alerts for a database change monitoring. The setup consists of SQL Server Agent configuration steps, Database Mail configuration, and creation of the alert and SQL Server Job. You will need to make sure Database Mail is configured and SQL Server Agent is setup to allow replacing tokens as per this tip.

In this tip we will show you how to set up WMI alerts and jobs responding to these alerts to monitor the following changes:

  • SQL Server login was enabled/disabled
  • SQL Server login was granted/denied SQL Server Access
Login
  • SQL Server login's password policy and/or expiration was changed
  • SQL Server login's credential was changed
  • SQL Server password was reset/changed
Login

Here are the jobs and alerts that will be created:

Jobs and WMI Alerts

We will provide the jobs' steps and alerts screenshots in this tip and a complete script at the end of the tip for all of the jobs and alerts. In this tip we will extend the WMI alerts functionality and include an indication of a successful vs. failed change. See the details below under the first alert’s response job.

Note, that the jobs are not scheduled and cannot be run manually.

Create SQL Server Agent Job that will Send an Alert when a Login is Enabled/Disabled

The following job ("WMI Response - Audit Enable/Disable Login Event") will respond to the WMI event every time a login is enabled or disabled.

The image below displays the job's step. You will need to update @profile_name and @recipients parameters with your values (@profile_name will be the "Mail profile" that you created during Database Mail configuration):

"WMI Response - Audit Enable/Disable Login Event" job step

Note, that we have a new parameter @p_importance. We will use this parameter to set an email priority. The priority will be set to "High" when an action (login property change) failed:

@p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END			

Here is the script for the job step above (you will need to update @profile and @recipients parameters with your values):

DECLARE @p_subject NVARCHAR(255), @p_action INT, @p_importance VARCHAR (6)

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass))), 
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT @p_subject = N'WMI Alert 1: Login [$(ESCAPE_SQUOTE(WMI(ObjectName)))] ' + 
		CASE 	WHEN  @p_action = 6 THEN 'enabled on' 
		 	WHEN  @p_action = 5 THEN 'disabled from'
		 	ELSE 'changed on' END + 
		' [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'DBServerAlerts', -- update with your values
	@recipients = '[email protected]', -- update with your values
	@importance = @p_importance ,
	@subject = @p_subject,
	@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(ObjectName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to a Login's Change Events (Enable/Disable Login)

Now we will setup the WMI alert:

  • Set the alert type to "WMI event alert"
  • Make sure you use the correct WMI namespace
WMI Alert to Respond to the Login Disable/Enable Events

Note: the namespace will be different for the default instance and for the named instance:

-- DEFAULT instance's namespace ("DEMOSQL1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER

-- NAMED instance's namespace ("DEMOSQL1\SQLINSTANCE1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\SQLINSTANCE1

Here is the WMI query for this alert:

select * from AUDIT_SERVER_PRINCIPAL_MANAGEMENT_EVENT where EventSubClass = 5 or EventSubClass = 6			

Read more about the Audit Server Principal Management Event Class here.

Set the response in alert's properties to execute the job we created earlier:

Alert

Create SQL Server Agent Job that will Respond to a Login's Password Changes

Here is the "WMI Response - Audit Login Change Password Event" job's step for the login's password reset/change events response:

"WMI Response - Audit Login Change Password Event" job step

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action NVARCHAR(255), @p_importance VARCHAR (6)

SELECT @p_action = CASE WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 1 THEN 'Password self changed'
WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 2 THEN 'Password changed'
WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 3 THEN 'Password self reset'
WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 4 THEN 'Password reset'
WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 5 THEN 'Password unlocked'
WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 6 THEN 'Password must change' ELSE 'N/A password action' END,
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT  @p_subject = N'WMI Alert: ' + @p_action + ' for Login [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' +
'on [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBServerAlerts', -- update with your values
@recipients = '[email protected]', -- update with your values
@importance = @p_importance ,
@subject = @p_subject,
@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime)));
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance)));
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
TextData: $(ESCAPE_SQUOTE(WMI(TextData)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to the Login Change Password Event

Now we will create the WMI alert as the following:

Login Password Change Alert

Here is the WMI query for this alert:

select * from AUDIT_LOGIN_CHANGE_PASSWORD_EVENT			

Read more about the Audit Login Change Password Event Class here.

Note, that the following Event SubClasses are available:

  • 1 - Password self changed
  • 2 - Password changed
  • 3 - Password self reset
  • 4 - Password reset
  • 5 - Password unlocked
  • 6 - Password must change

You can limit the number of alerts if you want and exclude some of the Event SubClasses. For example:

select * from AUDIT_LOGIN_CHANGE_PASSWORD_EVENT where EventSubClass = 2 or EventSubClass = 4 or EventSubClass = 5
			

Set the response in alert's properties to execute the job we have created earlier:

Alert

Create SQL Server Agent Job that will Respond to the Login's Properties Changes (Credentials and Password Settings)

This job ("WMI Response - Audit Login Change Property Event") will respond to the alerts triggered when login's credential is changed or when the password policy and/or expiration is changed:

"WMI Response - Audit Login Change Property Event" job step

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action INT, @p_importance VARCHAR (6)

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass))), 
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT @p_subject = N'WMI Alert: Login''s [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
		CASE 	WHEN  @p_action = 4 THEN 'Credential ' 
		 	WHEN  @p_action = 5 THEN 'Policy ' 
		 	WHEN  @p_action = 6 THEN 'Expiration ' 
		 	ELSE '' END + 
		'property changed on [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'DBServerAlerts', -- update with your values
	@recipients = '[email protected]', -- update with your values
	@importance = @p_importance ,
	@subject = @p_subject,
	@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to the Login's Credentials and Password Settings Change Events

Here is the WMI alert for these login's properties changes:

Login Property Change alert

Here is the WMI query for this alert:

select * from AUDIT_LOGIN_CHANGE_PROPERTY_EVENT where EventSubClass >= 4			

If you need to monitor login rename events in addition to other changes you can update the WMI query to this:

select * from AUDIT_LOGIN_CHANGE_PROPERTY_EVENT where EventSubClass >= 3			

Find more details about Audit Login Change Property Event Class here.

Set the response in alert's properties to execute the job we created earlier:

Alert

Create SQL Server Agent Job that will Send Alerts when a Login Granted/Denied/Revoked Connect SQL Server Permission

This job ("WMI Response - Audit Server Scope GDR Event") will respond to modifications of the login's permission to connect to the SQL Server:

"WMI Response - AuditServer Scope GDR Event" job step

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action INT, @p_importance VARCHAR (6) 

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass))), 
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT  @p_subject = N'WMI Alert: Login [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
		CASE 	WHEN  @p_action = 1 THEN 'granted ' 
		 	WHEN  @p_action = 2 THEN 'revoked ' 
		 	WHEN  @p_action = 3 THEN 'denied ' 
		 	ELSE '' END + 
		'CONNECT SQL permission on [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'DBServerAlerts', -- update with your values
	@recipients = '[email protected]', -- update with your values
	@importance = @p_importance ,
	@subject = @p_subject,
	@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
TextData: $(ESCAPE_SQUOTE(WMI(TextData)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to the Login's Permissions Changes Events (Connect SQL Only)

WMI alert for the login's permission changes (grant/deny/revoke "CONNECT SQL"):

Alert to audit Grant/Revoke/Deny CONNECT SQL server permission

Note, that "Permissions='1'" filter is used to monitor this specific Server Scope Permission ("CONNECT SQL").

Here is the WMI query for this alert:

select * from AUDIT_SERVER_SCOPE_GDR_EVENT where Permissions = '1'			

Read more about the Audit Server Scope GDR Event Class here.

Set the response in alert's properties to execute the job we created earlier:

Alert

Please note, that if access granted/changed using deprecated stored procedures sp_grantlogin, sp_revokelogin or sp_denylogin then you will need to use a different Event Class - "Audit Login GDR":

select * from AUDIT_LOGIN_GDR_EVENT			

Testing the Alerts

Now we should be able to receive email notifications every time when a login's property or password has changed or when "CONNECT SQL" Server Level permission has been granted/denied/revoked to/from a login.

Let's run the following commands for our test “_demo_user” login:

USE [master]
GO
ALTER LOGIN [_demo_user] DISABLE
GO

You should get an email like the following:

Disabled login email

Note, that the EventSubClass is equal to "5" when login is disabled. If a login is enabled then EventSubClass will be "6" and email's subject will change.

Now we will reset/change this login's password:

USE [master]
GO
ALTER LOGIN [_demo_user] WITH PASSWORD=N'[email protected]#qw78eQWE'
GO
ALTER LOGIN [_demo_user] WITH PASSWORD=N'123#qfgfgw78eQWE' OLD_PASSWORD=N'[email protected]#qw78eQWE'
GO

Here is an email notification for the first command (password reset):

Password reset email

Here is the email for the second command (password change):

Password change email

Now let's try to specify the incorrect old password:

USE [master]
GO
ALTER LOGIN [_demo_user] WITH PASSWORD=N'123#qfgfgw78eQWE' OLD_PASSWORD=N'Wrong_Old_Password1'
GO

Note the password change will fail with the following error:

Password Change Error
Msg 15151, Level 16, State 1, Line 3
Cannot alter the login '_demo_user', because it does not exist or you do not have permission.

We will get email alert with "High" importance because the password change has failed:

Password change email (failed event)

We will now change this login's password settings:

USE [master]
GO
ALTER LOGIN [_demo_user] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

If we change two properties in the same statement as in the example above (EXPIRATION=OFF, CHECK_POLICY=OFF) then two emails will be sent (one for each property change):

policy property
expiration property changed

In our next test we will map the login to an existing credential:

login properties
USE [master]
GO
ALTER LOGIN [_demo_user] ADD CREDENTIAL [F7C84B65-5090-41C9-ADA9-B510223CA868]
GO

Here is an email notification for this change:

Login property change email (credential settings)

Finally, we will deny permission to connect to the database engine to the login:

USE [master]
GO
DENY CONNECT SQL TO [_demo_user]
GO

The email looks like this:

Deny CONNECT SQL email

Complete Script

The script for all of the jobs and alerts could be downloaded from here.

Please note, that you will need to update the following parts of the script:

  • @wmi_namespace for the alerts (see the examples above)
  • @profile_name and @recipients parameters with your values (@profile_name will be the "Mail profile" that you created during Database Mail configuration)
  • replace the job owner if "sa" login is renamed on your SQL Server:
 @owner_login_name=N'sa'			
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools