You have probably heard about the phrase “gateway”. But what is meant by this phrase?
In an easy explanation: a gateway is a (data) bridge between an on-premise data source and a Microsoft cloud service. These cloud services include Power BI, PowerApps, Power Automate, Azure Analysis Services, and Azure Logic Apps. An one-premise data source can be a source on a hard-drive or a SQL server containing data and running on-premise*, so not cloud based.
*On-premises software is installed and runs on computers on the premises of the person or organization using the software.
Cloud services of Microsoft need to use the data from the on-premise data source to be able to function. Therefore the data needs to be transferred from on and other. To make sure this happens in a quick and secure way you will need to install a data bridge. Hence a gateway.
Gateway options
There are two different on-premise gateways available.
- On-premises data gateway: Allows multiple users to connect to multiple on-premises data sources. With a single gateway installation you can use an on-premises data gateway with all supported services. This gateway is well suited to complex scenarios where multiple people access multiple data sources.
- On-premises data gateway (personal mode): Allows one user to connect to data sources and can’t be shared with others. An on-premises data gateway (personal mode) can be used only with Power BI. This gateway is well suited to scenarios where you’re the only one who creates reports and you don’t need to share any data sources with others.
How does a gateway work?
To be able to make a gateway work we need to have access to the cloud service. For example: Power BI (service). We also need access to the on-premise data source. For example: A SQL server, or file on a hard drive which runs on computers on the premises of the person or organization using the software. If we have installed our gateway correctly – see subject: How to install and use the gateway ( in relation to Power BI) – and we have entered the right credentials in Power BI service, we will be able to interact with it.
Once we start an interaction with the on-premise data source, starting from the Microsoft cloud service, a sequel of steps will follow:
- The cloud service will create a query and encrypt the entered data source credentials. The query and the encrypted credentials are send to the gateway queue for processing.
- The cloud services analyzes the query and then send the query to azure relay*.
*The Azure Relay service enables you to securely expose services that run in your corporate network to the public cloud. You can do so without opening a port on your firewall, or making intrusive changes to your corporate network infrastructure. Source : https://learn.microsoft.com/en-us/azure/azure-relay/relay-what-is-it
- Azure Relay sends the requests to the gateway. Both the gateway and Microsoft cloud service are implemented to only accept TLS 1.2 traffic*.
*Transport Layer Security (TLS) 1.2 is the successor to Secure Sockets Layer (SSL) used by endpoint devices and applications to authenticate and encrypt data securely when transferred over a network. Source: https://www.accuenergy.com/support/reference-directory/tls-transport-layer-security-protocol/#:~:text=Transport%20Layer%20Security%20(TLS)%201.2,IoTs%2C%20meters%2C%20and%20sensors.
- The gateway receives the query, decrypts the credentials, and connects to one or more on-premise data sources with those credentials.
- The gateway sends the query to the data source to be run.
- The results are sent from the data source back to the gateway and then to the cloud service. The service then uses the results.
How to install and use the gateway ( in relation to Power BI)
Now that we know the definition of a gateway, the different options, and what it does, we should be able to use it. So, in relation to Power BI, let start with a case:
We have created a beautiful report in Power BI desktop. The data comes from an on-premise SQL server and we would like to publish this report into the Power BI service. Our goal is to add automatic refresh on the report so that the end-users of the report will always have the latest data.
By just publishing the report into the service you will not be able to add automatic refresh. Power BI service has no option yet to receive the data from the on-premise SQL server. To be able to have this we need to install an on-premise gateway.
If we follow the following step-plan that should not be too difficult.
1. Start by downloading the gateway. You can download this from https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install.
Because the gateway runs on the computer that you install it on, be sure to install it on a computer that’s always turned on. If you turn of the computer the gateway will not work.
2. In the gateway installer, keep the default installation path, accept the terms of use, and then select Install.
3. Enter the email address for your Office 365 organization account and then select sign in. The gateway is associated with your Office 365 organization account. You manage gateways from within the associated service.
4. Select Register a new gateway on this computer and then click on next.
5. Enter a name for the gateway. The name must be unique across the tenant. Also enter a recovery key. You’ll need this key if you ever want to recover or move your gateway. Select Configure.
6. Review the information in the final window. Select close.
You now have a running gateway installed. let’s install it also in Power BI service.
To install a gateway in Power BI service make sure you have access to an admin account. Only then you can manage and install gateways.
8. Login into Power BI service (preferably with your admin account) and click on settings – manage
connections and gateways.
9. On the tab connections, click on new and choose on-premises.
10. Enter the gateway cluster name. This cluster name is created while installing the gateway. See step 5.1
11. Choose a connection name and connection type for the gateway.
12. Once the connection type is selected, provide the required details that are needed to connect to the source database in the local system. If you don’t know these, ask the owner of the on-premise data source. This is usually your local admin.
13. Click add.
That’s all. You now have a working Power BI gateway, ready to use. If you now go back to your published Power BI dataset you will be able to choose gateway and cloud connections ( in your dataset settings) and your gateway should be there as an option to choose from. Once chosen the gateway, your automatic refresh should also be ready for installment.
Now be aware! If you add a new data source to the gateway. For example: If you made a connection with a folder on a hard-drive and you make a connection with a file in this folder? For every new file you have to manually add that file to the gateway and cloud connections ( in your dataset settings). This does not mean you need to re-install the gateway, but your connection will be broken until you choose all the files for the gateway.
Sources
https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem
https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install
https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem-indepth
https://learn.microsoft.com/en-us/azure/azure-relay/relay-what-is-it
https://www.sqlshack.com/setting-up-power-bi-data-gateway/
