Power Platform 2021 release wave 1 announced a new Dataverse Virtual Connector Provider feature whereby data residing outside of Microsoft Dataverse can be easily represented and used as Microsoft Dataverse virtual tables using supported tabular connectors (for example, SQL Server, OneDrive, Excel, SharePoint, and more).

At the time of publishing this blog at least this new functionality is still in Public Preview and as such is subject to change, the consequence of which may alter the findings and insight shared in this blog. If so I will update the blog accordingly where necessary.

In this public preview release, Microsoft introduced the virtual connector provider that supports creating virtual tables using the SQL Server connector and will expand and support other tabular connectors as part of this provider in subsequent releases.

I am genuinely excited at some of the opportunities in creating solutions that could potentially leverage the new Dataverse virtual connector provider functionality / features being released. For that reason I’ve spent a substantial amount of effort evaluating this feature and documenting my own learnings by way of this blog as I believe other people equally interested may find at least some of my insight shared extremely useful for them.

Objectives

There are already numerous Microsoft articles, community blogs and YouTube videos published that describe in detail how to implement this new functionality in your own Dataverse environments. The intent of this blog is not to cover topics already well documented, but instead focus content on the following key objectives:

Create awareness for organisations considering implementing virtual tables in their Power Platform solutions using the virtual connector provider with the SQL Server connector.
Highlight security concerns and potential implications thereof that I personally don’t believe Microsoft has (as-yet) adequately communicated in their pre-release documentation.
Supplement Microsoft’s current technical documentation by delving into topics I believe need a lot more prescriptive guidance on.
Introduce new topics that cover end-user experience: in particular manage security and access to your Dataverse virtual tables you create as well as your SQL Server database.
Share some of my own recommended practices / guidance and techniques to considering using to mitigate some of the concerns noted in this blog, in doing so hopefully enabling broader adoption / use of this functionality in your own organisations.

Key Concerns

SQL Server Connection

When creating the SQL Server connection in your environment the only option you have when specifying the authentication type is that you have to use SQL Server Authentication or alternatively potentially Windows Authentication for an on-premises SQL Server database. These authentication types are deemed to be implicit connection types.

Microsoft themselves acknowledge that there are inherit potential risks associated with using a SQL Server connection created using implicit credentials noting that there are valid use cases whereby an implicitly shared connection may be perfectly acceptable and even “encouraged as long as all of the data should be accessible by all users who are given access“.

Conversely when developing enterprise grade solutions that typically require server-side security your SQL Server connection should be implemented to use AAD authentication – i.e. an explicitly shared connection.

The latter approach (i.e. using an explicitly shared connection) as described above is irrelevant insofar as creating Dataverse virtual tables is concerned as the SQL Server connection must be created using an implicit authentication type.

The pre-release documentation incorrectly states:

“The connection can be shared with one user or can be shared with entire organization. This allows users to access and operate on virtual tables using a shared connection.“

In the same article in the detailed steps that subsequently describe how to create the SQL Server connection they clarify that you must in fact share the connection with all the users in your organization, emphasising the reason being:

Sharing the connection with the organization is an important step. This allows for the service principal that was created in this tenant to have access to the connection.

By highlighting the fact that the purpose of sharing the connection is in order that the service principal can access it obscures the fact that equally by sharing the connection to the entire organization anyone with app maker permissions in that environment can equally add data source (table) reference(s) to their apps directly using the SQL Server connection as opposed to using the Dataverse virtual table(s).

“Virtual tables are organization owned and don’t support the row-level Dataverse security concepts. We recommend that you implement your own security model for the external data source.“

“Access to virtual table data can be turned on or off for individual users based on their security role.”

When the previous 2 statements from Microsoft Docs articles are read together, further noting Create / Read / Write / Delete privileges for virtual tables can also be defined in Dataverse security roles, it’s reasonable to deduce this behaviour to be akin to the security model Microsoft have implemented for the SQL Server connection external data source.

However should a data source (table) be added to an app using the SQL Server connection as opposed to adding a virtual table data source, security role permissions and privileges defined in Dataverse do not apply and accordingly should never be relied on.

In essence Microsoft have not followed their own recommendations insofar as implementing a reliable security model in the virtual connector provider (when using with the SQL Server connector at least).

There are however some possible governance measures you can implement to mitigate this finding which are discussed later in this blog.

