SQL Server security and Policy Based Management In practice
SQL Server security and Policy Based Management - In practice
In this article, we are going to describe a step-by-step process of creating appropriate policies for the login Password expiration enabled and database user Authentication type properties consistency evaluation as aspects of SQL Server security Note that the whole process of creating and evaluating policies can be done using either T-SQL or SQL Server Management Studio. To make this article more descriptive we’ll focus on SQL Server Management Studio. However, to check appropriate T-SQL, you can use the Script Policy as and Script Condition as options in the context menu of corresponding Policy Based Management objects in Object Explorer Each policy primary depends on the condition that will be used. Besides the condition, the policy is additionally defined against target objects
View all posts by Ivan Stankovic Latest posts by Ivan Stankovic (see all) Using Extended Events to review SQL Server failed logins - August 5, 2014 SQL Server backup – models and types - May 26, 2014 SQL Server Policy Based Management – Categories and Database Subscriptions - May 21, 2014
SQLShack
SQL Server training EspañolSQL Server security and Policy Based Management – In practice
March 16, 2014 by Ivan Stankovic In the previous introduction article we have described concepts, terms, and common Policy Based Management tasks. The SQL Server Policy Based Management feature can be used in various scenarios and for different purposes. Using once declared policies and the Export/Import feature, maintaining SQL Server security in large enterprises with a number of SQL Server instances can be an automated processIn this article, we are going to describe a step-by-step process of creating appropriate policies for the login Password expiration enabled and database user Authentication type properties consistency evaluation as aspects of SQL Server security Note that the whole process of creating and evaluating policies can be done using either T-SQL or SQL Server Management Studio. To make this article more descriptive we’ll focus on SQL Server Management Studio. However, to check appropriate T-SQL, you can use the Script Policy as and Script Condition as options in the context menu of corresponding Policy Based Management objects in Object Explorer Each policy primary depends on the condition that will be used. Besides the condition, the policy is additionally defined against target objects
Conditions creation
To create a condition: Start SQL Server Management Studio and expand the Management node. Expand the Policy Management node and open the context menu of the Conditions sub-node. Select the New Condition option The Create New Condition dialog will open. Type in the name of the new condition (e.g. ConditionPasswordExpirationEnabled). From the Facets dropdown menu select the one which contains a property we want to create condition for. In our case it is the Login facet. Note that each condition can use one facet only In the Expression grid, set the Field, Operator, and Value dropdown options to the following values: @PasswordExpirationEnabled, =, and True. This means that the condition will check whether the Password expiration enabled property is set on particular login entities Click OK to save the condition Similarly, using appropriate steps (1-4), create another condition (e.g. ConditionDatabaseAuthenticationType) using the User Options facet, @LoginType field, ‘=’ operator, and WindowsUser value The condition will test whether a user’s login type is using the Windows authentication The newly created conditions will be shown under the Conditions node in Object ExplorerPolicies creation
After the needed conditions are created, we can proceed with declaring and creating policies. To create a policy: Select the New Policy option in the context menu of the Policies node This will open the Create New Policy dialog. Type in the name for the policy (e.g. PolicyLoginPasswordExpiration) Select previously created ConditionPasswordExpirationEnabled condition using the Check condition dropdown menu. Note that the dropdown menu provides conditions sorted per used facets during their creation. We have used the Login and User Options facets, and our conditions are listed under these facets. This way you can to quickly locate needed condition The Against Targets box will automatically show the Every Login target. The target can be additionally customized, for instance to check only the logins with the name that matches specific pattern. We’ll explain the customization in further text in context of another policy The next step is to define the policy evaluation mode. Default is the On demand mode. However, we will choose the On schedule mode via the Evaluation Mode dropdown menu. Click the New button to open a common SQL Server Agent job schedule dialog. After you define the schedule, it will be shown under its name in the Schedule box (e.g. MorningCheck). You can also use the Pick option to open the list of available schedules that already exist on the SQL Server instance Using the Server restriction option, you can specify, for instance, that the policy is applicable to specific SQL Server instances (e.g. SQL Server 2012 and newer versions) Click the OK button to save the policy Similarly, using the same steps (1-3), create another policy (e.g. PolicyDatabaseAuthenticationType) with the ConditionDatabaseAuthenticationType condition. Note that there are two targets in the Against Targets box now. There are the Every User and Every Database targets since user entities exist in multiple SQL Server database objects. This time, we are going to customize targets and narrow down their scope by specifying a match pattern for database names (e.g. database name starts with ACME): Open the Every dropdown menu and select the New condition option, as shown below This will open the Create New Condition dialog we have previously described. However, this time we’ll use it to declare the condition which will narrow down the targets in the policy Type in the condition name (e.g. OnlyACMEDBs), leave the Database facet value (it’s set by default since we are creating the condition on databases), and set the expression to @Name LIKE ‘ACME%’ using the Expression grid, as shown below To save the condition and return to the PolicyDatabaseAuthenticationType policy creation dialog click OK The Create New Policy (PolicyDatabaseAuthenticationType) dialog will look like this: This time, we’ll leave the Evaluation mode option with the default On demand value. To save the policy click OK Once the policies are created, they are shown under the Policies node in Object Explorer. Note that the OnlyACMEDBs condition is also available for further use If needed, each condition or policy can be modified by selecting the Properties context menu option. Also, policies can be exported/imported from/to SQL Server instances (via context menu in Object Explorer) and instantly applied ensuring security consistency in the enterprisePolicy evaluation
Policies can be evaluated per their evaluation mode (as described in the previous article). In our example we have created two policies. One is set to be automatically evaluated based on the schedule, and the other one is set to be evaluated on user’s demand Whatever the policy evaluation mode is used, the policy can always be evaluated manually. To do that select the Evaluate context menu option of the specific policy (e.g. PolicyLoginPasswordExpiration). The Evaluate Policies dialog will open The dialog will provide the evaluation result for each target object in the Target details grid. In our example one login complies with the policy rule, while the other one does not To apply the policy to the target that doesn’t comply the rule, check the box next to the target item in the grid and click the Apply button. The policy will modify the target login according to the rule (in our case it will set the login’s Password expiration enabled property). The dialog will automatically re-evaluate the policy: In this article, we have described the necessary steps to create policy conditions, policies that use the conditions, and how to evaluate the policies against specific target objects. Finally, we described how the particular target object that does not comply with the policy can be automatically fixed. Policy Based Management is a powerful SQL Server feature that helps ensuring security across enterprise by applying declared values to SQL Server security related properties, both on SQL Server instances and their objects. Moreover, it provides ways to evaluate policies on target objects and apply the policies where required Author Recent Posts Ivan StankovicIvan is a SQL Server professional and computer geek with years of IT and SQL Server experience. He has startedwith playing computer games, continued with computer programming and system administration. His areas of expertise are SQL Server disaster recovery, auditing, and complianceView all posts by Ivan Stankovic Latest posts by Ivan Stankovic (see all) Using Extended Events to review SQL Server failed logins - August 5, 2014 SQL Server backup – models and types - May 26, 2014 SQL Server Policy Based Management – Categories and Database Subscriptions - May 21, 2014