Cannot see how to reconfigure the on-premise gateway after migrating to new server

[Robert804]

We have migrated our database from a local inhouse server to an AWS EC2.  We re-installed the On-premise gateway and used the restore key during the installation.  When we check the gateway it does show it is online, but our report unable to connect to SQL server.  I have tried many searches to find direction for changing the SQL Connection string and have been unsuccecssful.  I am hoping someone can point me in the right direction on where I can find information on how to update this in the On-premise Gateway.

Thank you

Robert

[Greg_Deckler]

@Robert804 So, you don't do it there. It's in the Service, gear icon, Manage connections and gateways. Except you can't actually edit a connection once created other than the credentials. So basically what you need to end up doing is go back to the original report in Desktop. Edit the SQL Connection and republish and create a new connection.

[Robert804]

Does that Desktop version need to be running in the EC2 environment or from the original location.  I suspect this needs to be in the new server environment since this is where the DB resides.  Is that correct?

[Greg_Deckler]

@Robert804 Power BI Desktop can be running anywhere assuming you have network connectivity to your EC2 environment but you would reconfigure the connection to the database to be whatever it is in the EC2 environment. You need to edit your Source step most likely.

[Robert804 · Accepted solution]

I have finally been able to resolve this issue.  The problem was we upgraded to SQL 2019 and the DB connection was working in an encrypted mode.  I needed to download and run Power BI desktop on the new server and re-configure the connection.  

Seems that by the system was looking for an encrypted connection and SSL certificate.  I needed to use File-.Options and Settings->Data Source Settings to modify the source and ensure the "Encrypt Connections" option was unchecked.

I was now able to refresh the report data and re-publish it.

All working properly now.

[Robert804]

Thank you for your guidance.  I agree this is the root of my issue.  Problem is when I look at my source it is very simple and does not show it to be through the On-premise Gateway.  When I click to edit these setting I do not get an option to select an on-premise gateway.  How do I change this to be my On-premise Gateway?

Just to clarify, the DB and On-premise gateway were local on our on-site network.  Now the DB and On-premise Gateway are in the EC2.  I can see the gateway is online, but I do not see any options to add this gateway to my project.

[SaiTejaTalasila]

Hi @Robert804 ,

Create a new connection under that gateway and map your datasets to that gateway in dataset settings/dataflow settings.

If everything is working fine then you delete the old connections which are using personal mode gateways or any other gateways.

I hope it will be helpful.

Thanks,

Sai Teja 

[Robert804]

Thanks for the assistance, but I do seem to be missing something simple.

When I am in the on-line service for Power BI (Power BI), I can see my on-premise gateway.  when I click the info icon, it does show the correct server information.  Also, when I click the Status, it is showing as 'Online'.

I can easily create new connections for this On-premise Gateway.

My dataset is using the semantic model.  When I review the 'Gateway and Cloud Connections' I can see the cluster name, however, it is showing the old server address. When I select the 'Maps to:' drop down, I only see the option to 'Add to Gateway'.  I run through this and nothing seems to change.  The issue seems to be the only option I have is the old server name.  If I enter the new server name, it does not show-up as an option in the 'Maps to:' selection.

At this point I seem to be stuck only able to pick the old server.

I hope this help you understand my situation better to see if there is any other guidance you are able to offer.

Very much appreciated!