Enterprise Gateway to on-premise SQL Server

[MikeF]

Hi There,

I have a couple very basic questions:

I want to allow PowerBI.com to access data from an on-premise SQL 2012

1. Do I install the Enterprise Gateway on the SQL Server ... or on a web server with access to the SQL server (via port 1433)

2. Assuming that I install the Gateway on the SQL Server... does that mean that all connectivity to PowerBI.com is via some type of GW 'push'? I cannot envision a situation where PowerBI.com can 'reach into' an on premise SQL (pass two firewalls) and grab data on demand. (Perhaps I am misunderstanding the way the GW is supposed to work).

Thanks for any assistance,

Mike F.

[Seth_C_Bauer]

@MikeF

1) It can be on any server in your domain

2) The Gateway works in conjunction with Azure Service Bus to secure a connection to pull/query data. For a lot more info here are some relevant posts you should read.

Overall Security - I recommend downloading and reading the whitepaper

Enterprise Gateway

EG - In Depth

[MikeF]

@Seth_C_BauerThank you.

I have read the suggested paper, but I still don't fully get the connection to the on-premise SQL.I am a newbie to PBI so perhaps I could ask a few very simple questions I'm still not certain about.

1. If the enterprise GW is installed within my domain can I create a report in powerBI.com, based on a dataset that relies upon data from an on-premise SQL DB? (without SSAS).

2. If so... if data changes in the SQL DB will the report be refreshed? If yes... is the data pushed to PowerBI by the GW recognizing the SQL data has changed? Or am I completely off-base here?

3. Does the on-premise security connection to SQL depend upon Azure AD? i.e. does my org AD need to be associated with an Azure AD, or can the security model work simply with my org AD?

Thanks for assistance

Mike

[cryan]

Hi @MikeF,

I've also been looking into this piece, so I will answer what I can:

The gateway only polls the Azure service bus for pending requests, so there isn't anything just going straight "in" to the on-prem SQL Server where the gateway is installed. 

1. If the enterprise GW is installed within my domain can I create a report in powerBI.com, based on a dataset that relies upon data from an on-premise SQL DB? (without SSAS).  Yes, if your powerbi.com account is using an email address that matches an account within your domain. For SQL Server, the gateway will poll the service bus for any pending requests and then using the credentials (SQL Server Authentication) set within the gateway data source, it'll query SQL Server and push back the dataset. I'm learning it's a little different with SSAS than with SQL Server, but those seem to be the basic requirements for SQL Server. 

2. If so... if data changes in the SQL DB will the report be refreshed? If yes... is the data pushed to PowerBI by the GW recognizing the SQL data has changed? Or am I completely off-base here? You've still got to click "refresh" within the browser or manually click the report link, but the dataset and/or report will always be updated with the latest data from SQL Server. If one of your filters is date based, you do need to account for that. The html may auto refresh with new data every few seconds, I'd have to double check. 

3. Does the on-premise security connection to SQL depend upon Azure AD? i.e. does my org AD need to be associated with an Azure AD, or can the security model work simply with my org AD?  It does not. The documentation has been using that user@contoso.com and user@contoso.onmicrosoft.com scenario as an example. If your powerbi.com account matches what you've already got established within your org AD then you are good. if it doesn't, then I believe you need to look into the directory sync options. This is a piece I haven't fully explored yet.    

 Good luck,

-Chris

[MikeF]

->>  "You've still got to click "refresh" within the browser or manually click the report link, but the dataset and/or report will always be updated with the latest data from SQL Server."

1. How does this data refresh happen? Does a message get sent from powerBI.com to the Enterprise Gateway that, in turn, contacts the SQL Server to retrieve the data? 

2. If so, what is the mechanism by which the Enterprise GW is contacted by PowerBI.com?

3. Does the GW need to be accessible to the Internet so that the PowerBi.com can make requests to it? 

Sorry for the confusion... thanks for any assitance

Thank you

Mike

[Anonymous]

Hi Mike,

As far as I understand there is only outbound traffic between the on prem gateway and the Azure Service Bus... IT services don't need to open inbound ports on firewalls or something like.

What the gateway does is polls the ASB for any pending requests and if there are some, it goes and fetches the new resultsets delivering it back to the ASB.

Hope it was clear enough.

The only thing that I still don't have clear is how the poll mechanism works... like frequency and stuff like that.

[cryan]

The Service Bus documentation helped my understanding of this. It's the bridge between powerbi.com and SQL Server and is the mechanism you're asking about. The Gateway contacts the Service Bus, which looks for messages/requests queued by PowerBI.com. That's the one-way, outbound communication mechanism in this case. If there are messages in the Service Bus queue, the gateway retrieves, SQL Server processes, and then sends the response back to the GW and through the Service Bus and thus back to Power BI for the visual.  

Locally installed GW needs to get out to the internet to contact the Service Bus. If it's not residing on your SQL Server, then it should be installed on a server that can make outbound internet requests, as well as query the on-prem SQL data. There are ports listed in that in-depth doc. There's some other documentation about the port priority, too. 

https://azure.microsoft.com/en-us/documentation/articles/service-bus-fundamentals-hybrid-solutions/

[MikeF]

Thanks Chris, appreciate it. That clarifies things...