Provision access by data owner for SQL Server on Azure Arc-enabled servers (preview) – Microsoft Purview

Table of Contents0.1 In this article1 Prerequisites2 Security considerations3 Configuration3.1 Register Microsoft Purview as a resource provider in other subscriptions3.2 Configure permissions for policy management actions3.2.1 Permissions to make a data resource available for Data Use Management3.2.2 Permissions for policy authoring and publishing3.2.3 Delegation of access control responsibility to Microsoft […]

Access policies allow you to manage access from Microsoft Purview to data sources that have been registered for Data Use Management.

This how-to guide describes how a data owner can delegate authoring policies in Microsoft Purview to enable access to SQL Server on Azure Arc-enabled servers. The following actions are currently enabled: SQL Performance Monitoring, SQL Security Auditing and Read. These 3 actions are only supported for policies at server level. Modify is not supported at this point.

Prerequisites

Enforcement of policies for this data source is available only in the following regions for Microsoft Purview

  • East US
  • East US 2
  • South Central US
  • West US 3
  • Canada Central
  • West Europe
  • North Europe
  • UK South
  • France Central
  • UAE North
  • Central India
  • Korea Central
  • Japan East
  • Australia East

Security considerations

  • The Server admin can turn off the Microsoft Purview policy enforcement.
  • Arc Admin/Server admin permissions empower the Arc admin or Server admin with the ability to change the ARM path of the given server. Given that mappings in Microsoft Purview use ARM paths, this can lead to wrong policy enforcements.
  • SQL Admin (DBA) can gain the power of Server admin and can tamper with the cached policies from Microsoft Purview.
  • The recommended configuration is to create a separate App Registration per SQL server instance. This prevents SQL server2 from reading the policies meant for SQL server1, in case a rogue admin in SQL server2 tampers with the ARM path.

Configuration

Register Microsoft Purview as a resource provider in other subscriptions

Execute this step only if the data sources and the Microsoft Purview account are in different subscriptions. Register Microsoft Purview as a resource provider in each subscription where data sources reside by following this guide: Register resource provider.

The Microsoft Purview resource provider is:

Microsoft.Purview

Configure permissions for policy management actions

This section discusses the permissions needed to:

  • Make a data resource available for Data Use Management. This step is needed before a policy can be created in Microsoft Purview for that resource.
  • Author and publish policies in Microsoft Purview.

Important

Currently, Microsoft Purview roles related to policy operations must be configured at root collection level.

Permissions to make a data resource available for Data Use Management

To enable the Data Use Management (DUM) toggle for a data source, resource group, or subscription, the same user needs to have both certain IAM privileges on the resource and certain Microsoft Purview privileges.

  1. User needs to have either one of the following IAM role combinations on the resource’s ARM path or any parent of it (using inheritance).

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

    Follow this guide to configure Azure RBAC role permissions. The following screenshot shows how to access the Access Control section in Azure portal experience for the data resource to add a role assignment:

  1. In addition, the same user needs to have Microsoft Purview Data source administrator (DSA) role at the root collection level. See the guide on managing Microsoft Purview role assignments. The following screenshot shows how to assign Data Source Admin at root collection level:

Permissions for policy authoring and publishing

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

  • Policy authors role can create or edit policies.
  • Data source administrator role can publish a policy.

Check the section on managing Microsoft Purview role assignments in this guide.

Note

Known issues related to permissions

  • In addition to Microsoft Purview Policy authors role, user may need Directory Reader permission in Azure Active Directory to create data owner policy. This is a common permission for users in an Azure tenant. You can check permissions for Azure AD Directory Reader

Delegation of access control responsibility to Microsoft Purview

Warning

  • IAM Owner role for a data source can be inherited from parent resource group, subscription or subscription Management Group.
  • Once a resource has been enabled for Data Use Management, any Microsoft Purview root-collection policy author will be able to create access policies against it, and any Microsoft Purview root-collection Data source admin will be able to publish those policies at any point afterwards.
  • Any Microsoft Purview root Collection admin can assign new root-collection Data Source Admin and Policy author roles.
  • If the Microsoft Purview account is deleted then any published policies will stop being enforced within an amount of time that is dependent on the specific data source. This can have implications both on security and data access availability.

With these warnings in mind, here are some suggested best practices for permissions:

  • Minimize the number of people that hold Microsoft Purview root Collection admin, root Data Source Admin or root Policy author roles.
  • To ensure check and balances, assign the Microsoft Purview Policy author and Data source admin roles to different people in the organization. With this, before a data policy takes effect, a second person (the Data source admin) must review it and explicitly approve it by publishing it.
  • A Microsoft Purview account can be deleted by Contributor and Owner roles in IAM. You can check these permissions by navigating 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 ARM locks.

