Tutorial: Troubleshoot Microsoft Purview policies for SQL data sources
In this tutorial, you learn how issue SQL commands to inspect the Microsoft Purview policies that have been communicated to the SQL instance, where they'll be enforced. You'll also learn how to force a download of the policies to the SQL instance. These commands are only used for troubleshooting and aren't required during the normal operation of Microsoft Purview policies. These commands require a higher level of privileges in the SQL instance.
For more information about Microsoft Purview policies, see the concept guides listed in the Next steps section.
Prerequisites
- An Azure subscription. If you don't already have one, create a trial subscription.
- A Microsoft Purview account. If you don't have one, see the quickstart for creating a Microsoft Purview account.
- Register a data source, enable Data policy enforcement, and create a policy. To do so, use one of the Microsoft Purview policy guides. To follow along with the examples in this tutorial, you can create a DevOps policy for Azure SQL Database.
Test the policy
Once you create a policy, the Microsoft Entra principals referenced in the Subject of the policy should be able to connect to any database in the server to which the policies are published.
Force policy download
It's 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's 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 Microsoft Entra principals. 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
Next steps
Concept guides for Microsoft Purview access policies: