So, you have explored Power BI (free) and wanted to start some action in the cloud. Suddenly you realise that your data is stored in an on-premise SQL data source and you still wanted to get insights up in the cloud and share it with your senior business management.
Microsoft’s on-premises data gateway is a bridge that can securely transfer your data to Power BI service from your on-premises data source.
- Power BI pro licenses have been procured already for the required no of users (this is a MUST)
- Users are already part of Azure AD and can sign in to Power BI service as part of Office 365 offering
You can build and setup a machine to act as a gateway between your Azure cloud service and on-premises data sources. Following are the pre-requisites to build that machine:
1) Server Requirements
- .NET 4.5 Framework
- 64-bit version of Windows 7 / Windows Server 2008 R2 (or later)
- 8 Core CPU
- 8 GB Memory
- 64-bit version of Windows 2012 R2 (or later)
- The gateway is supported only on 64-bit Windows operating systems.
- You can’t install gateway on a domain controller
- Only one gateway can be installed on a single machine
- Your gateway should not be turned off, disconnected from the Internet, or have a power plan to go sleep – in all cases, it should be ‘always on’
- Avoid wireless network connection, always use a LAN connection
- It is recommended to have the gateway as close to the data source as possible to avoid network latency. The gateway requires connectivity to the data source.
- It is recommended to have good throughput for your network connection.
- Once a gateway is configured and you need to change a name, you will need to reinstall and configure a new gateway.
2) Service Account
If your company/client is using a proxy server and your gateway is not having a direct connection to Internet. You may need to configure a windows service account for authentication purposes and change default log on credential (NT SERVICE\PBIEgwService) to a service account you like with a right of ‘Log on as a service’
The gateway creates an outbound connection to Azure Service Bus and does not require inbound ports for communication. It is required to whitelist IP addresses listed in Azure Datacentres IP List.
Once you are over with a pre-requisite as listed in the previous paragraph, you can proceed to gateway installation.
- Login to Power BI with your organisation credentials and download your data gateway setup
- While installing, you need to select the highlighted option so a single gateway can be shared among multiple users.
- As listed in pre-requisites section, if your network has a proxy requirement – you can change the service account for the following windows service:
- You will notice gateway is installed on your server
- Once you open a gateway application, you can see a success message
Post installation, you need to configure a gateway to be used within your organisation.
- Open gateway application and sign in with your credentials
- You need to set a name and a recovery key for a gateway that can be used later to reconfigure/restore gateway
- Once it is configured successfully, you will see a message window that now it is ready to use
- Switch to Gateway’s Network tab and you will see its status as Connected – great!
- You are all set, the gateway is up and running and you need to start building reports to use data from your on-premises server using gateway you just configured.