Troubleshooting external data and access policies in Azure SQL and SQL Server

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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:

AndreasWolter_0-1669245536360.jpeg

 

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:

list of built-in external actionslist of built-in external actions

 

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.

AndreasWolter_2-1669245536455.jpeg

 

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:

  1. 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.
  2. 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:

User account in Azure Active Directory in Azure PortalUser account in Azure Active Directory in Azure Portal

 

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).

AndreasWolter_4-1669245536496.jpeg

 

 

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:

AndreasWolter_5-1669245536520.jpeg

 

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.

AndreasWolter_6-1669245536543.jpeg

 

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:

AndreasWolter_7-1669245536571.jpeg

 

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
policy_elements_count_after_pull

 

-         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
object_name
schema_name

-        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:

AndreasWolter_8-1669245536606.jpeg

 

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:

 

AndreasWolter_9-1669245536661.jpeg

 

 

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

 

 

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.