Power BI is a data analysis tool that connects to many data sources. Suppose the data source for Power BI is located in an on-premises location. In that case, the connection from the cloud-based Power BI Service to the on-premises located data source should be created with an application called Gateway. In this post, you will learn what the Gateway is, the types of the gateway, their differences, installing the gateway, and scheduling a data set with that gateway.
Video
What is Gateway?
Power BI Gateway connects Power BI cloud-based data analysis technology and the data source on-premises. Gateway is an application that can be installed on any server in the local domain. The gateway is responsible for creating the connection and passing data through.
The diagram below shows the role of the gateway in connection;
Do you need a gateway always?
You don’t need a gateway in all scenarios, Only if the data source is on-premises. For online or cloud-based data sources, no gateway is required. For example, if you are getting data from CRM Online, you don’t need a gateway. However, if you are getting data from an SQL Server database located on your local domain server, then you need a gateway. For Azure SQL DB, you don’t need a gateway. However, a SQL Server database on Azure Virtual Machine is considered on-premises and needs a gateway.
Types of Gateway
Power BI Gateway comes in two modes: Personal mode and standard mode. The difference between these two is not the paid or licensing plan. Both gateways are free to use. The difference is the way that you want to use the gateway. The personal mode is mainly used for one-person use, not for the team. On-premises standard gateway, on the other hand, is a choice when you want to work in a collaborative environment. Let’s look at their differences in detail. In the chart below, the on-premises mode means the standard mode of Power BI on-premises gateway.
Personal Mode
When you install the gateway in personal mode, you can use it yourself only. You can connect it to local data sources such as SQL Server, Excel, and other data sources. However, the gateway installed in personal mode only supports one type of connection: Import data or schedule refresh. This gateway is only used for Power BI; you cannot use it for other applications.
Because this gateway is personal, you cannot use it in a team development scenario. Multiple developers cannot leverage this gateway. You can create reports and connect them to this gateway and share it with multiple users. However, only one developer can use the gateway. That is why it is named personal mode.
Installing the Personal model and configuring it is easier than the on-premises gateway. When you install the gateway in personal mode, you don’t have the configuration option to set data sources. There is no place to configure it after installation. This gateway mode is meant to be used for business analysts with the least hassle to get their reports published and refreshed.
This type of gateway is usually for one business analyst who wants to publish Power BI reports and schedule them to refresh and share them for users to use easily. Not many configuration options, easy to set up and single developer features make it a good option for such scenarios.
On-premises Standard Mode
Power BI On-premises gateway has a recommended mode to install, which is the standard mode. This mode of installation supports a multi-developer environment. Multiple developers can use the gateway installed. This gateway type is built for team development; you can have a gateway administrator. There is a central configuration section for gateways to add data sources and control them.
On-premises recommended gateway supports not only Power BI but also PowerApps, Azure Logic Apps, and Microsoft Flow, which are other Microsoft cloud-based technologies.
On-premises recommended gateway also supports all types of connections from Power BI. Not only the import data or scheduled refresh is supported, but also DirectQuery and Live Connection are supported with this gateway.
This type of gateway is for enterprise usage of Power BI or where Power BI needs to be used alongside other applications such as PowerApps. Multiple developers can work with the same gateway if the administrator authorizes them to use it. More centralized control and monitoring exist for this type of gateway.
Gateway Execution flow
Here are the details of the architecture of the gateway;
- Gateway installed on a machine in the on-premises domain. During this installation, credentials are stored in local and Power BI services.
- Credentials entered for the data source in Power BI are encrypted and then stored in the cloud. Only the gateway can decrypt the credentials.
- Power BI service kicks off a dataset refresh; this happens through a Scheduler service in Power BI.
- Data Movement Service analyses the query and pushes it to the appropriate service bus instance.
- There is a queue of requests on the service bus. Gateway pulls the bus for pending requests.
- Gateway gets the query and executes it on the data source.
- After getting the result, the gateway pushes that back to Power BI.
What happens in step 5 is very important. Gateway is pulling the bus to check if there are any pending requests. The bus cannot trigger the gateway. The reason for this architecture is security. If the bus can trigger the gateway, the inbound security ports need to be open, which is not a good practice for security. So, we can say that the gateway connection is very secure because it is only using outbound ports.
Important things to consider before installing the gateway
The gateway can be installed on any machine in the on-premises domain. However, it is not recommended to be installed on the Domain Controller itself. Here are the requirements for gateway installation:
Minimum Requirements:
- .NET Framework 4.7.2 (Gateway release December 2020 and earlier)
- .NET Framework 4.8 (Gateway release February 2021 and later)
- A 64-bit version of Windows 8 or a 64-bit version of Windows Server 2012 R2 with current TLS 1.2 and cipher suites
- 4-GB disk space for performance monitoring logs (in the default configuration)
Recommended:
- An 8-core CPU
- 8 GB of memory
- A 64-bit version of Windows Server 2012 R2 or later
- Solid-state drive (SSD) storage for spooling.
How many gateways are required?
One gateway should be enough for many situations. However, sometimes you would get more benefits from having more gateways. As an example, if you have a gateway that is used for scheduled data refresh, and the same gateway is used for a Live Connection, then you get slow performance for the live connection if there is a scheduled data refresh in process at that time. So, in this scenario, you might consider having one gateway for your Live Connection and another for a scheduled refresh.
The gateway can be installed only on 64bit Windows operating systems.
I recommend choosing the version of the gateway you need on that machine carefully. If this is a server, I highly recommend installing an on-premises standard gateway rather than a personal one.
The gateway machine should always be up and running to cater for data refresh queries.
Do not install the gateway on a machine that is connected through a wireless network. Gateway will perform more slowly in a wireless network.
Ports that need to be open for the gateway are all outbound ports: TCP 443 (default), 5671, 5672, 9350, thru 9354. The gateway does not require inbound ports.
Installing Gateway
You can download Gateway from this link:
Or you can find the link when you log in to Power BI service, under download; Data Gateway;
After running the installation file, you will see the option to choose the gateway type.
For this example, we are going to install the on-premises recommended gateway option because it supports Live connection and DirectQuery connection, which we will talk about it in other posts.
After choosing the gateway type, the installer downloads the remaining files required for installation, and then you can continue the installation. You need to choose the folder to install the gateway. For this example, we’ll keep the folder with no change. The installation process is simple. After installation, you need to register your gateway.
To register your gateway, you need to use your Power BI email account and then sign in.
You can then Register a new gateway or migrate or restore an existing gateway.
Select Register a new gateway, and continue. You will need to enter two important pieces of information;
- Gateway name: a name that can remind you where this gateway is installed. For example, Reza-Vaio-Gateway or something like that.
- Recovery Key: this is a very important key required for recovering the gateway later. If you want to uninstall it and install it again, or if you want to move the gateway from one machine to another without the hassle of changing all connections, then keep the gateway name and recovery key in a safe place.
- You can also add the gateway to an existing gateway cluster. This option is for having high availability through gateways. For this example, leave that unchecked.
After successful registrations of your gateway, you should see a message that says the gateway is online and ready to go.
Now you can see the gateway in the Power BI service under your account as well. In Power BI Service, click on Setting Icon, and then click on Manage Gateways.
You should see all gateways set up under your account. In the screenshot below, you can see that I have a few gateways under my account.
If you are the tenant administrator, you can turn the “Tenant administration for gateways” on and see and manage all gateways under your organization’s tenant, even if you are not the direct administrator of that gateway.
Adding Data Sources
The gateway itself is just for creating the connection from the cloud to the local domain. For your datasets to refresh through this gateway, you need to add Data Sources. Data sources are connections to every on-premises database, file, folder, etc., used in Power BI as a connection.
To add data sources to the gateway, first, you need to check the Power BI file and see what data sources have been used. One easy way of finding that out is to open the *.pbix file in the Power BI Desktop.
After opening the file, then you click on Edit Queries -> Data Source Settings.
In the Data Source Settings, you will see all data sources used in the current file. Click on every data source, click on Change Source, and copy the path for the file.
Now you can go to Power BI Service, Manage Gateway section, select the gateway we installed previously, and then click Add Data Source.
In Add Data Source tab, you need to set some options. The name of the data source is only important for remembering it later easily. The first important option is; Data Source Type. In this example, I choose File because my source is an excel file. However, this can be an SQL Server database or any other data source.
After choosing the data source type, you need to enter other configurations for that source. We have used the file, so we need to specify the full path of the file. This path should be the path of the file from the machine that the gateway is installed on it. If the file is in a shared folder path, then that path should be accessible from the machine that the gateway is installed on it. And this should also be the same path that has been used in the data source configuration of Power BI Desktop.
You need to enter a username and password to access the data source as well. In this case, because we have used a file, the username and password should be the local username and password that have access to that data source from the machine the gateway is installed on. The username should always have a domain name leading it (domain\username), like the screenshot below.
If everything is set up correctly, you should see a message that mentions the Connection Successful.
Important note: If you have multiple sources, then you must do this process for every single one of them.
Set up a Gateway Connection to The Dataset
After adding the required data sources, you can create the connection through the gateway. You should select the data source to configure the Power BI Service. You can go to the Setting of the dataset in Power BI Service.
Click on Gateway Connection. If your gateway has all data sources needed for this dataset, you will see it under Use a data gateway, and you can select it, then click on Apply.
You have now configured your dataset to refresh through this gateway. You can now test it by manually refreshing your dataset. To manually refresh your dataset, first, find it in your workspace.
After finding your dataset, then you can click on refresh. The last refresh time should update with no error if everything is set up correctly. Congratulations! You have set up the gateway for your dataset.
Users and Access Controls for Gateway
There are multiple levels of controls on user access when it comes to gateway setup in Power BI.
Gateway installers
By default, anyone in your organization can install a gateway (they would need a Power BI account). However, as the tenant administrator, you can control this by clicking on Manage gateway Installers.
When you turn on this option, you can choose the group or people who can install gateways;
This is good for organizations with many Power BI users, and the control of the installation of the gateway is better to be governed.
Gateway users
Each gateway can have three types of access for the users.
Gateway Connection Creator
This type of user can create data sources under the gateway and use them for a connection to the datasets and dataflows.
Gateway Connection Creator with resharing
In addition to creating data sources and using them in connections, this user can also reshare access to the gateway.
Gateway Admin
This user has full control of the gateway. In addition to adding and removing data sources, this user can manage access to the gateway, control the settings, and remove the gateway.
Data Source users
In addition to giving access at the gateway level. You can give users access at the data source level. This is more granular access and is helpful when a user only needs permission or access to a few data sources and not the entire data sources under the gateway. The data source users can also have three types of access;
- User; This is just a pure user of the data source. This user cannot change the data source defined under the gateway but can use it to connect to a Power BI dataset or dataflow etc.
- User with resharing; In addition to being the user, this user can also reshare this data source with other users.
- Owner; This user has full control of the data source itself but not of other data sources or the gateway.
You must understand the difference between Data Source users and Gateway users and give access to the users correctly for their use-case. Too much access can sometimes be challenging for a user not trained to use it with caution.
Additional Settings for Gateway
There are a few other important additional settings for the gateway. Here is an explanation of each;
Distribute requests across all active gateways in this cluster
This means load balancing on the gateways. This is very helpful if the gateway is under many parallel requests. When you set up a gateway cluster (a group of gateway installations bundled together to serve as one gateway), Then you can enable this functionality. To learn more about load balancing, read here.
Allow user’s cloud data sources to refresh through this gateway cluster
When you combine multiple data sources in a single Power Query table (when one of the data sources is on-premises and another is cloud-based), then enabling this option will give you that ability. Otherwise, you may need to create two separate queries and combine them. Learn more about it here.
Allow user’s custom data connectors to refresh through this gateway cluster
You can build your own Power Query custom connector and use it through a gateway. You will need to set up the custom connector settings in the installed gateway, and in the service, you need to enable this option. To learn more about it, read here.
Troubleshooting
There are a few scenarios in which you may face an issue when setting up the gateway. Here I explained them one by one;
- You cannot see the gateway listed when you go to your dataset setting. Most probably because you did not add all the data sources needed for that dataset. Go to Power BI Desktop and check whether you have added all data sources.
- Analysis Services Live Connection with gateway requires more configuration, which I explained in another article.
- Gateway has a logging system which can be helpful when an issue comes up. You can enable additional logging and access the gateway logs from the on-premises data gateway installed in the application.
- There are a few known issues with the on-premises gateway, which you can read more about them here: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot
- There are a few known issues with the personal gateway, which you can read more about them here: https://docs.microsoft.com/en-us/power-bi/personal-gateway
Summary
In this section, you’ve learned about Gateway. The gateway connects the Power BI cloud-based dataset and the data source on-premises. You learned that gateway is only required for on-premises connections. There are two modes to install a gateway; personal and recommended (on-premises). You learned that the on-premises recommended gateway can serve more than one developer at a time and be used for Power BI, PowerApps, and a few other applications. It also supports multiple connection types.
We went through the installation and configuration of the gateway and connected one Power BI dataset to it. The key to using a gateway is to add all required data sources under it and then map them to the dataset.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Hi Reza. Great job.
When you put the email “reza@radacad.onmicrosoft”, it’s only login into the gateway.Is it ok? I’ve this similar case. But this, we’ve a problem with the notifications by email. For example, if I need to receveid the gateways notifications by e-mail when we have a problem, to the gatway it’s only login e not e-mail to send something.
Hi Igor,
Thanks for your nice feedback 🙂
to set the email for notification. you can set it in the manage gateway page in the service.
Cheers
Reza
Hi Rezza,
I am an avid follower of your blog posts and really like the way you lay down the steps to explain each and every concept in such clear and simple language. Great work. Looking forward to learning more from you.
Cheers,
Ketan ! 🙂
Hi Ketan,
Thanks for your great feedback. I’m glad your like our content 🙂
Cheers
Reza
Great Stuff Big Big thank to you for sharing valuable stuff
Ajay
I had created a gateway and removed for some reasons. Now i want to recover the same data gateway and i remember the security key given.. How can i recover the removed gateway using my key. Kindly advise.
Hi.
When you go through a new installation, one of the options is to RECOVER/RESTORE, go through that option and enter your recovery key, and continue
Cheers
Reza
Thanks for an all in one post as always.
I had (actually my Network team had) queries related to opening higher ports (5671,72, 9350-54). I read that this is all a standard and required by AMQP and TLS, but nowhere could I find what kind of data gets transferred through these, whether these are active connections or passive.
Would there be any security implications. Can these open ports be used by any other application/entity for malicious intent.
Hi.
These ports are only outbound ports. the security breach happens when inbound ports are open. For gateway; you do not need any inbound ports, so we can say for sure the Gateway connection is a secure connection.
Cheers
Reza
Hi Reza,
Thank you for this great guide. It is very helpful!
I am trying to add a Data Source to my organization gateway that I just set up.
I put in the server name and db as you described above.
When I usually connect to this server/db through my computer using SSMS, I just need to enter the server name and it recognizes me through windows authentication (without manually entering a password). Therefore, I can’t figure out which password to use when setting up the new data source. I put in my Username with the domain and tried adding my windows password and my Microsoft account password but I keep getting an error message:
Unable to connect: We encountered an error while trying to connect to . Details: “We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance.”
Is there a way to solve it?
Thank you!
Hi Ben.
What is the data source? SQL Server DB?
I would strongly suggest using SQL Authentication for that. just create (or ask your DBA to do that) a SQL username and password with the access to this DB, and then use that in the gateway data source configuration.
Cheers
Reza
Great guide!
Thank you
Thank you for your thorough explanation.
I just wanted to clarify – do I understand it correctly, that I can publish pbix files only to Microsoft cloud even if Gateway is installed? I.e. I can not publish pbix file to my local network server?
Gateway is only required IF you want to user Power BI website to host your *.pbix reports (and mainly refresh it, or get the data through live connection or directQuery). and it is only needed if your *.pbix file is sourcing from on-premises data sources.
there is another way to publish your *.pbix files; on-premises, called Power BI report server. for this option, you do not need a gateway.
Thanks! What is the best way to publish your pbix files if you do not want the end user to see and/or change your DAX formulas?
Hi Giedrius
If you PUBLISH the files, and then share with them, they won’t see DAX formulas depends on their access levels. Power BI Desktop and *.pbix should not be used for sharing. Here you can read all about different methods of sharing in my article.
Cheers
Reza
Hi. Great post, thanks. I was wondering whether there is ever a place for dev/test/prod instances of gateways? It seems like it might over-complicate the architecture as (if I understand this correctly) reports and data sources would also need to be replicated though the different instances?
Hi Steve
It is definitely possible to have three separate gateways. However, as you mentioned it brings up more administration work. You need to add all data sources under all three gateways and make sure they are all accessible. separating gateway may be sometimes even a good option as well because if you have a live connection through the gateway in your prod instance, you probably don’t want to consume resources from it and create a bottleneck with queries sent from the dev reports.
Hello!
I just wanted to thank you for this intuitive and convenient guide.
It stresses basically all neccessary aspects needed to get the gateway up and running.
We were trying on our own to get it working but haven’t been successfull.
With you help and your article, I was able to configure the Reports and the Gateway right away without any deeper knowledge or large experience regarding Power BI.
Thanks a lot!
Seb
Hi Sebastian.
Thanks for your nice words. Glad the article helped you out, and you got the configuration setup working correctly.
Cheers
Reza
Can we install a Power BI gateway on virtual machine?
Yes, you can.
Hi Reza
Thanks for that great blog. I´m starting to use PowerBI on MS SQL Server 2014 Analyses Service cubes (SSAS). I see that I can only access Analyses Service in “Data Import Mode”. “Connect Live” mode apparently requires SQL Server Enterprise Edition, while we use Standard Edition as it is four times cheaper.
Do you know if installing the gateway will allow Live connection ? Or will it be in import mode only, loading all on-premise data to the cloud?
Many thanks upfront
Josef
Hi Josef.
Installing Gateway won’t make any difference in having the live connection feature. If you don’t have it without a gateway, you won’t have it with it either. I suggest to upgrade to more recent versions; I think 2016 standard version supports MD live connection.
Cheers
Reza
Hi Reza
This post was really useful! I have a gateway installed on my computer and the refreshes are working as they should be. However, when I am not in the office, the refresh will fail as the gateway is offline. How do I create another gateway instance on a colleague’s computer so that is the primary gateway is offline then it is routed to the secondary gateway on my colleague’s computer?
Many thanks in advance,
Bheeshma
Hi Bheeshma
I would recommend installing the gateway on a machine that is always running, such as a server, rather than a laptop or individual machines.
however, if you still want to do that, you can set it up as a cluster. there is an option for it when you install a gateway.
Cheers
Reza
the use of the On-premises Data Gateway generates extra costs in Azure or power bi?
There are no extra costs for the on-prem gateway, except the fee that you pay for Power BI Premium, or Pro licenses.
Cheers
Reza
Is there any limitations to add no. of data sources through Gateway?
nothing that I’m aware of. I don’t think so
Cheers
Reza
I have a on-premise gateway installed and working in the Power BI Service. However, this is the free service version of Power BI. I would also like to access this same gateway from my organizations Power BI Pro service. However, I can’t see or access it from the Pro service. Any suggestions
Have you installed the gateway in on-prem RECOMMENDED mode? because personal is also another mode, and can’t work on this way. you need the recommended mode.
Also you need to make anyone else who wants to have access to the gateway admin or Manage gateway panel to be gateway administrator,
and also everyone who uses the gateway to be the data source users
Cheers
Reza
Are there any licensing cost involved to connect the POWER BI to SAP BW?
There is no licensing for Connection. all of them are free. However, when you want to share the report and host it in an organizational workspace, that time is when the licensing comes into consideration.
Thank you so much, its very helpful.It shows that my dataset has been configured but when I refresh I get “Invalid connection credentials” error. Please assist, the data source is an excel path.
Hi Amanda
Does the account you have provided in the credential, has access to the Excel file path?
Cheers
Reza
Hi Reza,
i am using On – Premises Gateway to connect to my SSAS Tabular Cube Live.
Gateway is all setup and it works fine, but sometimes i get this error ” There was a data source access error. Please contact the gateway administrator”. when i get this error i have to restart my server and it starts working.
i have raised a ticket with microsoft and they tired several Steps but nothing worked.
Hi Karthik
There are many things that can cause this issue.
You might have the SSAS server not working on those times, or the load from users at those times are high, and that creates a bottleneck under the server which it cannot resolve, etc. To give you a precise answer, I need to look at the configurations in details. Checking the SSAS log would help to see when that happens.
Cheers
Reza
Hi Reza,
Is it possible to access the drive/folder on other server when Gateway is installed on one server. If yes, how ?
Yes, you can access the folder on other servers too, but that folder should be shared, and you should be using UNC path for it.
Cheers
Reza
I created cubes using SSAS on my local machine . I’m successfully connected to ssas with power BI desktop . The thing is I had problems when trying to publish my reports to power BI service . I setup the gateway successfully using the PowerBI service and connected to the database using my local login credentials . I’m connected to power BI and to the entreprise gateway using my office 365 account . when publishing the reports from power BI desktop I got success but when trying to access the report on the power BI service , an error MSG appears : “there was a data source access error .Please contact the gateway administrator .”
Hi Victor,
there can be some reasons for this. I’d check below items:
What is your gateway user (the account that runs the gateway service in the local domain machine) access to the SSAS?
What version of SSAS you are using?
are you using Live Connection or Import Data?
Does the Power BI user has access to the SSAS cube? are they at the same domain? if not, have you mapped UPN mapping table?
Cheers
Reza
I see that you recommend 64 bit Windows Server for the gateway, but is it possible to install the recommended gateway on a machine running Windows 10 Home and to properly connect to it? This is really just for learning purposes. I had tried to do this and the gateway was online but I could not connect to my data source. It said my credentials were wrong but I had entered them in the format that you indicated above. However, I noticed that in Network the gateway seemed to be under NT USER\something. I uninstalled the gateway after that and removed it from Power BI online, but then I found this post and thought you might know what was wrong. Thanks for your response!
You CAN install the recommended gateway on a windows 10 home too. and it should work just fine.
I guess probably the account that was running the service, or the account used to create the data source had the problem. I’d try to use an admin account for both to see if there is still a problem
Cheers
Reza
Hi Reza, Is there an additional hidden charge for the Azure Service Bus when using Power BI Cloud service connected to On premise data source? Thanks
Hi Parul
No. there is no other services, or licenses required to use the Power BI on-premises gateway, nothing other than the Power BI license
Cheers
Reza
Great content. Thanks
Cheers
Titus Korir
Excellent Stuff 🙂 Many Thanks for sharing.
Thanks for sharing! This is super helpful!
Hi Reza
Your article has made many things clear to me. Thanks!
But I still have a doubt. My intention is to create a connection to a SQL Server database. That server already has security implemented (schemes, user groups, etc.).
Do I need to create a Datasource for each profile?
Like, a Datasource for Sales, another for Accounting, one more for HR and so on.
Hi CLaudia
I am not sure how this question is related to the gateway. However, if you want to create different schemas in your database for Sales, Accounting, HR, certainly you can do that.
Cheers
Reza
Hi Reza
Your article has made many things clear to me. Thanks! you written stepwise its so simple to understand, great job.and great work. Looking forward to learning more from you
Thanks Rupalli
I am glad it is helpful
Can we use power BI data gateway as data source to use on cloud for other application using SQL query?
Hi Raj
not with the data gateway itself
but you can use Dataflow to get that data, load it into a Datamart, Lakehouse or Warehouse, and then use that SQL Endpoint in other applications using SQL Query.