How to setup 2 Data Sources in a Enterprise Gateway connecting to same server or database?

[BBIUser]

Hi,

Currently, I have just one Gateway Cluster "Gateway1" and I have created 1 Data Source "Data Source1" connecting to the Server and Database. An Active Directory Service account has been created for this Data Source with assigned the credentials as needed.

Now, I have a requirement where the schema permissions will be different for a particular team connecting to the same Server and Database. So I cannot use the existing "Data Source1" even though it is connecting to the same Server and Database.

I tried creating another Data Source "Data Source2" in the existing "Gateway1". The Server and Database details remains the same.

But I create a new Active Directory Service Account for this new Data Source and give limited schema/table permissions and also set different credentials. If I create another Data Source connecting to the same Server and Data base as in "Data Source1", I get this error.

1) Can't I create another Data Source connecting to the same Server and Database with different AD Service Account and credentials?

2) What is my alternative method that I need to setup based on my requirement?

3) What is the limitation here that I need to understand?

Thanks. Appreciate your help! 

[v-piga-msft · Accepted solution]

Hi @BBIUser ,

As I know, though you add the user to the User List for the data source in Data Gateway, the permission of the DB Level for the that user will not change. 

So in your example, the new user will access to just 1 schema as usual.

Actually, it will depends on whose credential used in the published PBIX file. If the new user get data with User 1's crendential, he will  have the same DB Level permissions (access to those same 3 schemas) as User 1. If he used his own crendential, he will only have access to 1 schema.

Hope I explain it clear.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft]

Hi @BBIUser,

I'm afraid that we cannot add the same data source to gateway.

For you scenario, you could add the user who you want to grant access to the selected data source to the User List after adding the data source to gateway so that when the user use the data source, he will not need to configure the gateway.

More details, you could refer to this article Add users to a data source.

Best Regards,

Cherry

[BBIUser]

Thanks for the solution @v-piga-msft Cherry! But, If I add the user to the existing data source User List, won't that user have the same DB permissions as other users?

For example:
User 1 who is already in the User List (from my example - under "Data Source1" and "Gateway1") has DB Level permissions to say 3 schemas.
Server1
 Schema1          Schema2               Schema3
  Table1.1            Table2.1                   Table3.1
  Table1.2            Table2.2                   Table3.2
  Table1.3            Table2.3                   Table3.3

Let us assume that if I add the 'new user' to the same "Data Source1" and "Gateway1" User List.
Does this 'new user' have the same DB Level permissions (access to those same 3 schemas) as User 1?

If so, I do not want this 'new user' to assign that permission. Instead the 'new user' needs access to just 1 schema.
Server1
 Schema1
  Table1.1
  Table1.2
  Table1.3

So, when I use a Service Active Directory account, I assign this service account to the requested schema's with read permissions.

Basically, when a add the 'new user' to the User list, that 'new user' should not see the Schema2 and Schem3.

How should I go about configuring this in the Gateway? Any other alternatives? Thanks!

[v-piga-msft · Accepted solution]

Hi @BBIUser ,

As I know, though you add the user to the User List for the data source in Data Gateway, the permission of the DB Level for the that user will not change. 

So in your example, the new user will access to just 1 schema as usual.

Actually, it will depends on whose credential used in the published PBIX file. If the new user get data with User 1's crendential, he will  have the same DB Level permissions (access to those same 3 schemas) as User 1. If he used his own crendential, he will only have access to 1 schema.

Hope I explain it clear.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[BBIUser · Accepted solution]

Thanks for the reply  @v-piga-msft Cherry!

Actually, this solution from Hung worked for us (defined hostname alias)

https://community.powerbi.com/t5/Service/Data-Source-setup-on-Enterprise-Gateway/m-p/148597#M21516