In this article, we will be discussing about a special security feature in SQL Server, that is Policy-Based Management.
Some time ago I promised publishing a post on Policy-Based Management in SQL Server. Well, it’s time to fulfill my promise! 🙂 This is a rather large post, I hope you find the information it contains useful! Note that this article and examples talk about SQL Server 2008, but it can easily apply to newer versions of SQL Server, maybe with some minor changes.
What is Exactly Policy-Based Management in SQL Server?
Policy-Based Management is indeed one of the greatest administration features originally shipped in SQL Server 2008. With this feature, you can easily manage one or more instances of SQL Server!
The main concept in Policy-Based Management, is that Policy Administrators can set up policies that are evaluated upon certain events within the scope of the monitored SQL Server instance(s). After setting up the policies, based on certain scenarios, the policies are evaluated and specific actions are automatically performed as set up in the active Policies.
To this end, there are four evaluation modes:
On demand – This mode evaluates the policies when explicitly requested by the user.
On change: prevent – This mode uses DDL triggers in order to prevent Policy violations.
On change: log only – This mode allows Policy violations but it logs each violation using event notification. It logs the violations to the SQL Server logs and Windows Application logs.
On schedule – This mode allows the user to schedule (using a SQL Agent job) when the policies will be evaluated.
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Let’s get a first look and feel at the Policy-Based Management set up dialogs in SSMS. In the following screenshot you can see the Policy Management module in the Object Explorer in SSMS:
Actions in SSMS:
When right-clicking on Policies you can create a new Policy.
When right-clicking on Conditions you can create a new condition and then assign it to an existing or a new Policy.
The Facets is a collection of SQL Server objects that allow you to set up conditions and policies against their properties. By right-clicking on a Facet you can create a condition or a Policy.
Note that the Policy Management module falls under Management as its scope is for the entire SQL Server instance. Of course, via the Policies you create, you can evaluate multiple SQL Server instances, certain databases, even certain database objects!
OK! Now let’s proceed with an example, showing how we can set up policies based on the above-mentioned evaluation modes. For this post, I will be using the “AdventureWorks2008” sample database.
Policy-Based Management Evaluation Modes
On demand
We right-click on Policies and we select New Policy. We are presented with the following dialog where we provide a name for the Policy and then we either select the check condition or we create a new one. In this case we will be creating a new condition:
The next screenshot shows the newly created condition:
For creating a new condition, you must provide a name, select the Facet (in this example) the Stored Procedure, and set the expressions for evaluating the properties you like. In this case we want the condition to track all the stored procedures that their names start with ‘sp_’.
We click on the OK button and we are returned to the Policy:
You can see that the Policy now contains the “SPNames” condition we created in the previous step. Also, I set up the Evaluation Mode to “On Demand”. Though, this type of condition allows you to select any Evaluation Mode.
The available Evaluation Modes are dependent on the type of the condition. For example there are conditions for which a DDL trigger cannot be executed so the “On change: prevent” mode cannot be selected in such case, etc.
Also, in the “Against targets” list you can target specific databases against which the condition will be evaluated. Additionally, in the “Server restriction” you can select against which SQL Server instances the condition will be executed. Remember: Policy-Based Management allows managing multiple instances of SQL Server!
Now, let’s set the Policy to only evaluate the “AdventureWorks2008” database by clicking on the “In Every – Database” list item. Here’s the new condition for restricting the Policy’s evaluation on the specific database:
We click on OK and we are returned back to the Policy properties dialog:
I will not add a condition for the Server restriction as I am only using a single SQL Server instance for this post.
That’s it! We click on OK and the Policy is ready! As this Policy’s Evaluation Mode has been set to “On demand” we cannot enable it. It wouldn’t make any sense after all. We can only use it by right-clicking on it and selecting “Evaluate”. This is the purpose of the On Demand evaluation mode after all! 🙂
After running the Policy we get its evaluation results:
From the above screenshot we can conclude that at the time being all stored procedures within the “AdventureWorks2008” database comply with the condition set up in the Policy.
Now, what about adding a new procedure on the “AdventureWorks2008” database having the name “sp_SampleSP”? Let’s do so, re-run the Policy and check the evaluation results!
Here are the results:
Oops, we got an error message by the Policy. Let’s click on [Details] View… to see what’s wrong:
We can see that the Policy reported that the stored procedure “sp_SampleSP” does not comply with its condition of not having stored procedures with names starting with ‘sp_‘.
The above is a very simple example of what a Policy can do. Imagine what else you can do with Policy-Based Management thus making your life easier! 🙂
Let’s delete now the sample stored procedure we earlier created. We need to comply after all! 🙂
On change: prevent
If we want to change the Evaluation Mode of the above Policy to “On change: prevent”, we can double-click on the Policy, change it and then enable the Policy:
Now let’s try to create again the same stored procedure:
As you can see from the above screenshot, the Policy prevented the Database Engine from creating the stored procedure as it does not comply with the naming condition specified by the Policy!
On change: log only
Similarly, we can change the Evaluation Mode of the Policy to “On change: log only”. This will allow the stored procedure to be created but it will also add a log entry in SQL Server Logs and Windows Application Log reporting the violation of the Policy:
On schedule
Last but not least, you can set up a schedule for when the Policy should be executed. This will create a SQL Server Agent job that will execute the Policy at the selected time.
Summary and Conclusions
This article was a very basic example on how you can use Policy-Based Management in SQL Server 2008 or later. You can build really advanced Policy scenarios where you can have multiple Policies and conditions, evaluating a large set of actions in more than one SQL Server instances.
The Policy-Based Management feature in SQL Server 2008 is extremely powerful and allows setting up strict Compliance scenarios ensuring the health and desired structure of the monitored SQL Server instances and the databases that exist within them.
The best way to learn more about Policy-Based Management in SQL Server is to get experimented with it in order to fully realize its potentials.
You can also check out this link for more information on Policy-Based Management in SQL Server.
Secure your SQL Server Instances with DBA Security Advisor
DBA Security Advisor, is our SQL Server security tool, which can help you assess your SQL Server instances against a rich set of security checks. The assessment report, includes recommendations and remediation scripts that can help you better secure your SQL Server instances and databases (learn more…).
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.
Views:4,087
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent. Read More
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.