Data Source utilisation without provision of credentials to Designers (MS SQL DB)

[MichaelP2020]

Hi all

I'm fairly new to Power BI and the Power BI Service. I'm trying to determine if there is a solution / workaround to scenario I've outlined below..

Essentially I want to be able to provide access (to Power BI Content Designers) to an MS SQL Database for them to create Reports / Datasets from this Source. In addition, any subsquently published content to the Power BI Service should leverage a 'Service Account' (used / specified by Designers as part of the Dataset setup / deployment) that would be utilised to retrieve content from the Data Source (when Consumers run the associated Report/s). 

The obstacel here, is that ideally, I'd like to avoid sharing the Service Account credentials with Designers.

Thanks

Michael

[v-henryk-mstf · Accepted solution]

Hi @MichaelP2020 ,

I agree with @lbendlin. There is no necessary connection between the publish report and the gateway connection. The role of the gateway refers to the official document:

Best Regards,
Henry

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

[lbendlin]

Datasets contain both the data and the meta data (query definitions and report layout). Templates do not keep data, only meta data.  The credentials are not stored in either file (unless you do some crazy, highly discouraged hard coding).  When a designer is opening a template they are asked to provide their own credentials before they can see the data.

Rethink your business process.

[lbendlin]

That is standard behavior for Power BI Gateway connections. the Gateway connection credentials are independent of the credentials used by the developers during report design. For example the developer might be using their Windows login to access the database, but the gateway connection can use a basic (SQL) login, or a different Windows login.

[MichaelP2020]

Thanks for the resonse @lbendlin. I think the concept I'm struggling with here is how a Designer would consume the Data Source access (i.e. as configured in the Gateway) as part of a report they are designing within Power BI Desktop?

Assuming the Desginer has been granted the access to Publish Reports using the Data Source (as part of the Gateway Setup), are they able to select this as part of their Dataset setup in Power BI Desktop?

Thanks

Michael

[lbendlin]

Being allowed to publish reports is independent of being allowed to use the gateway connection. You could have a scenario where a developer refreshes the Power BI Desktop file and publishes it to the service, and then repeats that process without ever needing to use the gateway.

The gateway comes in when you want to remove the Power BI Desktop from the process.

[MichaelP2020]

Ok, thanks... I'm sensing then this scenario doesn't have an obvious solution. 

The only workaround I could think of (not being entirely sure of its feasibility) is, could I publish a Dataset as a template for Designers to consume?

I.e. the Dataset would merely comprise the Connection String, but no Data / Modelling content?

Would this a.) alleviate the Designers being able to see the Data Source Credentials? And b.) Allow the Designers to take a copy of the Dataset and build the Data / Modelling for the content they wish to publish?

[MichaelP2020]

So if they were to 'Get Data' from the Desktop app, assuming they had the necessary access to the Data Source setup on the Gateway, are they able to reference this as opposed to having to enter the SQL instance Server Name, DB instance, Credentials etc?

[lbendlin]

Those are separate things.