Data Source setup on Enterprise Gateway

[Anonymous]

Hello PowerBIs, I have been give the responsibility of maintaining the On Premise Gateway. Part of the responsibility is adding Data Sources. I am struggling on how to setup database/source connections on the enterprise gateway. Multiple users have dashboards built using the same database with their own level of access.

I thought I have to create one connection for each of the user connecting to the same database with their own credentials.(not possible complaints - Duplicate Data Source )

which leaves me with only option

Create one connection with full read access on the database and let all the dashboards from all users refresh using that one connection.

This leads to my question - how is the source Database/file security handled?  @source? @users level access?

My concern is - will there be any security risk to have Data source connections with 100% access to the source.

Appreciate any suggestions or links that can help.

Thanks

[hyeung]

Hi,

Have only recently started using PowerBi across our organisation, and I also wanted to create duplicate data sources but utilise different user accounts (to reflect different permission sets). Within our organisation, we looking to offer self service BI but have some control to which data sources should be created to enable this. But equally to have some form of control as to which business teams have access to what data sources and to also tie down permissions to only specific database/table access. So different business teams may have access to the same database, but each have different table access requirements; this would translate to mulitple data sources on the same database but tied to different domain accounts with specific permission sets at the database level.

Like you, i found out PowerBi's limitation when creating duplicate data sources. However, upon further investigations it would appear that there is a way around it (a hack). You can create multiple hostnames which map to the same target database server.... this potentially allows you to define muitple data sources which effectively relate to the same server. You must ensure that the defined hostname alias is used in the pbix file and publish the content; and ensure that the same matching hostname alias is defined in your gateway datasource. By doing this, you can effectively have mutliple duplicate datasources (i..e same database but different credentials) by utilising hostname aliases. The option could be achieved by dns's or just define local hostname aliases. A hack but appears to work....

Cheers, Hung

[hyeung]

Hi,

Have only recently started using PowerBi across our organisation, and I also wanted to create duplicate data sources but utilise different user accounts (to reflect different permission sets).

Within our organisation, we looking to offer self service BI but have some control to which data sources should be created to enable this. But equally to have some form of control as to which business teams have access to what data sources and to also tie down permissions to only specific database/table access.

So different business teams may have access to the same database, but each have different table access requirements; this would translate to mulitple data sources on the same database but tied to different domain accounts with specific permission sets at the database level.

Like you, i found out PowerBi's limitation when creating duplicate data sources. However, upon further investigations it would appear that there is a way around it (a hack). You can create multiple hostnames which map to the same target database server....

this potentially allows you to define muitple data sources which effectively relate to the same server.

You must ensure that the defined hostname alias is used in the pbix file and publish the content; and ensure that the same matching hostname alias is defined in your gateway datasource.

By doing this, you can effectively have mutliple duplicate datasources (i..e same database but different credentials) by utilising hostname aliases.

The option could be achieved by dns's or just define local hostname aliases.

A hack but appears to work....

Cheers,

Hung

[Eric_Zhang · Accepted solution]

@Anonymous wrote:

Hello PowerBIs, I have been give the responsibility of maintaining the On Premise Gateway. Part of the responsibility is adding Data Sources. I am struggling on how to setup database/source connections on the enterprise gateway. Multiple users have dashboards built using the same database with their own level of access.

I thought I have to create one connection for each of the user connecting to the same database with their own credentials.(not possible complaints - Duplicate Data Source )

which leaves me with only option

Create one connection with full read access on the database and let all the dashboards from all users refresh using that one connection.

@This leads to my question - how is the source Database/file security handled?  @source? @users level access?

My concern is - will there be any security risk to have Data source connections with 100% access to the source.

Appreciate any suggestions or links that can help.

Thanks

@Anonymous

Based on my test, it is not allowed to set up different credentials for one data source in one on-premises gateway. You can either

1. Set up multiple on-premises gates and create datasource with different credential in each.
2. If not in DirectQuery mode, use Personal gateway and set different credential in indivisual datasets.

As to the security risk to have data source connection with 100% access, I'd agree with @Seth_C_Bauer, "Permission levels should be a concern of the report author." For other aspect, per my personal experience, it should be safe. As the credential is encrypted and Power BI just query the database.

[Seth_C_Bauer]

@Anonymous Let me see if I can answer this based on your description:

First, as the owner of the On Premises Data Gateway, it shouldn't be your concern the level of access a user has in the SQL database. The connection that you create to the datasource, and the users you assign to the gateway in the PBI Service just allows those users to publish datasets to Power BI and have access to use the gateway as a refresh option.

Permission levels should be a concern of the report author. Because if they are using Direct Query or Import, then the end user is seeing the same level of data as the report author. Unless you have row level security applied.

As to the people building the reports, your granted access has nothing to do with what they can see in the SQL DB. Those permissions are contained within the SQL server and database...

I think I hit all your points, if not, let me know and I can clarify.

[Anonymous]

Thanks ENO1978 for the response.

That means the user account that i use to create connection should have access to the full database?

I have users using the sharepoint site, in this case should the account used to create the connection on the gateway should have access to the whole sharepoint site?

[Eric_Zhang]

---

@Anonymous wrote:

Thanks ENO1978 for the response.

That means the user account that i use to create connection should have access to the full database?

I have users using the sharepoint site, in this case should the account used to create the connection on the gateway should have access to the whole sharepoint site? 

---

I think @Seth_C_Bauer means, for database, as long as the data/content in the report is limited at design time, any credential(viewer or admin) with sufficient access is OK.

As to sharepoint, the report data may vary according to the account, so it all depends on what's in the report and who can see what data.

[Anonymous]

Thank you for all the inputs. Now I can plan with better understanding.

Appreciate all the help.