Microsoft Purview access policies for SQL Server 2022

August 11, 2022

Product
SQL

Part of the SQL Server 2022 blog series.

Overview

Microsoft Purview is a family of data governance, risk, and compliance solutions that help organizations:

Gain visibility into data assets across your organization. Data assets can be across data centers, multicloud, and software as a service (SaaS) data.
Enable secure access to your data.
Safeguard and manage sensitive data across clouds, apps, and endpoints.
Manage data risks and regulatory compliance end-to-end.
Empower your organization to govern, protect, and manage data in new, comprehensive ways.

The focus of this article is on using Microsoft Purview to enable access to user data as well as specific system metadata in SQL Server 2022 running on Azure Arc–enabled servers.

With the SQL Server 2022 release, the goal is to enable three main scenarios:

Browsing data in user-defined tables and views.
Performance monitoring with system commands, functions, and views.
Security auditing with security-related system functions and views.

How it works

To complete these scenarios the data owner first needs to author a policy. A purview policy is a set of purview statements, each containing a purview role, scope, and assigned Azure AD principal.

Depending on the scenario, a purview role would be one of the built-in roles: Read, SQL Performance Monitor, or SQL Security Auditor.

With scope, the policy author defines an applicable target for the statement. It could be either a specific SQL Server or a wider scope like a resource group or a subscription.

To apply the policy, the author then needs to publish it to the target resources. One thing to keep in mind here is that it takes a couple of minutes for a policy to become active as it takes that much time for the SQL engine to pull it in.

Once the policy is active, the assigned users will be able to connect and perform actions assigned by the policy. The Connect permission is implicit, so there is no need to create logins or database users using T-SQL.

Let’s see this in a more visual fashion using the scenario walkthrough.

Scenario walkthrough

For SQL Server to be able to use policies, it needs to be registered with a Microsoft Purview account and enabled for Data use management.

Microsoft Purview form showing the Name, Azure subscription.

In this scenario, Ana, who is an IT manager, wants to provide Chris, who is a data analyst, read-only access to tables in a SQL Server database so that Chris can create reports.

To accomplish this, Ana authors a policy that will allow Chris access to the target server.

Access Control Policy form showing the Name, Description, and Policy statements sections filled out.

When finished, Ana publishes this policy and applies it to SQL Server by publishing it.

Policy publish form showing FinanceSQLonArc selected.

Now Chris can connect to the SQL Server and execute queries against the server to which the policies are applied.

SQL Query showing actions taken previously to get user connected to SQL Server without explicit login.

As highlighted above, Chris could connect to SQL Server even though there is no explicit login.

Summary

The new access policies feature provides data owners the capability to author access policies through the Microsoft Purview data-governance service experience and then apply them to SQL Server 2022 data sources individually or at scale. With this approach users are empowered to assign permissions to Azure Active Directory users at a scale without using T-SQL or the need to explicitly create logins or users on a server. In this release, the following server roles are eligible for assignment: Read, SQL Performance Monitor, and SQL Security Auditor.

Next steps

Microsoft Purview access policies for SQL Server 2022 are just one of the many benefits of migrating to SQL Server 2022.

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionality being added to this release.