On-premise sql gateway works with Power Apps and Automate but won't connect in Power BI

[lmheimendinger]

Hopefully this is the right section to post this question.

I successfuly installed on-premise gateway and added it to connectors in Power Apps and Automate, but no such luck in Power BI where i really need it.

When I choose Get Data, my only SQL option seems to be SQL Server.  I use the local area network server name for server and a table name for table, but when I try and connect the following message appears:

Details: "An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)'"

I would have expected something related to the gateway to appear; in Apps and Automate, SQL Gateway is an option as is using Windows Authentication.  This just does not seem right to me.

Can anyone help?  

[mwegener]

Hi @lmheimendinger,

is the required .NET framework installed?

• .NET Framework 4.7.2 (Gateway release September 2019 and later)

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

[lmheimendinger]

Apparently it is. I downloaded and ran install but got a no install message saying that version 4.7.2 or a later version was already installed.  This is an up to date 2019 Windows Server.

I will also add that i have a Power BI Pro license.

I noodled around trying to find any settings that would pinpoint on-premise gatewat but found none.

Am I wrong not to see a specific SQL choice for on-premise and not just Azure and SQL server? @mwegener 

[lmheimendinger]

OOPS.  The server where this is installed is a domain controller.  @mwegener 

Why does it work nicely for Power Apps and Automate but not for Power BI?  KInda sucks.

[v-shex-msft]

HI @lmheimendinger,

>> The server where this is installed is a domain controller

As the document said, it does not support to install on a domain controller(it may cause the credentials issues), I'd like to suggest you install this gateway on another device and try again:

Install an on-premises data gateway#related-considerations 

Spoiler

Related considerations

• Gateways aren't supported on Server Core installations.
• The user installing the gateway must be the admin of the gateway.
• The gateway can't be installed on a domain controller.
• If you're planning to use Windows authentication, make sure you install the gateway on a computer that's a member of the same Active Directory environment as the data sources.
• You shouldn't install a gateway on a computer, like a laptop, that might be turned off, asleep, or disconnected from the internet. The gateway can't run under any of those circumstances.
• If a gateway uses a wireless network, its performance might suffer.
• You can install up to two gateways on a single computer: one running in personal mode and the other running in standard mode. You can't have more than one gateway running in the same mode on the same computer.

Regards,

Xiaoxin Sheng

[lmheimendinger]

Have something new to report.  The new gateway on the new non-DC server would NOT connect in Power Apps and Automate.  It times out trying to add.  I deleted that connection, added it back on the DC where the SQL database is and it connects just fine.  Still same error from Power BI.

[mwegener]

Hi @lmheimendinger ,

can you share some screenshots?

This is how it looks with us.

[lmheimendinger]

Nope.  I created a new virtual machine and installed Windows 2019 Standard with desktop.  I joined to the domain but did not promote to a DC.  I uninstalled the gateway on the DC then installed on the member server.  In Power BI desktop, i tried to connect again but got the same error message as in my original post:

Details: "An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)'"

I then tried connecting but instead of using the server name where SQL is installed, I used the IP address.  Same result.  I tried just using  the server name with no database.  Same result.

It just feels like there is something wrong with my choices rather than withe the gateway.

One thing that really bothers me is that nowhere in the connection process am I prompted for or shown a gateway connector.  In Power Apps, e.g., the connector name shows up when setting up the connection.  Suppose I had several different gateways running on different servers; how would Power BI know which to use, or do multiple gateways HAVE to point to disctinct 

[lmheimendinger]

I will have to gen up a new server and will install and test. Am I correct, though, in choosing SQL server from the desktop Power BI?

[mwegener]

Hi @lmheimendinger ,

i do not know it.