Forum Discussion
Enterprise Gateway to on-premise SQL Server
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.
7 Replies
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
AnonymousThank 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
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 [email protected] and [email protected] 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
