One of the aspects of the CDS platform I’ve liked since Dynamics CRM 4.0 is the extensibility options – the ability to add plugins into the core of the engine so that your custom code will be executed however someone is using it, whether through the standard UI, portals, external SDK app etc.
Although it already has a very flexible security model, you can always add plugins to the Retrieve and RetrieveMultiple messages to further log or filter the data that a user has access to.
If the T-SQL endpoint is a view onto CDS rather than a direct path to the SQL database then these plugins should still be fired. Alas, they’re not. No matter how I formulate the query or configure the plugin step it just doesn’t fire.
This makes some sense – you can’t translate all the possible SQL you might fire at it into any of the existing query types that the plugin might be able to get out of the parameters. I did wonder if there might be a new QueryBase-derived class to represent the SQL queries, but apparently not.
So, beware if Retrieve or RetrieveMultiple plugins form a core part of your security or business logic, as users could use T-SQL to bypass them!
So maybe there’s an entirely different SDK message processing step that I need to register my plugin on instead?
There’s nothing obvious listed when I type into the “Message” field in the plugin registration tool, so I decided to compare the list of available messages from my test instance to an old on-premise system to find what messages have been added. I ran SELECT name FROM sdkmessage against both systems and compared them in Excel. There’s a lot of extra messages online compared to on-premise, but one in particular stood out:
ExecutePowerBISql
This is almost certainly the one, given that the main purpose of this endpoint was to support running reports in Power BI.
Unfortunately the Plugin Registration Tool doesn’t offer the option of adding a step against this message. I’ve also tried adding one directly via the SDK and it gives the error:
Custom SdkMessageProcessingStep is not allowed on the specified message and entity.So as far as I can tell the SQL requests do go via the core CDS service, but use a different message than a standard RetrieveMultiple that currently makes it impossible to attach custom plugins to inspect or alter what’s going on.
Other Posts
This is part of a series of posts on the T-SQL endpoint, read more about it: