[Larry Heimendinger]

I installed SQL Server on a domain server and also installed the SQL On=Premise connector.  I successfully added a connector to Power Apps to this, and it then also appeared in Power Automate.  I use Windows authentication for SQL, and when I create the connector, that is a choice I can make (Azure, SQL or Windows authentication).  I then have access to the database and tables via Apps and Automate.

However, when i try to connect to the same on-premise connector via Power BI desktop, things are different.  

When I choose Get Data->Database, SQL is an option that I choose.  It prompts me for Server (I have used the server name as well as the IP address, on my LAN of course) and the table name as well as leaving it blank.  If I clear the credentials and enter the wrong ones (on purpose) I get an invalid user name message.  However, under all of the above conditions with correct Windows credentials I get the following message:

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)'”

FYI  I am a global admin for the on-premise domain, created the SQL install and database, and am also an O365 global admin and have a premium Power BI license.

Really need to get this solved, so any help much appeciated.

——————————
Larry Heimendinger
larry@techevents.com
Redmond WA
——————————

[Lutz Bendlin]

Creating connectors via PowerApps/PowerAutomate is a little frowned upon. Can you create the connector directly on the Power BI On-Premise gateway?

Note that gateways are VERY picky about the EXACT spelling of the connection details. For example if your gateway uses FQDN and your Power BI Desktop connection uses IP address then they will not work together

——————————
Lutz
——————————
——————————————-

[Larry Heimendinger]

Lutz-

Thanks for the reply.  But I am still puzzled.  Let me explain why:

1. You asked if I could create the connector directly on the Power BI On-Premise Gateway.  The answer is no I could not using either Power BI Desktop or Online. 
   1. Desktop
      1. When I try Get Data, the only thing I can seemingly choose is SQL Server.
      2. When i choose that, I am prompted for server name and optionally database name,  I have tried using the FQDN (Windows server name) as well as the LAN IP address.I have entered the database name exactly as it appears in SQL as well as left it blank.  In all four combinations, I get the same error message which I posted in the original on this thread.
      3. I was expecting to have some indication that an on-premise connector had been installed but nothing I could find indicates that.
   2. Online
      1. Under connectors, when I click create a new connection and choose databases, only Azure appears.  There seems to be no indication of an on-premise connector I have installed.
      2. And of course if I choose Azure SQL I am referred to the desktop version.

So the first bit of help I need is to have explained to me the steps to connect via the On-Premise Gateway in Power BI.  If I am doing the right thing, then, why the error.

Your response puzzled me as well.  Why are connectors n Apps/Automate frowned upon?  I need access to the SQL data to use those. Should I have tried first in Power BI with no custom connector yet in Apps/Automate?

The documentation kinda sucks on this, so your help is much appreciated.

——————————
Larry Heimendinger
larry@techevents.com
Redmond WA
——————————
——————————————-

[Lutz Bendlin]

here’s the process:
– create an on-premise gateway – preferably a cluster with multiple machines in different geographic locations
– on the Power BI Service go to Settings…Manage Gateways
– create the connections to your on-premise data sources and assign access to the developers that should consume these connections (NOT the end users!)
– now use these connections in your Power BI datasets (scheduled refresh settings) and in your Power Apps/Power Automate applications

Note:  Gateway maintenance SUCKS.  There is no sorting of connections (CTRL-F is your friend), there is no indication of connection ownership (we have a rule that the connection owner name has to be part of the connection name), and the frequent gateway software updates WILL break your gateway.  The load distribution is infantile, it requires the main cluster member to be online at all times,  Connectors may disappear if one of the cluster members is unreachable.  I could go on and on but this is a family friendly show here, i think.

——————————
Lutz
——————————
——————————————-

[Larry Heimendinger]

Lutz

Thanks.  I was able to make some headway/  I found Manage Gateways on the gear wheel drop down and my connector showed up there.  I was even able to add another copy of it directly there with a different name.  This was in Power BI online.

I then went to Power BI desktop to use the connector and see if I could create a report.

This time, when I did Get Data and chose SQL, I used the name of one of the Gateway connectors and got prompted for type of authentication and credentials.  Hurrah I thought I’m on my way.

No such luck.  I got the same error message 

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)'”

as I got from my first attempt and posting of the original message.

Since my gateway showed up I guess just by installing it and using my O365 credentials it was probably intact when I first started.  Maybe the issue is with the SQL database itself, a permissions/security setting?

I would point out how incredibly bad the UI is on this.  In the online version, Manage Gateways is incredibly obscure.  And in the desktop version, it appears not at all.  Get Data->Database should give reference to this, not have it be a scavenger hunt game.  

Ugh

——————————
Larry Heimendinger
larry@techevents.com
Redmond WA
——————————
——————————————-

[Lutz Bendlin]

You’re holding it wrong.  🙂  There is no need for the gateway on the Power BI desktop, since your desktop machine already sits on-premise.  (The purpose of a gateway is to allow Azure access to your on-premise data sources)

Gateways are only used on the Power BI service, and on the other Power suite apps (Power Apps. Power Automate etc)

Your error message is unrelated to the gateway, you need to investigate and fix it independently.

——————————
Lutz
——————————
——————————————-

[Larry Heimendinger]

Of course that makes sense because Apps & Automate have both their development and execution platforms as web based while Power BI is localized to create reports.  

I had already shifted gears yesterday to investigate the SQL server itself as a culprit.  I have been playing with permissions and settings but have not hit on a solution yet.

Thanks for helping with this so far.  I will post successful results (hopefully) when I have found them.

——————————
Larry Heimendinger
larry@techevents.com
Redmond WA
——————————
——————————————-

[Steen Schlüter Persson]

Hi,

I know it has been a while since there have been any update on this thread, but did you ever got any further with the problem?
I’m struggling with the same problem when connecting to a on premise SQL server from Power BI Desktop. I have been around many ideas like running Power BI as Administrator, checking .NET version, using IP adress instead of servername etc., but still with very little luck. 
I can make it work most of the times when I work on our DEV SQL server, but I can’t figure out what it is that makes that any different from some of the other servers where it doesn’t work. Furthermore, it also seems to cause troubles running a Scheduled Refresh for that Power BI app on our on-premise Report Server. Here I constantly get an error connecting to datasource (null), and my guess is that it’s related to the problem connecting from desktop. 

Regards

——————————
Steen Schlüter Persson
Head of BI
——————————
——————————————-

[Larry Heimendinger]

I never did get it fully working; it was a test project that got put on hold and then canceled, so I never got back to it.  I suspect that our experiences are similar in that it is server specific, but did not get around to trying other ones.  Thanks for posting.

——————————
Larry Heimendinger
larry@techevents.com
Redmond WA
——————————
——————————————-