cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roedeske Frequent Visitor
Frequent Visitor

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

Accepted Solutions
roedeske Frequent Visitor
Frequent Visitor

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

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.

 

 

18 REPLIES 18
Bjoern Established Member
Established Member

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

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.).

roedeske Frequent Visitor
Frequent Visitor

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

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

kthejoker
Advisor

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

@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?

 

ashishrj Senior Member
Senior Member

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

@kthejoker @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.

Bjoern Established Member
Established Member

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

@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.

Highlighted
Bjoern Established Member
Established Member

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

@kthejoker 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.

roedeske Frequent Visitor
Frequent Visitor

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

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.

 

 

Bjoern Established Member
Established Member

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

@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. ;-) ... 

kthejoker
Advisor

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

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.