Personal Gateway fails to synchronize SQL server with Windows authentication

[kakkaroolari]

BACKGROUND

I have a company e-mail / windows user account linked to Power BI license, let's call it 'user' - 'user@company.com'

I have an on-premises Windows server into which I log using 'server_user' - both 'user' and 'server_user' have the same e-mail address in the AD 'user@company.com'

On-premises SQL datasource (with SQL credentials)

Personal gateway installed into on-premises windows server, personal gateway is configured and logged into Power BI service.

When I click Sign-In in the Gateway Configurator the web popup windows I select 'user@company.com' ('user') [I DO NOT SELECT: 'server_user' - 'user@company.com' which is also visible in the list to select the account from.]

In the personal gateway I have 4 datasources:

1. SQL datasource with Windows Authentication
2. Network FOLDER with Windows Authentication
3. Excel file with Windows Authentication
4. SQL datasource with SQL credentials

Data sources 2,3 and 4 are OK

PROBLEM:

1. SQL datasource with Windows Authentication:

I open "Edit credentials" and choose "WindowsWithoutImpersonation" and level "Private"

RESULT:

Synchronize fails with:
"Failed to update data source credentials: Either the user, 'COMPANY\server_user', does not have access to the 'BBBBXXXXYYYYZZZZ' database, or the database does not exist.Show details"

SQL datasource with windows credentials

QUESTION(s):

a) Even though I never input 'server_user' into this gateway app (nor any Power BI gadget), why does it attempt to use it anyway for SQL datasource with Windows authentication? For FOLDER and FILE access this seems to be using the Windows account I

input (I know, because 'server_user' does not have privileges to the network FOLDER).

b) Is there any way to tell the Personal Gateway / SQL source which Windows user I want to use? The selection is just 'windows user', but which one?

c) Does the fact that both 'user' and 'server_user' use the same email address have influence in the matter?

d) Any ideas how to workaround?

[v-xicai]

Hi @kakkaroolari ,

There are some points you may considerate:

1.Make sure the account signing in the personal gateway is the same with the Power BI Service.

2.Make sure the connect credential of SQL Server in Power BI Service is the same in Power BI Desktop. Verify that you have the correct username and password. Also, verify that those credentials can successfully connect to the data source. Make sure the account that's being used matches the authentication method. When connect to the SQL Server, set the Authentication as Windows instead of Basic.

3.Make sure the gateway was installed on the same machine as the SSMS exists.

4.Please make sure the gateway is online, and try to upgrade the gateway to latest version.

5.Your data source credential may expire, so try to update the credential and republish the report. In Power BI, go to refresh settings for the dataset, select Edit credentials to update the credentials for the data source.

You may resolve the issue via the troubleshooting link :https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios .

Best Regards,

Amy

Community Support Team _ Amy

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

[kakkaroolari]

This answer is almost fully unrelated.

Item 3. Why would Microsoft SQL Server Management Studio would need to be installed in a Windows Server where the personal gateway is running?

WHEN

I login to "Personal gateway" with 'user' - 'user@company.com' 

THEN

1 out of 4 datasources in the "personal gateway" fails with error  'server_user' - 'user@company.com' does not have privileges.

RESULT:

This is true,  'server_user' does not have privileges to the Cube

EXPECTED RESULT:

Cube would try to sync with the user that has Signed in to "Personal gateway" and "power bi service"

[kakkaroolari]

Is there any way I can force Personal Gateway to connect to datasources with username, and not e-mail address?

Our company policy is to have two usernames with desktops and different one for servers. And the Gateway seems to select wrong one randomly.

Some hidden config somewhere?

[v-xicai]

Hi @kakkaroolari ,

>>Item 3. Why would Microsoft SQL Server Management Studio would need to be installed in a Windows Server where the personal gateway is running?

If you need to connect to SQL Server in SSMS, you may need to do so.

>>Is there any way I can force Personal Gateway to connect to datasources with username, and not e-mail address?

You can sign in personal gateway with the account which you would like to use in Power BI Service.

For reference: https://docs.microsoft.com/en-us/power-bi/service-gateway-personal-mode.

Best Regards,

Amy

Community Support Team _ Amy

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

[kakkaroolari]

>>If you need to connect to SQL Server in SSMS, you may need to do so.

For sure, but I like to do that from my physical workstation, not from the server via remote desktop.

>>You can sign in personal gateway with the account which you would like to use in Power BI Service.

I can but the gateway does not use the account I select to access data.

In other words, am I incorrect at assuming that: Power BI account MUST be the same as the Windows user that is logged in to machine.

This has changed worse since Fall/November versions, before I could do something with it. Even the File, Folder shares are no longer working. Before it was just SQL (more precise, Analysis services) source.

Is this intended behavior? I'm asking if I have any chance in continuing with our workflow, or do we need to start refactoring our setup?