Inexpensive solution for managing access to SQL health, performance and security information

[Guest inward-eye]

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.

 

 

 

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 soon Azure SQL MI. A DevOps policy has 3 parts:

 

• The subject: The set of Azure AD users, groups, or service principals that are granted access.
  
• The data resource: this can be a SQL data source, an 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 SQL data source(s). 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.
  

 

 

 

Now, you can use a Microsoft Purview account only 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.
  

 

 

• Starting April 1st, 2023 each DevOps policy will be priced at around US$2 a month (or equivalent in your local currency), prorated using hourly intervals. Note, before April 1st, you can try DevOps policies at no additional charge. Also, data source types still in private preview will not have an additional charge, even after April 1st. 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.
  
• 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 will be priced at around US$2 per month after April 1st 2023, really inexpensive for the value you get! Here is an example of a DevOps policy on an entire resource group:
  

 

 

 

 

Recommended next steps:

 

• DevOps policies for Azure SQL MI (Private Preview): Click here to test-drive this new experience. Note, your Microsoft Purview account and Azure SQLM MI will be allow-listed after you enroll.
  
• DevOps policies for Azure SQL Database (Public Preview) and SQL Server 2022 (GA):
  
  • Quick overview video on YouTube - DevOps policies: provision access at-scale for IT operations users while limiting insider threats
    
  • Concept guide: Microsoft Purview DevOps policies concepts - Microsoft Purview
    
  • Quick start: Manage access to Azure SQL Database system health and performance using Microsoft Purview DevOps policies, a type of RBAC policies. - Microsoft Purview
    

 

Continue reading...