In the previous article related to the SQL Server Policy Based Management feature, we discussed and provided steps and tips to create conditions and policies that help maintaining SQL Server security in large enterprises with a number of SQL Server instances. Now, we are going to go further as once set policies are not much of a use if their violations are left unnoticed.

The general idea is to receive a notification in such cases and be informed of policy violation details. For instance, if policy rules are violated, the policy cannot do much, except when used in the On change: prevent evaluation mode. However, as that mode is limited to few conditions, we will focus on a general policy violation alerting scenario.

The SQL Server Alerts feature

Policy violations generate events in SQL Server application logs. Using SQL Server Agent and the Alerts feature, we are able to search the SQL Server logs and locate the events describing policy violations. In addition, using SQL Server Alerts and Operators we can automate the process of policy violation notification

Let us examine one SQL Server policy violation alerting example. We’ll create a policy that evaluates SQL Server login mode and verifies whether only the Windows Authentication mode is set. As a common security precaution, the Windows Authentication mode is highly recommended to be used, instead of the mixed (SQL Server and Windows Authentication) mode.

First, create a condition and a corresponding policy that will be monitored for violations. We’ll use SQL Server Management Studio, although T-SQL can be used too:

1. In Object Explorer, expand the Management node. Expand the Policy Management node and open the context menu of the Conditions sub-node. Select the New Condition option

2. Fill in the condition name in the Create New Condition dialog (e.g. ConditionServerLoginMode)

   

   In our example, we will evaluate the SQL Server login mode. For that purpose, select the Server Security item from the Facets dropdown menu. It will provide the list of field values in the Expression grid. Using the grid, set the “@LoginMode = Integrated” expression, as shown above. This means that the condition will test whether SQL Server login is set to the Windows Authentication mode.

3. The next step is to declare the policy that will use the condition. To do that, right-click the Policies node and select the New Policy option

4. Type in the policy name (e.g. PolicyServerAuthenticationMode), and using the Check condition dropdown menu select the previously created (ConditionServerLoginMode) condition

   

   Note that the LoginMode field value we used in the condition has no targets but the SQL Server instance itself, so the Against targets box is empty.

5. Set the Evaluation Mode option to On schedule. Using the New option create a new schedule, or using the Pick option select one of available schedules on SQL Server. In our case, we’ll select the pre-defined available schedule (CollectorSchedule_Every_5min). This means our policy will be checked every 5 minutes and evaluate whether SQL Server login mode complies the policy rule (SQL Server login is set to the Windows Authentication mode)
6. Click Ok to save the policy
7. Enable the policy using it’s context menu

Once we have created the condition and policy, they are available for further use and shown in Object Explorer

To create a SQL Server alert that will notify you if the policy violation occurs, use an Operator object (e.g. PolicyManagementOperator) that will receive the alert notification via email. Note that this requires the properly configured Database Mail feature.

To create the alert:

1. Using Object Explorer, expand SQL Server Agent and select the New Alert option from the Alerts node context menu

2. This will open the New Alert dialog. Type in the alert name (e.g. AlertAuthenticationMode) and check the Error number option

   The error number depends on the evaluation mode we used on a declared policy. There are four evaluation modes and each is represented by a specific error number in SQL Server logs

   1. On change: prevent (if automatic) – 34050
   2. On change: prevent (if On demand) – 34051
   3. On schedule – 34052
   4. On change – 34053
   
   

   As we used On schedule, type in the 34052 value in the Error number box

   

3. Select the Response page in the dialog, check the Notify operators option, and check the E-mail checkbox for a desired operator (e.g. PolicyManagementOperater)

   

4. Select the Options page in the dialog and check the E-mail option in order to include alert error text in the notification email
5. Click Ok to save the alert

Note a common omission made during the previously described process. If the policy is left disabled (the 7^th step while creating the policy), it will not create any SQL Server log entries and therefore will not be found by the alert. Even manually evaluated disabled polices do not create SQL Server log entries

The SQL Server Policy Based Management alerting helps DBAs to automatically verify declared SQL Server security settings across enterprise and get notified about any policy compliance violation.