Potential Implications

Demo Power App

In order to validate many concerns noted earlier are genuine and warrant further consideration when designing your own solutions, I created a demo solution in order to provide factual evidence to support the theoretical concerns I’ve described.

I created a Azure SQL database with a number of tables as well as a new user in SQL Server and granted permissions to a few tables to the new user. In my Power Apps environment I created a new SQL Server connection to this database using SQL Server authentication.

In a new Power Apps solution, following the steps in the Microsoft Docs Create virtual tables using the virtual connector provider (preview), I created a single new Dataverse virtual table associated with a table “Client” in my database.

At this point with everything seemingly working exactly as expected, I created a demo canvas Power App in which I added data source connection to the virtual table in addition to adding a second data source using the shared SQL Server connection to directly connect to the same table in the database.

I implemented some basic functionality including:

Adding 2 data table controls, one of which associated to the Custom Entity [dbo].[Client]s virtual table and the other to the direct Client table data source.
A form control to enable user to add or edit rows using the virtual table data source in addition to a Delete button to remove the selected record from the virtual table data source.

Testing using System Administrator user account

I first tested the app using my System Administrator account to ensure data in the Client table was displayed as expected using both the virtual table data source added to the app as well as when alternatively using the direct / native SQL Sever connection table data source.

At this point I was able to confirm that all operations implemented for the virtual table worked as expected, including the full set of CRUD operations using the virtual table data source. Great!

Testing using non System Administrator user account

Without implementing any additional security role changes, I retested the same Power App with a non System Administrator user. This time when loading the app an exception error message was displayed:

The error message displayed was expected because by default users will not have permissions to new virtual tables added to the environment (as with any custom Dataverse table/entity created). The permissions and CRUD privileges to virtual tables need to be granted to a Security Role assigned to the users of the solution.

Simply granting the security role privileges to the custom virtual table however was insufficient. On re-testing the app with the non System Administrator user a slightly different error message was then displayed:

Having done some troubleshooting it became apparent you also need to grant Read privilege to the Connection Reference custom entity and grant at minimum Business Unit Read privilege. I did not come across any guidance on Microsoft docs that noted this specific security role permission was required for end-users in addition to virtual table specific permissions that need to be granted. As such hopefully in sharing it may well save you some of your own time troubleshooting.

In the public preview release seemingly the specific security privileges granted to the virtual table(s) do not always work as would be expected. For example:

Granting Read privilege to the security role did “work”, thereby enabling data to be displayed (viewed) in the data table in the Power App – assuming as noted above you equally granted Read privilege to the Connection Reference custom entity.
When the user did not have Read privilege on the virtual table an error message was raised and displayed to the user in the Power App.
Granting Create privilege to the security role sort of worked, enabling me to add new rows to the virtual table. However with this privilege (and Read) I was also able to delete rows from the virtual table despite not being assigned Delete privilege. I wasn’t able to edit existing records (expected behaviour).
Granting Write privilege to the security role also sort of worked, enabling me to edit existing rows to the virtual table. However again with only this privilege (and Read) I was also able to delete rows from the virtual table despite not being assigned Delete privilege. I wasn’t able to create new existing records (expected behaviour).

Worth calling out is that whilst expected error messages were displayed in the app when the user had not be given Read privilege on the virtual table being queried and equally when the user had not been given Read privilege on the Connection Reference custom entity, when the user attempted to perform other operations on the virtual table including Create, Write and Delete actions without having the corresponding necessary permissions to the virtual table, expected error messages informing users of insufficient permissions of the virtual table were not displayed in the Power App.

The primary takeaway here is that whilst these observations are by all accounts bugs in the code. I would expect these should be “fixed” by the time it’s released (aka General Availability). Furthermore should you experience similar unexpected issues such as some of the ones I’ve highlighted as least you now know about some of them.

Penetration Test

It is common practice for custom developed solutions to include a penetration test exercise prior to be released to a production environment. Often outsourced, the purpose of a penetration test is to ensure that an application does not expose vulnerabilities in the code by way of monitoring and manipulating network traffic such that malicious users could potential exploit such as access data not intended for their use, possible corrupting data in the database etc.

For the purposes of this blog the objective of conducting a penetration test was to determine what if any vulnerabilities could be identified that “app makers” in “general” considering leveraging the new virtual connector provider for SQL Server virtual tables (in particular) should be conscious of.