Important

You can assign the data source side permission (i.e., IAM Owner) only by entering Azure portal through this special link. Alternatively, you can configure this permission at the parent resource group level so that it gets inherited by the “SQL Server – Azure Arc” data source.

SQL Server on Azure Arc-enabled server configuration

This section describes the steps to configure the SQL Server on Azure Arc to use Microsoft Purview.

  1. Sign in to Azure portal with a special link that contains feature flags to list SQL Servers on Azure Arc

  2. Navigate to a SQL Server you want to configure

  3. Navigate to Azure Active Directory feature on the left pane

  4. Verify that Azure Active Directory Authentication is configured. This means that all these have been entered: an admin login, a SQL Server service certificate, and a SQL Server app registration.

  5. Scroll down to set External Policy Based Authorization to enabled

  6. Enter Microsoft Purview Endpoint in the format https://<purview-account-name>.purview.azure.com. You can see the names of Microsoft Purview accounts in your tenant through this link. Optionally, you can confirm the endpoint by navigating to the Microsoft Purview account, then to the Properties section on the left menu and scrolling down until you see “Scan endpoint”. The full endpoint path will be the one listed without the “/Scan” at the end.

  7. Make a note of the App registration ID, as you will need it when you register and enable this data source for Data Use Management in Microsoft Purview.

  8. Select the Save button to save the configuration.

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

  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]
    (./how-to-enable-data-use-management.md)

  7. Upon enabling Data Use Management, Microsoft Purview will automatically capture the Application ID of the App Registration related to this Arc-enabled SQL server. Come back to this screen and hit the refresh button on the side of it to refresh, in case the association between the 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.

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 one of the examples shown in the images.

Example #1: SQL Performance Monitor policy. This policy assigns the Azure AD principal ‘Christie Cline’ to the SQL Performance monitoring action, in the scope of Arc-enabled SQL server DESKTOP-xxx. This policy has also been published to that server. Note: Policies related to this action are not supported below server level.

Example #2: SQL Security Auditor policy. Similar to example 1, but choose the SQL Security auditing action (instead of SQL Performance monitoring), when authoring the policy. Note: Policies related to this action are not supported below server level.

Example #3: 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: Policies related to this action are not supported below server level.

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.

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

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
SQL Performance Monitor Microsoft.Sql/sqlservers/Connect
Microsoft.Sql/sqlservers/databases/Connect
Microsoft.Sql/sqlservers/databases/SystemViewsAndFunctions/DatabasePerformanceState/rows/select
Microsoft.Sql/sqlservers/databases/SystemViewsAndFunctions/ServerPerformanceState/rows/select
SQL Security Auditor Microsoft.Sql/sqlservers/Connect
Microsoft.Sql/sqlservers/databases/Connect
Microsoft.Sql/sqlservers/SystemViewsAndFunctions/ServerSecurityState/rows/select
Microsoft.Sql/sqlservers/databases/SystemViewsAndFunctions/DatabaseSecurityState/rows/select
Microsoft.Sql/sqlservers/SystemViewsAndFunctions/ServerSecurityMetadata/rows/select
Microsoft.Sql/sqlservers/databases/SystemViewsAndFunctions/DatabaseSecurityMetadata/rows/select

Next steps

Check blog, demo and related how-to guides

Source Article

Next Post

Microsoft.CostManagement/costAllocationRules - Bicep, ARM template & Terraform AzAPI reference

Mon Sep 26 , 2022
Table of Contents0.1 In this article1 Bicep resource definition2 Resource format3 Property values3.1 costAllocationRules3.2 CostAllocationRuleProperties3.3 CostAllocationRuleDetails3.4 SourceCostAllocationResource3.5 TargetCostAllocationResource3.6 CostAllocationProportion4 ARM template resource definition5 Resource format6 Property values6.1 costAllocationRules6.2 CostAllocationRuleProperties6.3 CostAllocationRuleDetails6.4 SourceCostAllocationResource6.5 TargetCostAllocationResource6.6 CostAllocationProportion7 Terraform (AzAPI provider) resource definition8 Resource format9 Property values9.1 costAllocationRules9.2 CostAllocationRuleProperties9.3 CostAllocationRuleDetails9.4 SourceCostAllocationResource9.5 TargetCostAllocationResource9.6 CostAllocationProportion […]
Exit mobile version