cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ben-thompson Impactful Individual
Impactful Individual

SQL Data connection and plugins

I'm posting this here as I really can't think of a more appropriate place. If there is a better one please move it there.

 

Having finally got access to an environment with the SQL Data Connection I ran a very simple test last night with a pre-operation plugin running on retrieve multiple and discovered that the SQL connection path is currently bypassing plugins and returning the complete dataset rather than the empty dataset the modified fetchxml statement should return.

 

Is this by design or is it bypassing plugins at the moment because it's a preview feature?

 

Label is client SDK as it's the most appropriate from a bad set of options.

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".
1 ACCEPTED SOLUTION

Accepted Solutions
ben-thompson Impactful Individual
Impactful Individual

Re: SQL Data connection and plugins

To close this off - I've had two confirmations (one from Charles Lamanna and one on github see https://github.com/MicrosoftDocs/powerapps-docs/issues/1403#issuecomment-629857023) that the current process path (bypassing plugins) is temporary and that there (may be) a private preview with plugin support.

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

View solution in original post

3 REPLIES 3
ryanspain
Frequent Visitor

Re: SQL Data connection and plugins

Good observation.

 

IMO, I believe this is by design. The SQL endpoint is just for read-only access which probably boasts some performance improvement. I think the idea here is to bypass the most of the normal execution pipeline (security model is still enforced).

 

Reminds me somewhat of a CQRS pattern where we have a separate query mechanism (SQL) and the commands can be taken care of by the Web API via the SDK.

ben-thompson Impactful Individual
Impactful Individual

Re: SQL Data connection and plugins

For the TSQL side of things I might support that argument but if this is the basis of the PowerBi reporting then it's not a valid argument unless MS expect users to be happy with records appearing / disappearing as they move from a dashboard to a view.

 

As an aside I can think of at least 3 large (2000-20000 seat) companies who will be annoyed by this issue.

 

Equally if you look at the original announcement Charles Lamanna says that it fully supports plugins ( I went and doublechecked this on Friday when Alex Shlega first revealed the issue). It's worth saying that Alex was using a post operation plugin so I used some pre-operation code I wrote previously that's designed to change the query at a minimal cost (it usually adds 30ms to the run time)

 

You then have the weirdness where FetchXML and TSQL are treated equally on Microsoft docs 

 

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/use-fetchxml-construct-quer... (FetchXML)

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query (TSQL)

 

But the fact one path allows plugins and the other does not is not mentioned anywhere.

 

Also I should add that while the built in security model is enforced the fact plugins can be created on retrieve and retrievemultiple means that a lot of clients will over the past 10 years have created custom security solutions to resolve their issues. For instance a common requirement is for Chinese walls to stop particular users accessing a particular subset of records. This can be done in one of two ways (by separating customer details from users and overwhelming the Principal Object Access table) or via custom security (as demonstrated in the few lines of C# I mentioned above that add additional criteria to a fetchxml or queryexpression query)

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".
ben-thompson Impactful Individual
Impactful Individual

Re: SQL Data connection and plugins

To close this off - I've had two confirmations (one from Charles Lamanna and one on github see https://github.com/MicrosoftDocs/powerapps-docs/issues/1403#issuecomment-629857023) that the current process path (bypassing plugins) is temporary and that there (may be) a private preview with plugin support.

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

View solution in original post

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Users online (6,409)