For this test I used a free tool – Fiddler Classic – which includes functionality to log, inspect, and alter HTTP(s) network requests and server responses generated from the demo Power App when using Dataverse virtual tables and similarly when alternatively using a data source (table) added to the app using the shared SQL Server connection.

Observations – Virtual connector provider virtual table

In Fiddler it was relatively difficult to identify, subsequently clone and modify the specific HTTP request generated from the Power App for data operations using the Dataverse virtual table data source, but not impossible. When refreshing the Custom Entity [dbo].[Client]s virtual table, the HTTP request sent from the Power App looks along the lines of:

POST https://org999cv999.crm4.dynamics.com/api/data/v9.0/$batch HTTP/1.1
Host: org999cv999.crm4.dynamics.com
Connection: keep-alive
Content-Length: 443
…
Content-Type: application/http
Content-Transfer-Encoding: binary
GET new_dbo_clients?%24select=new_dbo_clientid%2Cnew_externalprimarykey HTTP/1.1

I was able to clone the original HTTP request sent from the Power App and modify the GET header to retrieve all Dataverse tables including all virtual tables in the environment, despite the fact that the user had only been explicitly granted specific privileges to a single virtual table.

GET entities HTTP/1.1

Having identified several other virtual tables the user had not been granted any privileges to, I modified the GET header to assess whether I could possibly query data from another virtual table the user did not have Read privilege on.

GET new_dbo_vendors?%24select=new_dbo_vendorid HTTP/1.1

In line with the expected behaviour, this HTTP request failed returned an error, part thereof:

“@Microsoft.PowerApps.CDS.InnerError.Message=Principal user (Id=[REDACTED]), is missing prvReadnew_dbo_vendor privilege“

Whilst I conducted numerous other network penetration tests targeting the virtual tables created using the Virtual Connector Provider with the SQL Server connection, I did not identify any unexpected behaviours worth calling out other than as noted earlier – i.e. user privileges granted by way of security roles to virtual tables seemingly don’t always work as expected.

As such I was content and satisfied with my findings in this penetration test exercise.

Observations – SQL Server connection – table data source

The difference when using a data source to a Power App using the SQL Server connection and selecting a table to use within the app is that in such cases the user permissions to the data are entirely determined by the permissions that have been granted to the SQL Server user account specified when creating the SQL Server connection (i.e. implicit credentials).

Put differently permissions when directly using the SQL Server connector are in no way linked to security role privileges that may have been defined in Dataverse for virtual tables created using the virtual connector provider.

Using Fiddler one can relatively easily identify the specific HTTP request querying the SQL Server data source (database table) using the azure-apim.net/invoke API:

POST https://mars-999.azure-apim.net/invoke HTTP/1.1
Host: mars-999.azure-apim.net
Connection: keep-alive
Content-Length: 0
x-ms-request-url: /apim/sql/GUID/v2/datasets/mars.database.windows.net,mars_db/tables%255Bdbo%255D.%255BClient%255D/items?%24select=ID%2CClientId%2CClientName&amp;%24top=100

As with Dataverse HTTP requests, the original SQL Server HTTP request sent from the Power App can be cloned and modified so as to retrieve a list of all tables in the SQL Server database for which the SQL Server user account specified in the connection has been granted permissions to (and other database objects for that matter).

x-ms-request-url: /apim/sql/GUID/v2/datasets/mars.database.windows.net,mars_db/tables

For comparative purposes I further modified the HTTP request so as to query the Vendors table instead:

x-ms-request-url: /apim/sql/GUID/v2/datasets/mars.database.windows.net,mars_db/tables/%255Bdbo%255D.%255BVendor%255D/items

Whilst expected, a list of all the records in the Vendors table was returned in the server response. This behaviour is reflective of possible significant vulnerabilities being exposed that in turn could potentially be exploited by malicious users, given the Power Apps user was not granted any privileges by way of a security role(s) on the corresponding virtual table created in the environment.

https://docs.microsoft.com/en-za/powerapps/maker/canvas-apps/connections/sql-server-security#implicit-connection-sharing-risks

Since both the app and its connections are deployed to end users, it means that end users can author new applications based on those connections.

Important

