Although Policy Based Management in SQL Server provides a variety of facets with a range of properties and values in order to create complex conditions, DBAs often require more. Every SQL Server environment is specific and different security policies may be required.

The policy core consists of conditions and targets. Moreover, complex targets are defined by additional conditions, as we already showed in the SQL Server security and Policy Based Management – In practice article

Along with predefined properties, conditions can utilize T-SQL queries too. In this article, we’ll describe and provide a step-by-step example for creating an advanced Policy Based Management policy which uses T-SQL within its condition. The example will provide the policy which verifies whether SQL SERVER Agent alerts were reset, which can be a serious security alert that requires further investigation

The first thing you need to do when creating a new policy is to declare and create the condition on which the policy is based.

To create the condition:

1. Start SQL Server Management Studio, and expand the Management node in Object Explorer
2. Expand the Policy Management node and select the New Condition option in the Conditions sub-node context menu
3. Type in the condition name (e.g. NumberOfResetAlerts) in the Create New Condition dialog, and select the Server item from the Facets dropdown menu

4. Click the “…” button next to the empty field in the Expression grid

   

5. This will open the Advanced Edit dialog. Scroll down the Functions and properties list, locate the ExecuteSql() function, and double-click it in order to add it to the Cell value text box

   

   The ExecuteSql(string returnType, string sqlQuery) value will be shown and ready for additional modification. The ExecuteSql() function has two parameters:

   • The result type which the function provides specified by the returnType string variable. Supported types are Numeric, String, Bool, DateTime, and GUID
   • T-SQL specified by the sqlQuery variable

   The function return value is the first column in the first row of the result set returned by the T-SQL query. Only one column should be specified in the SELECT statement. Any additional columns are ignored. The same applies to returned rows. The rows beyond the first one are not considered. In case the query result is an empty set or NULL, the condition expression built on the ExecuteSql() function will be false

   In our example, we’ll use ‘Numeric’ as a type, and ‘SELECT COUNT(*) from sdb.dbo.sysalerts where count_reset_date=0’ T-SQL as a query. The final expression in the Cell value text box will be:

   ExecuteSql(‘Numeric’, ‘SELECT COUNT(*) from msdb.dbo.sysalerts where count_reset_date=0’)

   Click Ok to save the cell expression value

6. This will close the Advanced Edit dialog and return to the New Condition dialog. Select the != operator and type in the 0 as the value.

   

   This will complete the condition expression which evaluates whether the msdb.dbo.sysalerts system table holds any records about alerts that were reset at some point in time. Note that the query within the ExecuteSql() function can be modified to meet the requirements, if needed. In addition, the Expression grid can hold multiple rows with different functions and queries in order to narrow down the required condition.

To create the policy that will use the NumberOfResetAlerts condition:

1. Select the New Policy option from the context menu of the Policies node. The Create New Policy Dialog will be opened

2. Type in the policy name (e.g. ThereAreNoResetAlerts) and select the NumberOfResetAlerts condition from the Check condition dropdown list

   

3. Set the desired evaluation mode using the Evaluation Mode dropdown menu. Note that ExecuteSql() used in the condition supports the On Demand and On Schedule evaluation modes. On change evaluation modes are not supported

Depending on the evaluation mode, consider using an appropriate SQL Server Agent alert that can automatically notify you about policy violations and help keep your SQL Server secure.