Enable IT personnel to monitor SQL health and performance while reducing the insider risk

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

It is common that IT personnel tasked with monitoring the health and performance of database systems be given very high privileges such as SQL sysadmin. This enables them to do their job but comes with significant risks. Those privileges enable them to read or modify the data that other users in the organization store in those databases. That data is commonly referred to as “user data”. Sometimes user data can be very sensitive, for example, the consolidated financial information of a public company prior to being disclosed in an earnings report, a technological achievement that gives the company a competitive edge, and customer or employee information that must be protected to comply with privacy regulations. Sensitive data may be leaked or tampered with because of malicious intentions or simply poor security practices. When that happens, the company usually suffers financial damage and litigation against its officers.


Microsoft Purview DevOps policies support the Principle of Least Privilege (PoLP), which simply states that people should be given only the minimum access they need to be able to perform their job and no more. DevOps policies address the scenario of IT personnel tasked with monitoring the health and performance of database systems. This article showcases the experience for Azure SQL Managed Instance, the newest source supported for DevOps policies (soon to enter private preview). Azure SQL Database and SQL Server 2022 are already supported, and the configuration steps are linked at the end.


First, register the Azure SQL MI in Microsoft Purview and enable Data use management. This means consenting that you would like to use Microsoft Purview to grant users access to the Azure SQL MI.




Second, navigate to the Data Policy App in Microsoft Purview and then to DevOps policies. Create a policy, selecting the Azure SQL MI data source in the prior step. Once you do that, the Data resource path will show <subscription name > resource-group name > data source name>. Next, select one of two role definitions "SQL Performance Monitor" or "SQL Security Auditor". Finally, select the Add/remove subjects to specify the Azure AD user(s) or group(s) that should be granted access:




Once you save the policy, Microsoft Purview will communicate it to the Azure SQL MI. It may take up to 5 minutes to be enforced.


To test, you can use SSMS. Connect with one of the AAD users that was granted access and then execute a query to get system metadata (DMVs and DMFs). For example, SQL Performance Monitor grants access to see the virtual file stats or the wait times. SQL Security Auditor grants access to see database encryption keys. An IT user granted SQL Performance monitor user should be able to perform the operations:




Next, try accessing a table in one of the databases. The IT user is denied, which means the crown jewels are protected.




Recommended steps:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.