Once an implicitly shared connection is deployed to end users, the restrictions you may have put in the app you shared (such as filters or read-only access) are no longer valid for new apps end users create. The end users will have whatever rights the authentication allows as part of implicitly shared connection.

Per Microsoft own guidance when using the SQL Server connector, they describe an example scenario wherein an app shared with departmental users consumes data from a SQL Server database by way of an implicitly shared connection. They go on to say:

“Departmental based apps can grow into enterprise-wide and mission critical apps. In these scenarios, it’s important to understand that as a departmental app moves to be enterprise-wide, it will need to have traditional enterprise security built in. This approach is more expensive for app building efforts, but important in corporate-wide scenarios.“

The real risk of creating connections using implicit credentials (shared with the entire organization) extends beyond the fact the end users may possibly be able author new apps based on those connections. By way of example as I have demonstrated using Fiddler, there are other methods users may be able to leveraged to surface and possibly modify data in your database should you have created a SQL Server connection specifying implicit authentication credentials in your environment.

This is an extremely important finding in the context of critical solution architecture design decisions you may make when considering the possibility of using the virtual connector provider for SQL Server so as to create virtual tables to use within your solution(s).

Only solutions deemed to be acceptable valid use cases when using an implicitly shared SQL Server connection should equally only be deemed appropriate valid use cases when considering creating virtual tables using the virtual connector provider with an implicitly shared SQL Server connection in your solution(s).

Mitigation Techniques

Power Platform Environment

In my opinion the single most important design decision every user / developer should do is create a new Power Platform environment for solutions that will leverage the virtual provider connector to create virtual tables in particular when using the SQL Server connector. At the very least never use the default environment.

This allows for at least some level of governance wherein only a select few users can be given app maker permissions in the non-default environment.

SQL Server User Account Permissions

Amongst other recommended practices you should always follow especially when creating a SQL Server connection specifying implicit credentials, only grant absolute minimum CRUD permissions to the bare minimum set of tables in the database required for your solution to the connection’s shared user account.

Never grant EXECUTE permissions on any stored procedures to the SQL Server user account as these cannot be invoked directly from a Power App in any case and equally serve no purpose insofar as the virtual provider connector is concerned.

Implement a Business Data Layer for Sensitive Data Operations

For sensitive data operation use cases consider implementing that specific logic in Power Automate flow(s) in combination with SQL Server stored procedure(s) where possible.

In such cases Power Automate acts a business data layer between your Power Apps and your SQL Server database, potentially mitigating several vulnerabilities when alternatively implementing that functionality directly within your apps using the SQL Server connector created specifying implicit credentials.

Should your solution make use of any stored procedures invoked within a Power Automate flow instantiated from an app, consider adding a second SQL Server connection in your environment using a different SQL Server user account granted limited EXUCUTE permissions to only the stored procedures used in Power Automate.

The reason for using a second connection here is because network traffic between Power Automate and the SQL Server (using the connector APIs) in generated in the cloud and as such cannot be monitored and/or manipulated from an end-user device.

Implement Governance Measures to block apps using the SQL Server connection

There are some techniques you could consider implementing that may provide you with some level of governance measures to mitigate the potential risks associated should app makers add SQL Server database table / data source references to any given app in the environment using the SQL Server connection.

Power Automate SQL Server Connecter App Usage flow

The Power Apps Management Connector for Administrators connector provides an action Get Apps as Admin (Get-AdminApps) that returns an array of Power Apps in a specified environment. Amongst other information returned for each Power App is an array of all connection references that may have been added to the app.

This action enables you to identify any apps in your environment wherein an app maker has added a data source connection to an app using the implicitly shared SQL Server connection intended solely for leveraging virtual tables created using the virtual provider connector.

In the event that any app(s) are identified / flagged, a high priority alert can be raised with a recommended remediation action, such as to remove the SQL Server connection data source reference from app in the environment asap and consider using a virtual table reference instead.

Practically this governance measure can be implemented using a scheduled Power Automate flow (daily), notwithstanding alternate techniques can be implemented / leveraged to achieve similar outcomes (e.g. PowerShell scripts, COE Dashboard etc.).

Create a new scheduled Power Automate flow

Add a “Get Apps as Admin” action (“Power Apps for Admin” connector)

Add an “Apply to each” control action

