Provision access by data owner for Azure SQL Database (preview)

Important

This feature is currently in preview. The Supplemental Terms of Use for Microsoft Azure Previews include additional legal terms that apply to Azure features that are in beta, in preview, or otherwise not yet released into general availability.

Data owner policies are a type of Microsoft Purview access policies. They allow you to manage access to user data in sources that have been registered for Data Use Management in Microsoft Purview. These policies can be authored directly in the Microsoft Purview governance portal, and after publishing, they get enforced by the data source.

This guide covers how a data owner can delegate authoring policies in Microsoft Purview to enable access to Azure SQL Database. The following actions are currently enabled: Read. Modify is not supported at this point.

Prerequisites

Create a new Azure SQL Database instance, or use an existing one, in one of the currently available regions for this preview feature. You can follow this guide to create an Azure SQL Database instance.

Region support

All Microsoft Purview regions are supported.

Enforcement of Microsoft Purview policies is available only in the following regions for Azure SQL Database:

  • East US
  • East US2
  • South Central US
  • West Central US
  • West US3
  • Canada Central
  • Brazil South
  • West Europe
  • North Europe
  • France Central
  • UK South
  • South Africa North
  • Central India
  • East Asia
  • Australia East

Configure the Azure SQL Database instance for policies from Microsoft Purview

For the logical server associated with Azure SQL Database to honor policies from Microsoft Purview, you need to configure an Azure Active Directory admin. In the Azure portal, go to the logical server that hosts the Azure SQL Database instance. On the side menu, select Azure Active Directory. Set an admin name to any Azure Active Directory user or group that you prefer, and then select Save.

Screenshot that shows the assignment of an Active Directory admin to a logical server associated with Azure SQL Database.

Then, on the side menu, select Identity. Under System assigned managed identity, turn the status to On, and then select Save.

Screenshot that shows the assignment of a system-assigned managed identity to a logical server associated with Azure SQL Database.

You also need to enable (and verify) external policy-based authorization on the logical server associated with Azure SQL Database. You can do this in PowerShell:

Connect-AzAccount -UseDeviceAuthentication -TenantId xxxx-xxxx-xxxx-xxxx-xxxx -SubscriptionId xxxx-xxxx-xxxx-xxxx

$server = Get-AzSqlServer -ResourceGroupName "RESOURCEGROUPNAME" -ServerName "SERVERNAME"

#Initiate the call to the REST API to set the externalPolicyBasedAuthorization property to true
Invoke-AzRestMethod -Method PUT -Path "$($server.ResourceId)/externalPolicyBasedAuthorizations/MicrosoftPurview?api-version=2021-11-01-preview" -Payload '{"properties":{"externalPolicyBasedAuthorization":true}}'

# Verify that externalPolicyBasedAuthorization is set to true
Invoke-AzRestMethod -Method GET -Path "$($server.ResourceId)/externalPolicyBasedAuthorizations/MicrosoftPurview?api-version=2021-11-01-preview"

In the response, "properties":{"externalPolicyBasedAuthorization":true} should appear under Content.

Microsoft Purview configuration

Configure permissions to enable Data use management on the data source

Before a policy can be created in Microsoft Purview for a resource, you must configure permissions. To enable the Data use management toggle for a data source, resource group, or subscription, the same user must have both specific identity and access management (IAM) privileges on the resource and specific Microsoft Purview privileges:

  • The user must have either one of the following IAM role combinations on the resource's Azure Resource Manager path or any parent of it (that is, using IAM permission inheritance):

    • IAM Owner
    • Both IAM Contributor and IAM User Access Administrator

    To configure Azure role-based access control (RBAC) permissions, follow this guide. The following screenshot shows how to access the Access Control section in the Azure portal for the data resource to add a role assignment.

    Screenshot that shows the section in the Azure portal for adding a role assignment.

  • The same user needs to have the Microsoft Purview Data source admin role for the collection or a parent collection (if inheritance is enabled). For more information, see the guide on managing Microsoft Purview role assignments.

    The following screenshot shows how to assign the Data source admin role at the root collection level.

    Screenshot that shows selections for assigning the Data source admin role at the root collection level.

Configure Microsoft Purview permissions to create, update, or delete access policies

The following permissions are needed in Microsoft Purview at the root collection level:

  • The Policy author role can create, update, and delete DevOps and Data Owner policies.
  • The Policy author role can delete self-service access policies.

For more information about managing Microsoft Purview role assignments, see Create and manage collections in the Microsoft Purview Data Map.

Note

Currently, Microsoft Purview roles related to creating, updating, and deleting policies must be configured at the root collection level.

In addition to the Microsoft Purview Policy author role, users might need Directory Readers permission in Azure Active Directory to create a policy. This is a common permission for users in an Azure tenant.

Configure Microsoft Purview permissions for publishing Data Owner policies

Data Owner policies allow for checks and balances if you assign the Microsoft Purview Policy author and Data source admin roles to different people in the organization. Before a data policy takes effect, a second person (Data source admin) must review it and explicitly approve it by publishing it. Publishing is automatic after DevOps or self-service access policies are created or updated, so it doesn't apply to these types of policies.

The following permissions are needed in Microsoft Purview at the root collection level:

  • The Data source admin role can publish a policy.

For more information about managing Microsoft Purview role assignments, see Create and manage collections in the Microsoft Purview Data Map.

Note

Currently, Microsoft Purview roles related to publishing Data Owner policies must be configured at the root collection level.

Delegate access provisioning responsibility to roles in Microsoft Purview

After a resource has been enabled for Data use management, any Microsoft Purview user with the Policy author role at the root collection level can provision access to that data source from Microsoft Purview.

The IAM Owner role for a data resource can be inherited from a parent resource group, a subscription, or a subscription management group. Check which Azure AD users, groups, and service principals hold or are inheriting the IAM Owner role for the resource.

Note

Any Microsoft Purview root Collection admin can assign new users to root Policy author roles. Any Collection admin can assign new users to a Data source admin role under the collection. Minimize and carefully vet the users who hold Microsoft Purview Collection admin, Data source admin, or Policy author roles.

If a Microsoft Purview account with published policies is deleted, such policies will stop being enforced within an amount of time that depends on the specific data source. This change can have implications on both security and data access availability. The Contributor and Owner roles in IAM can delete Microsoft Purview accounts.

You can check these permissions by going to the Access control (IAM) section for your Microsoft Purview account and selecting Role Assignments. You can also use a lock to prevent the Microsoft Purview account from being deleted through Resource Manager locks.

Register the data sources in Microsoft Purview

The Azure SQL Database data source needs to be registered first with Microsoft Purview before creating access policies. You can follow these guides:

Register and scan Azure SQL Database

After you've registered your resources, you'll need to enable Data Use Management. Data Use Management can affect the security of your data, as it delegates to certain Microsoft Purview roles to manage access to the data sources. Go through the secure practices related to Data Use Management in this guide: How to enable Data Use Management

Once your data source has the Data Use Management toggle Enabled, it will look like this screenshot. This will enable the access policies to be used with the given Azure SQL server and all its contained databases. Screenshot shows how to register a data source for policy.

Create and publish a data owner policy

Execute the steps in the Create a new policy and Publish a policy sections of the data-owner policy authoring tutorial. The result will be a data owner policy similar to the example shown.

Example: Read policy. This policy assigns the Azure AD principal 'Robert Murphy' to the SQL Data reader action, in the scope of SQL server relecloud-sql-srv2. This policy has also been published to that server. Note that policies related to this action are supported below server level (e.g., database, table)

Screenshot shows a sample data owner policy giving Data Reader access to an Azure SQL Database.

Important

  • Publish is a background operation. It can take up to 5 minutes for the changes to be reflected in this data source.
  • Changing a policy does not require a new publish operation. The changes will be picked up with the next pull.

Unpublish a data owner policy

Follow this link for the steps to unpublish a data owner policy in Microsoft Purview.

Update or delete a data owner policy

Follow this link for the steps to update or delete a data owner policy in Microsoft Purview.

Test the policy

The Azure AD Accounts referenced in the access policies should now be able to connect to any database in the server to which the policies are published.

Force policy download

It is possible to force an immediate download of the latest published policies to the current SQL database by running the following command. The minimal permission required to run it is membership in ##MS_ServerStateManager##-server role.

-- Force immediate download of latest published policies
exec sp_external_policy_refresh reload

Analyze downloaded policy state from SQL

The following DMVs can be used to analyze which policies have been downloaded and are currently assigned to Azure AD accounts. The minimal permission required to run them is VIEW DATABASE SECURITY STATE - or assigned Action Group SQL Security Auditor.


-- Lists generally supported actions
SELECT * FROM sys.dm_server_external_policy_actions

-- Lists the roles that are part of a policy published to this server
SELECT * FROM sys.dm_server_external_policy_roles

-- Lists the links between the roles and actions, could be used to join the two
SELECT * FROM sys.dm_server_external_policy_role_actions

-- Lists all Azure AD principals that were given connect permissions  
SELECT * FROM sys.dm_server_external_policy_principals

-- Lists Azure AD principals assigned to a given role on a given resource scope
SELECT * FROM sys.dm_server_external_policy_role_members

-- Lists Azure AD principals, joined with roles, joined with their data actions
SELECT * FROM sys.dm_server_external_policy_principal_assigned_actions

Additional information

Policy action mapping

This section contains a reference of how actions in Microsoft Purview data policies map to specific actions in Azure SQL DB.

Microsoft Purview policy action Data source specific actions
Read Microsoft.Sql/sqlservers/Connect
Microsoft.Sql/sqlservers/databases/Connect
Microsoft.Sql/Sqlservers/Databases/Schemas/Tables/Rows
Microsoft.Sql/Sqlservers/Databases/Schemas/Views/Rows

Next steps

Check blog, demo and related how-to guides