In this article you will find an overview of all the new metadata that was added to support customers using external data and access Policies from Microsoft Purview. You will find this interesting if you are in a technical role and have access to a SQL database, for example as a DBA or developer or need to create reports on who has access to your systems.
Background
If you are one of the early adopters of the new Microsoft Purview based data and access policies (a.k.a. external policies) you are already familiar with what it does and how it works.
For all others I recommend looking at the following articles:
Private Preview: controlling access to Azure SQL at scale with policies in Purview
Microsoft Purview DevOps policies enter GA: Simplify access provisioning while protecting your data
In this article I provide some background on the architecture of the Purview Integration and what “actions” in the context of policy based access control mean compared to SQL permissions: Revamped SQL Permission system for Principle of Least Privilege and external policies – internals
With these fundamentals established let’s take a look under the hood of SQL Server and what it can tell us about active external policies.
How to check if External Policies are enabled
The first and most basic question is: “does my SQL Server currently support external policies at all?” or, in other words, “is this functionality activated and could policies that are defined in Microsoft Purview grant access to anyone on this server?”
There are 2 ways to do that, one in T-SQL and one via Powershell.
In T-SQL we have the function SERVERPROPERTY() with a new parameter: “IsExternalGovernanceEnabled”.
This is how it’s queried:
SELECT SERVERPROPERTY('IsExternalGovernanceEnabled')
If the result is “1” that means this server is currently listening for external policies. It does not mean there are actual policies in place that address this server, but if there are, they would be effective.
Result “0”, the default simply means this Server is not enabled for this feature and would not download any policies even if policies have been defined for this server.
Note on Preview-version
In the Azure SQL Database where this feature currently is in Preview, the parameter is called “IsExternalPolicybasedAuthorizationEnabled” – so you need to use SERVERPROPERTY('IsExternalPolicybasedAuthorizationEnabled')
Below this article I have listed all object names that are used in the Preview version.
In PowerShell you can use the following command:
Invoke-AzRestMethod -Method GET -Path "$($server.ResourceId)/externalPolicyBasedAuthorizations/MicrosoftPurview?api-version=2021-11-01-preview"
The parameter $server requires your Azure SQL Database’s logical server name.
It will return true or false, following the same logic as above, meaning that “true” means the feature is enabled.
There is one other way to determine if your server has this feature enabled and that is using a new DMV named sys.dm_external_policy_cache.
This DMV will always contain one row if the server has the feature enabled.
If the DMV returns an empty resultset, in other words zero rows, the feature is not enabled:
SELECT *
FROM sys.dm_external_policy_cache
A server with this feature enabled would have a resultset like the following:
That leads us to another topic:
Are the policies on my server the most current?
Policies that are defined and published in Purview are downloaded periodically, not continuously. On average it will take about 2-3 minutes for a new policy to appear in SQL.
The DMV sys.dm_external_policy_cache that I introduced will let you know the last time in UTC that the SQL engine checked and, if necessary, downloaded all policies that are applicable to this server.
It also tells you the number of policies that have been downloaded and by that currently apply to this server.
How to ensure policies are fresh during troubleshooting
Since it can take a few minutes for freshly published policies to be downloaded to the SQL Server, to help speed up troubleshooting and testing, we provide you a system stored procedure that forces the engine to download any new policies when run: sys.sp_external_policy_refresh (BOL: sp_external_policy_refresh (Transact-SQL))
It updates the Purview cache on the whole instance, for every database.
EXEC sys.sp_external_policy_refresh @type = 'Reload' -- aka FULL
“Reload” forces the engine to flush and “Reload” the policy cache entirely.
Note
If there are any ongoing pull requests, by the background task or by another user, the requested pull waits until the former task is finished and starts a new pull. This ensures that the result of calling this procedure explicitly always results in a fresh pull (the former may have been requested many seconds earlier and thus not contain what the user expects).
How to determine who can do what: allowed operations
Now that you are sure that you have the latest version of all policies applied at your server, you want to understand which AAD Accounts have access via policies and which activities they can perform.
Note
External policies use so-called Actions to define which operations the policy allows to perform. The difference between Actions and permissions are explained in this post: Revamped SQL Permission system for Principle of Least Privilege and external policies – internals
Policies are stored in memory using JSON format. This data is not exposed at this time.
However, to facilitate determining who has access and can do what, policies are parsed and made available in relational format through a set of DMVs.
All these new DMVs come in pairs of two: one DMV that contains policies that apply to the whole server and one that only shows policies that apply to the current database.
sys.dm_server_external_policy_actions and sys.dm_database_external_policy_actions
List all available Data actions, independently of them being used or not (basically static, returns what could be used)
The result will look like this:
sys.dm_server_external_policy_principals and sys.dm_database_external_policy_principals
Contain a list of all principals (all types of AAD Accounts) that have at least the Connect-Action assigned to them.
Concerning the SID
Currently the external_policy_DMVs only return SIDs not the User names from AAD. And there are 2 formats in which we expose them:
- The column sid: it contains the SID in the format that SQL Server uses and can be used to join with for example sys.dm_exec_sessions and other objects that return Windows or AAD Login SIDs.
- The column aad_object_id: this column contains the SID that is used by Azure Active Directory for the Object ID. The reason is that this is the format that Microsoft Purview policies use internally.
You can see the Object ID belonging to each account in the Azure portal for example here:
sys.dm_server_external_policy_roles and sys.dm_database_external_policy_roles
All external roles (aka Action Groups in Purview) that are in use for the current scope (server or database)
sys.dm_server_external_policy_role_members and sys.dm_database_external_policy_role_members
Link external_principals with external_roles -> can be used to join those DMVs
This DMV also contains the scope at which the role is applied. Remember that your server may reside in some Resource Group in Azure where the policies are applied, instead of policies being applied on a per-server basis (that would usually be a bad design choice).
sys.dm_server_external_policy_role_actions and sys.dm_database_external_policy_role_actions
Link external_actions with external_roles -> can be used to join those DMVs
How to query all at once:
To get a useful, informative result, you will want to join those views. To spare you that work, we have created one more set of DMVs that do that for you:
sys.dm_server_external_policy_principal_assigned_actions and sys.dm_database_external_policy_principal_assigned_actions
This set of views joins all 5 views that I explained above and as a result lists the AAD accounts (currently just the SIDs) together with the external roles they are assigned to and the data actions that those roles provide.
The result can look like this:
Now you can see from one query, which AAD Account (the SIDs) has which Actions granted to it and which role this is coming from and on what level of the Resource hierarchy this role is applied to.
How to detect Users connecting via policy-based permissions
Often, as a DBA or Auditor, you will want to understand how a certain User that you see in the system was able to connect and what its permissions are.
In this case you are most likely looking at either sys.dm_exec_sessions (sys.dm_exec_sessions (Transact-SQL) ), often joined with sys.dm_exec_requests ( sys.dm_exec_requests (Transact-SQL) ), both being DMVs that exist since a long time before external policies came into play.
Since policy-backed accounts are not persisted in SQL Server Catalog views like sys.server_principals or sys.database_principals, you would now start using sys.dm_database_external_policy_principals and sys.dm_server_external_policy_principals to make sure to find these accounts. And starting from there you can find out which Actions they have been assigned – using the DMVs that I explained above.
To join sys.dm_exec_sessions with the new external_policy-DMVs you can use the column security_id (aka SID).
In the following screenshot you can see all currently logged in AAD Accounts that have a Policy assigned to them as indicated by the “RBAC_ASSIGNED_USER” in the type-field.
Of course you will still also enumerate all accounts that may exist as traditional “External Login from Azure Active Directory” using sys.server_principals.
It is quite possible that an AAD Account has a Login in SQL in addition to a Policy assigned to it. So don’t be surprised to find entries in both the new external_policy- and the old server_principals system views.
Here is how that could look:
As you can see marked in red boxes, the sessions 54 and 55 are listed twice, since I am joining with both DMVs and find an entry for both a Login based on AAD (EXTERNAL_LOGIN) and the same account also is assigned to a Policy, hence “RBAC_ASSIGNED_USER” as the metadata calls it.
This is logical and important, since the traditional Login (and potential Database-Users) can have additional permissions granted directly in SQL.
How to check for possible performance-impact on the system or queries
Lastly, once we understand that hopefully all access makes sense, there are cases where you will want to understand which possible impact the policy cache, the updates and calls to it may have.
There are 3 areas to look at.
DMVs
Besides sys.dm_external_policy_cache which I explained above, you can monitor the Memory Clerk that the Purview SDK uses through the existing sys.dm_os_memory_clerks.
That will show you the amount of memory used by the SDK. You can find the Memory clerk with this query:
SELECT pages_kb/1024.0 AS [SizeMB], *
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_EXTERNAL_GOVERNANCE_AUTHORIZATION_ENGINE'
Note on Preview-version
In the Preview-version and currently in Azure SQL Database, this Clerk-type is named “MemoryClerk RBAC” for historical reasons. Below this article I have listed all object names that are used in the Preview version.
Wait-Types
Analyzing Wait-Stats is an essential part of system-investigation. Waits are a normal part of any system but depending on the operation that the system is waiting for, abnormal high waits can point to underlying issues.
Server-wide waits are queried via sys.dm_os_wait_stats and in Azure SQL Database using sys.dm_db_wait_stats or on a session-level via sys.dm_exec_session_wait_stats. If you are not familiar with this concept, start here: sys.dm_os_wait_stats (Transact-SQL) | Microsoft Learn
The following Wait-types have been introduced in the preview version of Purview integration:
|
Wait Type |
Description |
|
WAIT_EXTERNAL_GOVERNANCE_PERMCACHE_RESOURCELOCK |
Occurs during a read/write lock on the permission cache, used for the response from the Purview SDK on a permission-lookup. |
|
WAIT_EXTERNAL_GOVERNANCE_PERMCACHE_DECISIONLOCK |
Occurs during a read/write lock on the permission cache, used for the response from the Purview SDK during evaluation of an AAD principal for a data action. |
|
WAIT_EXTERNAL_GOVERNANCE_POLICY_AAD_GROUP_INFO |
Occurs during waiting on internal lock on a map for caching AAD Group IDs for AAD Users. |
|
PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING |
Occurs during calls to Purview SDK. Scheduling is switched to preemptive mode. It will occur after PWAIT_PREEMPTIVE_OS_AUTHENTICATEDWEBCALL. |
|
WAIT_EXTERNAL_GOVERNANCE_POLICY_PROVIDER |
Occurs during a read/write lock on the policy provider cache while accessing the stored policies in memory. |
|
EXTERNAL_GOVERNANCE_POLICY_UPDATE |
Occurs during updates of the policy cache on a pull task run. |
|
WAIT_EXTERNAL_GOVERNANCE_POLICY_SESSION_AUDIT |
Lock to synchronize access to an active session’s audit information containing audited external permission evaluation and their outcome together with applicable policy id and version. |
|
WAIT_EXTERNAL_GOVERNANCE_POLICY_SESSIONS_AUDIT |
Lock to synchronize allocation of, or access to Purview permissions audit information object for a session. |
|
EXTERNAL_GOVERNANCE_PULL_TASK |
Occurs if there is a background pull task running and a user manually initiates a pull at the same time. This wait type shows up until the background task is done, before the user initiated one can be started. |
Therefore, a query to retrieve just those could look like:
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type IN
( 'WAIT_EXTERNAL_GOVERNANCE_PERMCACHE_RESOURCELOCK'
, 'EXTERNAL_GOVERNANCE_PULL_TASK'
, 'EXTERNAL_GOVERNANCE_ATTR_SYNC_BACKGROUND'
, 'PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING'
, 'WAIT_EXTERNAL_GOVERNANCE_POLICY_AAD_GROUP_INFO'
, 'WAIT_EXTERNAL_GOVERNANCE_PERMCACHE_RESOURCELOCK'
, 'WAIT_EXTERNAL_GOVERNANCE_PERMCACHE_DECISIONLOCK'
, 'WAIT_EXTERNAL_GOVERNANCE_POLICY_PROVIDER'
, 'WAIT_EXTERNAL_GOVERNANCE_POLICY_SESSION_AUDIT'
, 'WAIT_EXTERNAL_GOVERNANCE_POLICY_SESSIONS_AUDIT'
);
From those, the one that will most likely be on top always, is PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING. Since this happens in a background-process (unless you force the cache update manually), this should normally not be an issue. On my server I have a max wait time of 15 ms over multiple weeks.
Extended Events
All the information from DMVs is looking at data at a certain point in time.
So for example you can get the timestamp of the last time that the cache was updated, and the wait-time accumulated until when your query is running.
But sometimes you will want to run a so-called trace, to log specific events over a certain timespan. That’s where Extended Events aka XEvents come into play. To assist you in tracing policy related information, we created the following new events that can be captured:
|
XEvent and Description |
Event Fields |
|
external_governance_policy_pulltask_started *
XEvent for policy pull start |
policy_pull_type - FULL = Reload, DELTA = Update service_endpoint_uri |
|
external_governance_policy_pulltask_retries *
XEvent for policy pull diagnostics |
Message - Internal status message retry_number |
|
external_governance_policy_pulltask_finished *
XEvent for policy pull finish |
modified_policy_elements_information
- these fields are only useful for internal Microsoft engineers |
|
external_governance_call_to_authorization_engine *
a call to external governance SDK is made |
database_id - self-explanatory
data_action - internally used ID for Data action - i.e. 0 = Connect on Database, 1 = Connect on Server external_governance_decision - 0 = Permit, 1 = Deny |
All these events from external governance tasks are under the Debug-channel. Therefore, if you are searching for these events in the UI, you need to activate that channel to see them – as shown in the below screenshot:
In addition to new events we also include a new field in the process_login_finished–event to help you understand whether a connection was allowed or denied due to policy-based permissions.
|
Existing event |
New Event Field |
|
process_login_finish |
external_governance_policy_authorization
- this returns one of 3 values, depending on if the session that logged in was authenticated via an external policy Connect-Action: 0 = NotApplicable, 1 = Permit or 2 = Deny |
To help you get started I am also attaching a file that contains an XEvent session including all of these events to this article.
Here is an example result of tracing a single policy-update call with XEvents:
Marked in red you can see the Pull type = “Full” which corresponds to “Reload” and the Endpoint URI of the Purview Service used by my server.
The green box marks the events that encompass the actual “pulling” of policies from the Purview service. The events are external_governance_policy_pulltask_started and external_governance_policy_pulltask_finished. The events before are part of the initialization process and those after cover the updates to the database-specific caches.
Note
In the example I am using the RingBuffer-Target for XEvents which stores the traced events in memory. That is sufficient for a low-impact ad-hoc analysis. For longer running traces you should work with the file-target.
XEvents are stored in XML format, so to present it in tabular format I am using the nodes-Method for the XML data type.
Further reading
XEvents overview - SQL Server, Azure SQL Database, and Azure SQL Managed Instance | Microsoft Learn
nodes() Method (xml Data Type) | Microsoft Learn)
Hopefully this is useful.
As this is a new functionality, the security team is very interested in feedback concerning usability and visibility and what we can do to make your life as DBA, Auditor or Developer easier. You can reach out to me using my Firstname dot LastName At Microsoft.com
Happy securing
Andreas
Thank you to the following people for help with this article:
Vlad Rodriguez (Senior Program Manager Purview), Joshua Glassmyer (Program Manager Azure SQL Security), Jakub Szymaszek (Principal Program Manager Azure SQL Security), Srdan Bozovic (Senior Program Manager Azure SQL Security), Sailesh Duvvuri (Senior Software Engineer Purview)
Appendix: Preview release names
Memory Clerk in Azure SQL Database Preview release:
SELECT pages_kb/1024.0 AS [SizeMB], *
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_RBAC'
Wait-Type names in Azure SQL Database Preview release:
|
Wait Type |
Description |
|
WAIT_EXTGOV_PERMCACHE_RESOURCELOCK |
Occurs during a read/write lock on the permission cache, used for the response from the Purview SDK on a permission-lookup. |
|
WAIT_EXTGOV_PERMCACHE_DECISIONLOCK |
Occurs during a read/write lock on the permission cache, used for the response from the Purview SDK during evaluation of an AAD principal for a data action. |
|
RBAC_AAD_GROUP_INFO |
Occurs during waiting on internal lock on a map for caching AAD Group IDs for AAD Users. |
|
PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING |
Occurs during calls to Purview SDK. Scheduling is switched to preemptive mode. It will occur after PWAIT_PREEMPTIVE_OS_AUTHENTICATEDWEBCALL. |
|
WAIT_RBAC_PROVIDER |
Occurs during a read/write lock on the policy provider cache while accessing the stored policies in memory. |
|
BABYLON_POLICY_UPDATE |
Occurs during updates of the policy cache on a pull task run. |
|
WAIT_RBAC_SESSION_AUDIT |
Lock to synchronize access to an active session’s audit information containing audited external permission evaluation and their outcome together with applicable policy id and version. |
|
WAIT_RBAC_SESSIONS_AUDIT |
Lock to synchronize allocation of, or access to Purview permissions audit information object for a session. |
|
BABYLON_PULL_TASK |
Occurs if there is a background pull task running and a user manually initiates a pull at the same time. This wait type shows up until the background task is done, before the user initiated one can be started. |
Matching SQL query:
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type IN
( 'WAIT_EXTGOV_PERMCACHE_RESOURCELOCK'
, 'WAIT_EXTGOV_PERMCACHE_DECISIONLOCK'
, 'RBAC_AAD_GROUP_INFO'
, 'PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING'
, 'WAIT_RBAC_SESSION_AUDIT'
, 'WAIT_RBAC_SESSIONS_AUDIT'
, 'WAIT_RBAC_PROVIDER'
, 'BABYLON_POLICY_UPDATE'
);
XEvent names in Azure SQL Database Preview release:
|
Xevent and Description |
|
external_policy_pulltask_started
XEvent for policy pull start |
|
external_policy_pulltask_retries
XEvent for policy pull diagnostics |
|
external_policy_pulltask_finished
XEvent for policy pull finish |
|
security_external_governance_call a call to external governance SDK is made |