Provision access by data owner for Azure Arc-enabled SQL Server (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 Arc-enabled SQL Server. The following actions are currently enabled: Read. This action is only supported for policies at server level. Modify is not supported at this point.

Prerequisites

Region support

Policy enforcement is available in only the following regions for Microsoft Purview:

  • East US
  • East US 2
  • South Central US
  • West Central US
  • West US
  • West US2
  • West US3
  • Canada Central
  • Brazil South
  • North Europe
  • West Europe
  • France Central
  • Switzerland North
  • UK South
  • UAE North
  • South Africa North
  • Central India
  • Korea Central
  • Japan East
  • Australia East

Security considerations for Azure Arc-enabled SQL Server

  • The server admin can turn off the Microsoft Purview policy enforcement.
  • Azure Arc admin and server admin permissions provide the ability to change the Azure Resource Manager path of the server. Because mappings in Microsoft Purview use Resource Manager paths, this can lead to wrong policy enforcements.
  • A SQL Server admin (database admin) can gain the power of a server admin and can tamper with the cached policies from Microsoft Purview.
  • The recommended configuration is to create a separate app registration for each SQL server instance. This configuration prevents the second SQL Server instance from reading the policies meant for the first SQL Server instance, in case a rogue admin in the second SQL Server instance tampers with the Resource Manager path.

Verify the pre-requisites

  1. Sign in to the Azure portal through this link

  2. Navigate to SQL servers on the left pane. You will see a list of SQL Server instances on Azure Arc.

  3. Select the SQL Server instance that you want to configure.

  4. Go to Azure Active Directory on the left pane.

  5. Ensure that Azure Active Directory authentication is configured with an admin login. If not, refer to the access policy prerequisites section in this guide.

  6. Ensure that a certificate has been provided to for SQL Server to authenticate to Azure. If not, refer to the access policy prerequisites section in this guide.

  7. Ensure that an app registration has been entered to create a trust relationship between SQL Server and Azure AD. If not, refer to the access policy prerequisites section in this guide.

  8. If you made any changes, select the Save button to save the configuration and wait until it shows it completed successfully.

    Screenshot that shows pre-requisites to configure a Microsoft Purview endpoint in the Azure Active Directory section.

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.

Delegation of 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 that 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 place a lock to prevent the Microsoft Purview account from being deleted through Resource Manager locks.

Register data sources in Microsoft Purview

Register each data source with Microsoft Purview to later define access policies.

  1. Sign in to Microsoft Purview Studio.

  2. Navigate to the Data map feature on the left pane, select Sources, then select Register. Type "Azure Arc" in the search box and select SQL Server on Azure Arc. Then select Continue Screenshot shows how to select a source for registration.

  3. Enter a Name for this registration. It is best practice to make the name of the registration the same as the server name in the next step.

  4. select an Azure subscription, Server name and Server endpoint.

  5. Select a collection to put this registration in.

  6. Enable Data Use Management. Data Use Management needs certain permissions and 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

  7. Upon enabling Data Use Management, Microsoft Purview will automatically capture the Application ID of the App Registration related to this Azure Arc-enabled SQL Server if one has been configured. Come back to this screen and hit the refresh button on the side of it to refresh, in case the association between the Azure Arc-enabled SQL Server and the App Registration changes in the future.

  8. Select Register or Apply at the bottom

Once your data source has the Data Use Management toggle Enabled, it will look like this picture. Screenshot shows how to register a data source for policy.

Enable policies in Azure Arc-enabled SQL Server

This section describes the steps to configure SQL Server on Azure Arc to use Microsoft Purview. Execute these steps after you enable the Data use management option for this data source in the Microsoft Purview account.

  1. Sign in to the Azure portal through this link

  2. Navigate to SQL servers on the left pane. You will see a list of SQL Server instances on Azure Arc.

  3. Select the SQL Server instance that you want to configure.

  4. Go to Azure Active Directory on the left pane.

  5. Scroll down to Microsoft Purview access policies.

  6. Select the button to Check for Microsoft Purview Governance. Wait while the request is processed. While that happens, this message will be displayed at the top of the page. You may need to scroll up to see it. Screenshot that shows Arc-SQL agent is processing a request

  7. Do a browser refresh. At the bottom of the page, confirm that the Microsoft Purview Governance Status shows Governed

  8. Confirm that the Microsoft Purview Endpoint points to the Microsoft Purview account where you registered this data source and enabled the Data use management

  9. Compare the App registration ID with the one shown in the Microsoft Purview account registration for this data source

    Screenshot that shows Microsoft Purview endpoint status in the Azure Active Directory section.

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:

Example: Read policy. This policy assigns the Azure AD principal 'sg-Finance' to the SQL Data reader action, in the scope of SQL server DESKTOP-xxx. This policy has also been published to that server. Note that policies related to this action are not supported below server level.

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

Note

  • Given that scan is not currently available for this data source, data reader policies can only be created at server level. Use the Data sources box instead of the Asset box when authoring the data resources part of the policy.
  • There is a know issue with SQL Server Management Studio that prevents right-clicking on a table and choosing option “Select Top 1000 rows”.

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 Arc-enabled SQL Server.

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