Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
We've had the personal gateway (PG) installed on the server to allow for refresh of the on-premise SQL Server data. Now with the release of the Enterprise gateway (EG) I can see no reason to keep the PG but want to replace it with the EG. Both are installed on the server (which should be fine) but under Gateway setting only the EG is listed. However since all datasets that are created were created when the PG was used, they are all setup with the PG as gateway under "Gateway connection" in the dataset settings page. The option "Use an enterprise gateway" is disabled and cannot be selected.
I was told that there is no way to move from PG to EG for a dataset but that I need to delete the dataset and re-create it in order to change the gateway used. Deleting a dataset will of course delete all reports, tiles etc. that use that dataset so this is not a good option.
So questions:
A) Why is the "use an enterprise gateway" option disabled although it is installed, configured and working OK
B) Is the only option to move from PG to EG really to delete all the datasets?
C) Is there ANY reason nowadays to use the PG over the EG?
Thanks
Fredrik
Solved! Go to Solution.
Hi Fredrik,
Few things to explain before answering your questions below:
- In order to start using the Enterprise gateway, you need to go to Manage Gateway page and add your SQL server connection there, where you add server,DB name and your credentials that you want to use. Now after you do that you can go to your dataset again and the Enterprise Gateway should show up. Make sure that the data source information you add under the gateway matches exactly what you have on that dataset. No additional work needed to move to using from the Personal Gateway to the enterprise one. More info can be found here: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-sql/
- "Manage Gateways" UI will only show the enterprise gateways, it doesn't show the personal gateway, since you could only have one.
- You are right: there is no way to move from using the personal gateway to the enterprise one. The only way to do it is to add the data sources to the new enterprise gateway one by one.
To your questions:
A) Why is the "use an enterprise gateway" option disabled although it is installed, configured and working OK
B) Is the only option to move from PG to EG really to delete all the datasets?
C) Is there ANY reason nowadays to use the PG over the EG?
Hi All,
Just a note. After fighting this all yesterday I have managed to get it to work, procedure used as follows:
NB I use Postgresql so I had no option to use the standard connectors, so I had to setup OBDC first so some of these tasks only refer to connections with Postgresql.
It is really annoying that MS have made people jump through these hoops just to get functionality that should be default.
We just went through this exact issue as well when trying to migrate from the peronal gateway to the on-premise gateway.
The solution described below (make sure the server name and DB name match 100% between the data source you add under the gateway and the ones you used to establish the connection in your report) DID NOT resolve our problem. Case-sensitivity was a red-herring as well. (Note, we're using an Azure SQL server.)
Our solution required that we establish the connection to both the server and the database in a single call:
================= On Premise Gateway configuration: =================
Server: CSG-SQL.cloudapp.net,57500
Database: csgODS
================= OLD SYSTEM, which worked using Personal Gateway: ================
Source:
let
Source = Sql.Databases("CSG-SQL.cloudapp.net,57500")
in
Source
All other queries were prefaced with these two lines:
let
Source = Source,
csgODS = Source{[Name="csgODS"]}[Data],
...
================ NEW SYSTEM, which works using the On-Premise Gateway: ================
Source
let
Source = Sql.Database("CSG-SQL.cloudapp.net,57500", "csgODS")
in
Source
All other queries now just start with this line:
let
Source = Source,
...
I re-configured EG with required data sources but could not remove PG as its been grayed out. Looks like I am into similar situation like you said above and need to configure same data source through EG. How can we identify the data sources configured with EG on my profile?
I have the same issue, despite removing Personal Gateway from the computer that it was installed on, installing the enterprise gateway and conecting to a data source, testing conections which showes as being good, the issue above is still there. Under Gateway conection, it is showing - Use your data gateway (Personal Power BI). However, below this Use Data Gateway is greyed out. I have tried upper and lower case for the databse name, tried to use the server.domain_name but this was not accespted as a conection and not sure what else to try. Does anyone know how else I might try to remove the personal gateway. We are using an on premises SLQ Server.
dcresp
Did you ever get a solution to this problem? I am stuck with the same issue.
And us - did this get solved ?
Our issue is that we have converted and now found that we can't mix on-premises files with Cloud data such as Dynamcis within the same dataset that updates. !!
Yes, this issue has been solved. If the enterprise gateway does not appear as an option in "Tools--Settings--Datasets," it means that the one or more of the "Data Sources" in your cloud data set is either not listed or not accurately described. If you follow this prescription you should find the problem:
1. In your PBI Desktop, go to Edit Queries (Home ribbon, External Data section). You will see each one of your Data Sources listed along the left side. For each one of the Data Sources, do the following:
2. In the Query section of the ribbon, select "Advanced Editor." In the line starting with the word "Source," you will see the description of the Data Source. The information in between the quotation marks is the critical information you need.
3. In the cloud data set, go to "Tools--Manage Gateways" and select the down arrow next to the name of the gateway. You should now see a listing of each Data Source. There must be a cloud Data Source for each Data Source in the desktop and the data source descriptions (the information in between the quotes) must match EXACTLY. If all the Data Sources are listed and the connection information is accurate, the Enterprise Gateway should appear in the Settings.
When I was pulling my hair out with this issue, I had two Excel data sources that had similar names and one of the connections was not going to the proper file. It can be frustrating to troubleshoot this. In the end, I deleted all of the connections in the cloud data set and added them back in one by one making sure that I listed each source in the Desktop and meticulously copied the data source connection information (in between the quotes). (Note that for a SQL data base connection there will be two pieces of information in between quotes, "server" and "database name".)
Hope this helps.
Hi,
Had worked that part out already.
Our main issue is that we have mixed datasets, pulling from a mix of excel files, On-Premise and Azure SQL databases, and finally Dynamics CRM in the Microsoft Cloud.
The first ones work fine, but we have had to breakout the Dynamics feeds into new datasets as they use ODATA to connect that doesn’t seem to be supported within the Enterprise gateway.
That is annoying as it was within the personal one.
Regards
Mark
Hi, you are so right Mark!
I doesn't make any sense why OAuth2 for OData is supported in the Personal gateway and not within the Enterprise gateway.
I took me a while to figure this out and even longer to explain to my customers IT department the need for a Personal Gateway for an online source 😞
Hope Microsoft will fix this Gateway mess and align it with the real world.
Regards
Jeppe
My Issue resolved. All I did is, uninstalled the PG using CCLEANER, (standard msi uninstallation did not help here) and re-configured the PG through another box and found all reports are in sync per the schedules configured.
So to fix the issue with PG, a clean uninstall would do. Since msi uninstallation continues to fail - use CCLEANER and then re-configure PG and the box/server we want.
Hi Fredrik,
Few things to explain before answering your questions below:
- In order to start using the Enterprise gateway, you need to go to Manage Gateway page and add your SQL server connection there, where you add server,DB name and your credentials that you want to use. Now after you do that you can go to your dataset again and the Enterprise Gateway should show up. Make sure that the data source information you add under the gateway matches exactly what you have on that dataset. No additional work needed to move to using from the Personal Gateway to the enterprise one. More info can be found here: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-sql/
- "Manage Gateways" UI will only show the enterprise gateways, it doesn't show the personal gateway, since you could only have one.
- You are right: there is no way to move from using the personal gateway to the enterprise one. The only way to do it is to add the data sources to the new enterprise gateway one by one.
To your questions:
A) Why is the "use an enterprise gateway" option disabled although it is installed, configured and working OK
B) Is the only option to move from PG to EG really to delete all the datasets?
C) Is there ANY reason nowadays to use the PG over the EG?
Hi,
I have followed these steps but when I refresh it is blocked and I'm asked to "rebuild this data combination" for some Queries.
Thanks
Thank you!
The reason why the EG option was disabled was because the server name was not identical as the one in the personal gateway. In the PG I had servername.domain and in the EG only servername. Once I created a new EG datasource with servername.domain as servername the EG option is now available to select when configuring the gateway for the dataset.
Follow-up questions
1. Can I be confident that it will be OK to switch to the EG now that the option is enabled or is there any other caveats?
2. If I proceed and change the gateway for all configured datasets I should be OK to uninstall the personal gateway from the server, right?
Thanks again
Fredrik
hi @Fredrik,
The answer is yes for both. Just make sure the data sources you're using with Personal Gateway is currently supported in the EGW.
@Fredrik wrote:We've had the personal gateway (PG) installed on the server to allow for refresh of the on-premise SQL Server data. Now with the release of the Enterprise gateway (EG) I can see no reason to keep the PG but want to replace it with the EG. Both are installed on the server (which should be fine) but under Gateway setting only the EG is listed. However since all datasets that are created were created when the PG was used, they are all setup with the PG as gateway under "Gateway connection" in the dataset settings page. The option "Use an enterprise gateway" is disabled and cannot be selected.
I was told that there is no way to move from PG to EG for a dataset but that I need to delete the dataset and re-create it in order to change the gateway used. Deleting a dataset will of course delete all reports, tiles etc. that use that dataset so this is not a good option.
So questions:
A) Why is the "use an enterprise gateway" option disabled although it is installed, configured and working OKB) Is the only option to move from PG to EG really to delete all the datasets?
C) Is there ANY reason nowadays to use the PG over the EG?
Thanks
Fredrik
To answer your third question, the personal gateway was and still is intended to be installed on a workstation and not a server. It is primarily used for refreshing personal datasets such as an Excel file.
I'm experiencing similar issues with trying to switch over to an Enterprise Gateway as well, but my problems are bit more troublesome because for some reason I can't set up my Oracle database as a datasource even though I can connect to it through Power BI Desktop.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
27 | |
14 | |
14 | |
12 |