Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community
Inexpensive solution for managing access to SQL health, performance and security information
Published Feb 28 2023 09:35 AM 5,634 Views
Microsoft

I've written before about the power of Microsoft Purview data policies, and more recently about the Microsoft Purview DevOps policies (see here). In this article, I will detail how you can use DevOps policies as an inexpensive solution to provision access at-scale for IT/DevOps personnel tasked with monitoring and auditing SQL system health, performance, and security. In fact, I will show you a way to test them for free!

 

Microsoft Purview governance is well known for its ability to map, search and classify a customer's data estate. But there is a lot more. For example, the Microsoft Purview Data policy App is seamlessly integrated with the data map and can help you provision user access to data sources and datasets. In essence, you create the policy in one of the Data Policy Apps multiple experiences and that policy is then communicated and gets enforced on a set of data sources. One such experience is the Microsoft Purview DevOps policies, which focuses on access control to SQL system metadata. Because access is configured automatically from Purview, it eliminates the need for a SQL sysadmin to create local users and assign to them privileges. This helps curb insider risk.

 

Microsoft Purview DevOps policies currently support Azure SQL Database, SQL Server 2022 (Arc-enabled) and now also Azure SQL MI. A DevOps policy has 3 parts:

  • The subject: The list of Azure AD users, groups, or service principals that are granted access.
  • The data resource: This is the scope where the policy gets enforced. It can be a SQL data source, but also an entire Azure resource group or a subscription. A policy on a resource group or subscription will be enforced by all SQL data sources that belong to it and are enabled for policies from Microsoft Purview.
  • The role: describes the set of permissions that are granted on the data resource. For DevOps policies, this can be either "SQL Performance Monitor" or "SQL Security Auditor". Essentially, each role provides access to a large subset of DMVs/DMFs that are very useful to either monitor SQL system performance/health or to audit security. Crucially, these role definitions do not include access to the user data, which is another way DevOps policies help curb insider risk.
To summarize, the DevOps policy assigns the role's related permissions to the subject and gets enforced in the scope of the data resource's path.
 

Now, you can use a Microsoft Purview account just to get the benefits of the DevOps policies. This standalone solution can help stretch a limited IT budget! Here are the high-level steps that will help you save money (refer to the configuration guides at the end of this article for more details).

 

  • Create a new Microsoft Purview account (or use an existing one).
  • Register the SQL data source in Microsoft Purview and enable Data use management. DevOps policies work at SQL server level, which only requires their registration. In other words, there is no need for Microsoft Purview to scan the SQL data sources, so you can avoid those costs. But you also accomplish a second thing: registering without scanning keeps the size of Microsoft Purview's data map small. If the size of your Microsoft Purview's data map is below 1MB, Microsoft Purview does not charge for "Data Map Consumption". So just with that, you avoid two of the biggest costs. By the way, the size of your Microsoft Purview's data map is in Azure portal under Overview > Data Map Storage Size.

Screenshot 2023-02-24 171643.png

  • Starting April 2023 each DevOps policy is billed at around US$2 a month (or equivalent in your local currency), prorated using hourly intervals. The price does not change with how many Azure AD users or groups you configure in the subject of the policy. The price is also the same whether the data resource is a SQL data source, a resource group or a subscription. Note, policies on data source types that are still in private preview (hint Azure SQL MI) are free, so you can take advantage to test-drive DevOps policies.
  • Create an Azure AD group with the IT/DevOps personnel that need to monitor SQL performance or audit SQL security. Add to the group the primary + its backups (as the primary may get sick or take a vacation from time to time). This Azure AD group will be in charge of dozens of SQL data sources (Azure SQL or SQL on-prem). If the company is big enough, you will need multiple Azure AD groups, each in charge of a set of SQL data sources.
  • To increase efficiency and accelerate your savings, map each set of SQL data sources to a single Azure resource group. And instead of creating DevOps policies on individual SQL data sources, create them directly on the resource group. We show you here how. As mentioned before, each DevOps policy on a resource group or subscription is priced at around US$2 per month, really inexpensive for the value you get! Here is an example of a DevOps policy on an entire resource group: 

Screenshot 2023-02-24 175343.png

  • You want to try this for free? Leverage the Azure free trial, which includes $200 in credit for 30 days.

 

Recommended next steps:

Co-Authors
Version history
Last update:
‎Apr 27 2023 06:59 PM
Updated by: