Private Preview: controlling access to Azure SQL at scale with policies in Purview

Announcement: Purview policy based access control for Azure SQL at scale – Private Preview

I am excited to present a new way to control access to resources by assigning policies in Azure Purview to Azure SQL Database.

Benefit

Controlling access to database servers and databases - especially when in a modern cloud-based enterprise there can be hundreds and thousands of databases laid out in a subscription - comes with multiple challenges, such as:

• Having to deploy the permission-assignments individually
• Standardizing permission assignments
• Keeping track of current permissions and identifying potential violations
• While doing that, also adhere to the Principle of Least Privilege

Using policies in Azure Purview, customers can create policies that allow Azure Active Directory Groups and AAD Users to access Azure SQL Database within a subscription.

These policies can be assigned at any level of the resource hierarchy (individual database, server, resource group or even whole subscription), and by that apply effectively to all SQL Servers and databases within the given resource scope.

Everything is deployed by Rest-API calls, which removes the need to run T-SQL in subsequent PowerShell scripts.

At the same time, access to the databases, since it is controlled from outside of the SQL engine, does not get mirrored from a primary database to its read-only replicas: what matters is where the database resides within the subscription hierarchy. This enables scenarios where Users need access to only either the read-only replica or the primary database but must not connect to the other copy of the database.

Covered functionalities

This first private preview covers the following use-cases:

SQL Performance Monitoring

A predefined set of permissions (called Action-Groups) that grant access to system metadata, that is commonly used for Performance Monitoring tasks, via a set of system views that are covered under these new permissions

SQL Security Auditing

A predefined set of permissions that grant access to system metadata, that is commonly used for Security Auditing tasks, via a set of system views that are covered under these new permissions

Technical background

For Purview Policies to have effect on a given database in Azure SQL Database, 3 things need to come together:

1. The logical server must allow external policies to have effect – this requires owner-level permissions

At this stage, all databases under that logical server will in addition to the existing SQL permissions also understand new permissions that are coming from Purview Policies.
You can see this in this screenshot, where the queried DMV requires either the traditional SQL permission VIEW SERVER PERFORMANCE STATE or the new RBAC Action Microsoft.Sql/sqlservers/SystemViewsAndFunctions/ServerState/rows/select

2. The logical server must be registered in an Azure Purview Account

Together with step 1 this enables a 4-eye principle as it is required for SoD-compliant environments.

3. A policy, using one of the 2 built-in Action-Groups must be created and published in Azure Purview Studio

Call to Action

At this point we are opening a private preview for a small audience only. If you would like to take part and have an early hands-on experience with this new capability, please use the following link to let us know, and we will get back to you when there is a slot available in the limited private preview.

Opt-in to private Preview

Further references

Security: The Principle of Least Privilege (POLP) - Microsoft Tech Community

Introduction to Azure Purview - Azure Purview | Microsoft Docs

Quickstart: Create a Purview account in the Azure portal - Azure Purview | Microsoft Docs

Azure Active Directory documentation | Microsoft Docs

Acknowledgement

This new capability is possible thanks to the collaboration between the SQL Security Engineering team and the Purview Data governance team. Many people have been involved across continents and time zones. Thank you all for your hard work!