Moving from personal gateway to the enterprise gateway

[Fredrik]

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

[jonnyboy101]

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.

1. Delete PBI Personal Gateway, Install Enterprise Gateway.
2. Find and install PostgreSQL Unicode(x64) OBDC driver - I used (psqlodbc_09_06_0200-x64)
   1. I installed it as a DSN but this later turned out to be a wrong move, but you can at least do this and use the test connect function to make user the driver is installed correctly.
3. Go to PBI Web app > Settings > Manage Gateways > New Data Source
   1. Fill in your connection details, including the connection string
      1. https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
   2. Save and test connection.
   3. Copy connection string.
   4. DELETE ALL ON PREMISE DATASETS from the right hand menu, I know its a pain but I tried doing this without deleting them and it just wouldnt work.  You can export the PIBX if you want to backup before deletion.
4. Open PBI desktop file > Edit Queries > Advanced Editor   You will see SQL code, the top line shows the data source, this needs to be changed.  Now paste in the connection string you used earlier. Do this for ALL the tables your report accesses. 
   1. Save report
   2. Upload report to PBI web app
5. Navigate back to PBI web app then open up the dataset settings, you should now have the option to change the gatway from personal to enterprise.

It is really annoying that MS have made people jump through these hoops just to get functionality that should be default.

[JenM]

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,
    ...

[Sreekanth]

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?

[dcresp]

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. 

[MojoGene]

dcresp

Did you ever get a solution to this problem? I am stuck with the same issue.

[dcresp]

No I am still stuck as well.

[mdawesdigitalk]

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. !!

[MojoGene]

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.

[mdawesdigitalk]

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

[jeppesal]

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

[Sreekanth]

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.

[dimazaid · Accepted 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

• you need to add the data source from manage gateways.

B) Is the only option to move from PG to EG really to delete all the datasets?

• You do not have to delete the datasets, just 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.

C) Is there ANY reason nowadays to use the PG over the EG?

• Yes, use cases are different: in the personal gateway you can run it on your personal laptop without having to be an admin on that machine, you can use one gateway for all your datasets. The downside is you cannot share the gateway, you cannot have a centralized control, and you cannot establish a direct query in the persona gateway.

[Sergec67sch]

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 

[Fredrik]

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

[dimazaid]

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. 

[wonga]

---

@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 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

---

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.