Custom Report Server security extension and custom DirectQuery based connector

[mtjohnuk]

Hi

We have the following scenario that is being explored (all on-prem based):

• A user logs onto Power BI Report Server using ADFS for authentication/authorization
• A report is made accessible to the logged on user that retrieves its data in realtime (DirectQuery mode) from a custom source e.g. an OData based API
• The custom data source accepts the authentication/authorization details (token/claims) already available (in some way) and uses them to ensure only the relevant data is made visible in the report for that user

The assumptions we are working with at the moment are:

• Create a custom security extension for Report Server to provide ADFS authentication capabilities
• Create a custom connector that is able to pass the authentication/authorization details through to the data source
• Build the data source (Web API/OData feed) so that it filters the data according to the authentication/authorization details provided e.g. token/claims

We are looking for guidance with:

• How to create a custom connector that supports Direct Query to a custom OData source (without an ODBC driver)
• How to build the custom connector so that it can "pass through" the credentials to the data source

Pointers to relevant articles or examples that would help with this would be greatly appreciated.

Most articles already read suggest that building a custom connector that supports DirectQuery can only be done via an ODBC driver...is that still the case or has anyone already achieved this...?

CData provides connectors that support DirectQuery capabilities, however these do not appear to support passthrough of authentication/authorization details to the underlying data source.

Thanks in advance for any help provided.

[v-rzhou-msft]

Hi @mtjohnuk 

You need to install Power Query SDK to create the custom connector by Visual Studio.

Installing the Power Query SDK

I think you need to install an ODBC driver. From the blog: What can you do with a custom connector?

• Enabling DirectQuery for a data source using an ODBC driver.

You need an ODBC driver to enable DirectQuery for a data source.

Here is the code of DirectQuery connector, you make take a look.

For reference: Example: Connector with required parameters

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mtjohnuk]

Hi Rico and thankyou for your response.

We have been able to use the Power Query SDK to build a very basic custome connector, however we need it to support DirectQuery mode without using an ODBC driver. We have found a GitHub issue thread that suggests it can be done (see below) but we're looking for a suitable full example.

Documentation for Table.View Expression Handlers · Issue #246 · microsoft/DataConnectors (github.com...