Individual credentials per object through on-premises gateway, is it possible?

[SorenHElisa]

Hi,

Background:

We have an on-premises gateway server set up with the latest gateway software. Gateway service running with an internal service account (i.e. not a domain account). 

Our main data source on-premise is Vertica and we are using both ODBC and native Vertica drivers to connect to the database. 

Everything is working fine, but only with preset user credentials in Power BI Service's gateway connections. 

What we want:

We want each dataset and data flow to set the credential that is accessing the database. 

Example scenarios what we want:

GatewayConnection1: ODBC, DSN=OnPremVertica, dummy credentials set (to test connectivity)

GatewayConnection2: Vertica, host: a.b.c; database: dbA, dummy credentials set (to test connectivity)

Dataset1: using GatewayConnection1, credentials: userA

Dataset2: using GatewayConnection2, credentials: userB

Data flow1: using GatewayConnection1, credentials: userC

Data flow2: using GatewayConnection2, credentials: userD

Purpose:

We need to track the username on the database level, who is actually accessing the data in the database. Since the datasets might be in personal workspaces, we need to see what data is accessed by whom. 

Questions:

Is this possible?

If possible, what are the exact steps? And where can I find documentation about how to "ignore" the credentials currently set in the gateway connections?

Is creating one gateway connection per user the only way to get this working?

Hoping someone can help,

Sören

[SorenHElisa]

Hi,

Anyone has any ideas? Documentation? Links to other questions? Trust me, I've tried googling but not been able to find anything to get me forward. 

Best regards,

Sören

[jaweher899]

Yes, it is possible to set the credentials for each dataset and data flow in Power BI Service to access the on-premises data source using the on-premises gateway. This can be done by configuring the data source credentials for each individual dataset and data flow.

To configure the data source credentials for a dataset or data flow, follow these steps:

1. In Power BI Service, open the dataset or data flow you want to configure.
2. Click on the "Data source settings" option in the "Settings" menu.
3. In the "Data source credentials" section, select "Custom" as the authentication method.
4. Enter the username and password for the account that will access the data source. If you want to use a Windows account, select "Windows" as the authentication method and enter the appropriate Windows credentials.
5. Select the appropriate gateway and gateway connection for the data source.
6. Save your changes.

By configuring the data source credentials in this way, each dataset and data flow will use its own set of credentials to access the data source, and you will be able to track who is accessing the data at the database level.

Creating one gateway connection per user is not necessary to achieve this. You can use a single gateway connection for all datasets and data flows, and simply configure the data source credentials for each individual dataset and data flow.

[SorenHElisa]

Thanks for replying!

I'm trying to find these mentioned settings, but I'm unavailable. 

When creating an ODBC connection for the gateway, there are 3 options in the credentials part: Basic, Anynomous and Windows. No Custom. 

• Windows we cannot use, since Vertica is not configured for it. 
• Basic we can use, but that fixes that connection with a username.
• Anonymous we can use, but demands skipping the connection test. If skipping test is disabled, it gives us the error message "Unable to update this data source's credentials. Invalid connection credentials."

By using an anynomous, i.e. skipping the test, or basic authentication without filling the credentials and skipping the test, we get this message in the dataset:

Setting the credentials for the gateway connection, we get the same "admin has granted access.." and no option to set the credentials per dataset. 

Do we need to somehow create the gateway connection in another way? Using REST Api to delete the credentials in the gateway to somehow force the dataset to ask for credentials?

Any more things that we can test?

Best regrads,

Sören