Solved: How to access SQL data on secure server

KogerMD · ‎11-22-2022

For security reasons, our end users do not have direct access to our SQL server, so they are not able to create data connections in Power BI Desktop that pull data directly from SQL. With SSRS, we were mostly able to get around this through the use of Shared DataSets, but this concept doesn't exist in Power BI Report Server (although I see that it has been requested as a new feature). We currently use a combination of SSRS Shared DataSets and connect to them over OData from PBIRS to allow our users the ability to create reports that pull data directly from the SQL server. The problem with this is that the SSRS REST API implementation doesn't support the standard OData methods such as top, select, filter, etc, so they are very slow when there is a lot of data being pulled in.

Is there a better solution that we can use that will allow our users to report on the SQL data without having to give them direct access to the SQL servers?

d_gosbell · ‎11-22-2022

@KogerMD wrote:

With SSRS, we were mostly able to get around this through the use of Shared DataSets, but this concept doesn't exist in Power BI Report Server (although I see that it has been requested as a new feature).

SSRS Shared datasets do exist in Power BI Report Server (since it is built on top of SSRS). What does not exist yet in PBIRS is the ability to share datasets from pbix reports.

@KogerMD wrote:

We currently use a combination of SSRS Shared DataSets and connect to them over OData from PBIRS to allow our users the ability to create reports that pull data directly from the SQL server. The problem with this is that the SSRS REST API implementation doesn't support the standard OData methods such as top, select, filter, etc, so they are very slow when there is a lot of data being pulled in.

This should still work, but with the same limitations. The shared datasets have no way of efficiently implementing top, select, filter etc as they can be based on things like stored procs or file based data sources so there is no way to consistently "fold down" these operations to the underlying data source.

@KogerMD wrote:

Is there a better solution that we can use that will allow our users to report on the SQL data without having to give them direct access to the SQL servers?

One other option is to create a set of one or more tabular models hosted on a local SSAS instance. Then you can have the users report off those instead of hitting your SQL servers directly.

View solution in original post

d_gosbell · ‎11-22-2022

@KogerMD wrote:

With SSRS, we were mostly able to get around this through the use of Shared DataSets, but this concept doesn't exist in Power BI Report Server (although I see that it has been requested as a new feature).

SSRS Shared datasets do exist in Power BI Report Server (since it is built on top of SSRS). What does not exist yet in PBIRS is the ability to share datasets from pbix reports.

@KogerMD wrote:

We currently use a combination of SSRS Shared DataSets and connect to them over OData from PBIRS to allow our users the ability to create reports that pull data directly from the SQL server. The problem with this is that the SSRS REST API implementation doesn't support the standard OData methods such as top, select, filter, etc, so they are very slow when there is a lot of data being pulled in.

This should still work, but with the same limitations. The shared datasets have no way of efficiently implementing top, select, filter etc as they can be based on things like stored procs or file based data sources so there is no way to consistently "fold down" these operations to the underlying data source.

@KogerMD wrote:

Is there a better solution that we can use that will allow our users to report on the SQL data without having to give them direct access to the SQL servers?

One other option is to create a set of one or more tabular models hosted on a local SSAS instance. Then you can have the users report off those instead of hitting your SQL servers directly.