Provision access by data owner for SQL Server on Azure Arc-enabled servers (preview)
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. Read is only supported for policies at server level. Modify is not supported at this point.
- An Azure account with an active subscription. Create an account for free.
- Create a new, or use an existing Microsoft Purview account. You can follow our quick-start guide to create one.
- Create a new, or use an existing resource group, and place new data sources under it. Follow this guide to create a new resource group.
- SQL server version 2022 CTP 2.0 or later running on Windows. Follow this link
- Complete process to onboard that SQL server with Azure Arc and enable Azure AD Authentication. Follow this guide to learn how.
Enforcement of policies for this data source is available only in the following regions for Microsoft Purview
- East US
- UK South
- Australia East
- 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.
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:
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.
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.
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:
- 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.
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
- 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.
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.
Sign in to Azure portal with a special link that contains feature flags to list SQL Servers on Azure Arc
Navigate to a SQL Server you want to configure
Navigate to Azure Active Directory feature on the left pane
Verify that Azure Active Directory Authentication is configured and scroll down.
Set External Policy Based Authorization to enabled
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.
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.
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.
Sign in to Microsoft Purview Studio.
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
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.
select an Azure subscription, Server name and Server endpoint.
Select a collection to put this registration in.
Turn the switch Data Use Management to Enabled. This switch enables the access-policies to be used with the given Arc-enabled SQL server. Note: Data Use Management can affect the security of your data, as it delegates to certain Microsoft Purview roles managing access to the data sources. Secure practices related to Data Use Management are described in this guide: registering a data resource for Data Use Management
Enter the Application ID from the App Registration related to this Arc-enabled SQL server.
Select Register or Apply at the bottom
Once your data source has the Data Use Management toggle Enabled, it will look like this picture.
- If you want to create a policy on a resource group or subscription and have it enforced in Arc-enabled SQL servers, you will need to also register those servers independently for Data use management to provide their App ID. See this document on how to create policies at resource group or subscription level: Enable Microsoft Purview data owner policies on all data sources in a subscription or a resource group.
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 role, in the scope of Arc-enabled SQL server DESKTOP-xxx. This policy has also been published to that server.
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.
Example #3: Read policy. This policy assigns the Azure AD principal 'sg-Finance' to the SQL Data reader role, in the scope of SQL server DESKTOP-xxx. This policy has also been published to that server.
- 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”.
- Publish is a background operation. It can take up to 4 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
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|
|SQL Performance Monitor||Microsoft.Sql/sqlservers/Connect|
|SQL Security Auditor||Microsoft.Sql/sqlservers/Connect|
Check blog, demo and related how-to guides
- Demo of access policy for Azure Storage
- Concepts for Microsoft Purview data owner policies
- Blog: Private preview: controlling access to Azure SQL at scale with policies in Purview
- Enable Microsoft Purview data owner policies on all data sources in a subscription or a resource group
- Enable Microsoft Purview data owner policies on an Azure SQL DB
Submit and view feedback for