Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roedeske
Advocate I
Advocate I

How to use Enterprise Gateway (SQL) Datasource within Designer?

Hi there,

 

I installed the latest Enterprise Gateway for SQL on-prem, I create a Gateway and setup a Datasource. Copnnection is telling status ok, and now the big question, how do I connect to that data source from within the Designer or also the PowerBI Web portal, I have no clue and did not find anything in the documentations. Can anyone help?

 

Regards,

Michael

1 ACCEPTED SOLUTION

Hi together,

 

I tried as you were telling and had success. It's just so simple.

 

1. Install Enterprise Gateway

2. Setup Gateway

3. Connect within local Designer to local SQL Server source

4. Choose DirectConnect

5. Create report

6. Publish report

7. Open Report in PowerBI ... the Gateway just got used, no configuration needed, it seems that just all gets routed by teh gateway.

 

 

View solution in original post

18 REPLIES 18
Anonymous
Not applicable
dhrot
New Member

Hi

just wondering on the naming requirements for the gateway, datasource etc.   What are the hooks for getting this working.   Do we need to name the datasource the same as the powerbi datasource, how does it find the gateway?

 

I would have thought i would just select the gateway as a datasource?

 

Thanks in advance.

Dave

 

 

Same here Dave, folks are stating that they can connect by give no examples of the details they are entering in for credentials to gain access... I don't want to see the exact credentials, but if there is something that has to be added to the server name or anything weird that isn't straightforward, I'd want to see the explicit example.  I would have thought that you simply connected to the gateway and selected the data source from there, strange it works otherwise.  How does it know how to transfer the call, or even intercept the call for the sql server instance?  Anyone got a clue?  Still haven't been able to use a SQL Server DirectQuery datasource through the Gateway even though it is configured and the data source is showing that it is ok in the configuration.  Any ideas?

In reply to my own query, it seems you need to have the PowerBI Desktop installed on the server and then publish from where the gateway is also installed and it just seems to pickup on the datasource being able to live query.

 

Thanks, look forward to being able to select gateway datasources in PowerBI and Excel directly.

 

 

Bjoern
Continued Contributor
Continued Contributor

For PBI WebService:

Get data -> SQL Server database -> it will search within your organization -> connect should do the trick (I have not tested it my self, but it works exactly like this for the AS Tabular Connector).

 

For PBI Designer:

Get data -> enter server name (please make sure that you are in the same domain etc., no firewall restrictions etc.).

Anonymous
Not applicable

@Bjoern When I go to Get Data from the Web Service, I don't see SQL Server database at all, just Azure SQL + SSAS on prem via the tabular connector.

 

In Desktop, I created a Get Data > SQL Server, connected in fine, built a simple report and published out to my PowerBI.com account.

 

When I go to PowerBI.com and try to schedule refresh, it says I need to install the Personal Gateway. So it's like it's ignoring that I have an Enterprise Gateway installed and configured with that particular data source.

 

So there's some disconnect here between creating a gateway data source and having PowerBI desktop "know" that the connection in it is configured in that data source so it can use that gateway to handle the refresh.

 

Anyone who can point out how to connect the Enterprise Gateway data source with an actual data source in a PBIX file?

 

Bjoern
Continued Contributor
Continued Contributor

@Anonymous Thank you for the good post.

Maybe to tell you what I do with the SSAS connector:

1. Install it on the database server, configure it with my PBI account.

2. create a PBIX-file, enter the servername as source.

3. create report sheets with the mentioned source.

4. publish the reports to the power BI web service. The data source is the name of the report, and not the "original" server name.

 

Not sure if that helps - if it is a live connect via gateway, I would assume it works the same way for the SQL enterprise gateway. I have not tested it myself, but will do so next week.

Hi together,

 

I tried as you were telling and had success. It's just so simple.

 

1. Install Enterprise Gateway

2. Setup Gateway

3. Connect within local Designer to local SQL Server source

4. Choose DirectConnect

5. Create report

6. Publish report

7. Open Report in PowerBI ... the Gateway just got used, no configuration needed, it seems that just all gets routed by teh gateway.

 

 

Hi there,

 

I also tried this and it does NOT work.

 

I am on my Azure VM with my MS SQL Server in Power BI Desktop.

 

I connected to my SQL Server on my Azure VM.

 

I saved the report.

 

I published to my Power BI account.

 

I get an error message that says this:

 

 

COMET_0024.gif

I have the enterprise gateway setup on my Azure VM.

 

SSAS works as expected and is easy.

 

All I do is click on Get Data, "Import of Connect to Data" Databases, SQL Server Analysis Services, and select my SSAS tabular models that are defined on my Enterprise Gateway - easy.

 

Why doesn't it work the same for SQL Server? For me it doesn't work at all.

 

Thanks,

Ric

Just updating this- I have the same issue.   The connection string to SQL seems "locked in" at step 1 in the designer ("Get Data"), and there doesn't seem to be anyway to re-key the connection string to a gateway other than on the SAME MACHINE you are running the designer on.   Which seems seriously stupid to me.    How can a consultant design a dashboard for a customer, without saying "oh, hey, you need to give me admin access to your database server, so I can load this design program, and I'm gonna RDT into that server for a few days to build your dashboards.   That's not a problem, is it?"    #WTF

Where do you reference the Gateway and/or Datasource you create under the Gateway?

 

For Step 3 is the "local SQL Server source" the actual name of the SQL Server?

 

For Step 4 did you meant to type DirectQuery versus DirectConnect?

 

Hi,

 

Thanks for your help, but on my side I'm still facing some troubles and data does not get refreshed.

When you say "local SQL Server", does it mean I need to install the designer on the server?

 

Currently I've installed the Enterprise Gateway on the SQL Server but the designer is installed on my laptop, from where I publish the reports.

 

Regards,

Fabien

Dear Fabien,

 

so I understood and is the way it is working for me. I see the same "problem" to have the designer installed on the source SQL server, but yeah product still in progress and we look forward.

 

Regards,

Michael

Anonymous
Not applicable

Yes, I verified this as well.

 

This should definitely be added to the documentation as it is not obvious this is how the Gateway works.

 

 

Also, be aware - the gateway uses its credentials to connect to the data source, ignoring whatever you've got set in your desktop file, so if you have any sort of row-level security / dynamic view this might become an issue. This is expected behavior of course, but stlil something to take note of.

Bjoern
Continued Contributor
Continued Contributor

@roedeske Great to hear that it worked out! Power BI is in general really simple and you don't need hours to build a setup like in SSRS. 😉 ... 

@Anonymous @Bjoern I have tested Enterprise gateway and works fine. Please note currently Enterprise Gateway only supports live connections to on-premises SQL Server databases using DirectQuery.

Dear Bjoern,

 

the SQL Server connected to the gateway is not within the Power BI Account Domain, so name resolution within network will never work. But in my understandig this is why there is this kind of gateway.

 

Regards,

Michael

Bjoern
Continued Contributor
Continued Contributor

@roedeske Erm, please correct me, if I am wrong. But if you would be able to share the source outside your organization, this would be a serious security threat. In my opinion the gateway is used with a Power BI account to publish the data (same like the SSAS connector) into the web-service, where it can be used by PBI accounts of your own organization.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors