cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

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
Highlighted

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
Highlighted
Frequent Visitor

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.

Highlighted

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".
Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Users online (9,504)