Use the following expression for the "*Select an output from previous steps" input field:
outputs('Get_Apps_as_Admin')?['body/value']

Add a “Condition” control action to check if the app has any Connection References

Enter the following expression for the first comparison input box field:
empty(item()?['properties/connectionReferences'])

For the operator dropdown select "is not equal to".
For final input box enter "true".

Add a “Filter array” action in the “If yes” branch of the Condition action (just added)

Use the following expression for the "*From" input field:
item()?['properties/connectionReferences']

Enter the following expression for the first comparison input box field:
item()?['displayName']

For the operator dropdown select "is equal to".
For final input box enter "SQL Server".

Add a “Condition” control action to check if the app specifically uses the SQL Server Connection (Reference)

Enter the following expression for the first comparison input box field:
length(body('Filter_array'))

For the operator dropdown select "is greater than".
For final input box enter "0".

Add a “Send an email (V2)” action in the “If yes” branch of the Condition action (just added)

Fill in the fields to send an email to alert someone that there is a Power App in the environment using a direct data source connection added to the app using the SQL Server connection that may potentially be exploited.

In the *Body of the email per this example 2 expressions were added to include the name of the related Power App identified as well as the URL to launch/run the app as follows:
item()?['properties/displayName']
item()?['properties/appOpenUri']

Create a Data Loss Prevention policy to block the use of the SQL Server connector

At the time of writing this blog, i.e. with the virtual connector provider still in public preview, I decided to test what would happen should I create a data loss prevention policy in the Power Apps Admin Center so as to block the use of the SQL Server connector in my test environment.

My findings were significantly unexpected and mind boggling insofar as potential repercussions thereof are concerned.

As expected, all Power Apps in the environment wherein data source(s) had been added to tables in the database using the SQL Server connector immediately stopped working with an error message along the lines of:
“It looks like this app isn’t compliant with the latest data loss prevention policies.“
The only way to get those apps working again was to remove the related data sources from each app.
Contrastingly with the virtual tables created in the environment using the virtual connector provider and the SQL Server connector connection, the existing apps using any of the virtual tables worked as they had before creating the policy.
I was able to create new virtual tables using the SQL Server connection, and add those to use in apps.
By all accounts there was no noticeable enforcement of any DLP restrictions insofar as using the virtual connector provider and the SQL Server connection created in the environment.
I was unable to find any method to add the virtual connector provider to the list of blocked connectors in my data loss prevention policy. I personally don’t believe this feature is nor ever will be classified as a connector of sorts – the virtual connector provider extends connectors.

Pros

On the one hand the behaviour observed could be seen as hugely beneficial as most concerns noted in this blog relate to the potential for app makers to add references to tables using the shared SQL Server connection to their apps can be strictly prevented.
Should your solution have requirements for Power Automate flows that may need to use SQL Server actions (such as executing stored procedures), adding a data loss prevention policy blocking the use of the SQL Server connector will equally prevent you from using the connector in Power Automate.
Adding a row to a virtual table that perhaps trigger a Power Automate flow in another environment where the SQL Server connector is not blocked may be a potential workaround, albeit not ideal.

Cons

On the other hand these findings will likely be of significant concern for many organizations that currently have data loss prevention policies that already block the SQL Server connector in some or all of their Power Platform environments.
In such cases those policies can seemingly be bypassed by app makers using the virtual connector provider with a SQL Server connection in an environment to create virtual tables.
I personally deem the behaviour observed as a significant bug / vulnerability that should be a high priority task for Microsoft to remediate soonest.

Wrapping up

By all accounts this blog intentionally delves into a substantial amount of detail for the purposes of sharing my own learnings whilst evaluating the virtual connector provider specifically when creating virtual tables using the SQL Server connector.

I believe the concerns noted and additional topics discussed have not yet been adequately documented by Microsoft. In their defence they clearly state their documentation is pre-release and is subject to change. As such in sharing my learnings hopefully Microsoft will clarify recommended practices and types of use cases suited for this functionality, address some of the concerns noted, fix a number of obvious bugs in their code and provide further guidance wrt configuring end-user security.

I personally wouldn’t have spent the effort I have if I didn’t believe in the potential opportunities this functionality promises to deliver. That said with my own findings (shared) I can’t see this feature being released (General Availability) any time soon.

Posts

Dataverse virtual connector provider for SQL Server. Awesome?