Power BI Gateway Connections - Changes to Server ID

[hawkerr1]

Hi all,

My current client is a heavy user of dataflows. The dataflows are connected to various SQL server databases via Gateways.

The current connections are via public end points - i.e. server names ending "database.windows.net".  We have been asked to change these to private end points - i.e. the server names no longer end with .net. This is a change for security purposes.

I am struggling to see a way to do this efficiently.

• The UI for datasource settings does not allow you to change the server or database IDs.
• You can only change the authentication / connection name etc. 
• I thought about using the API to update gateway datasources but that also does not seem to allow the server name to update.

The only solution I can see is to create new connections with the new server name and then migrate the dataflows from the old datasource to the new datasource.

• Presumably we would use the Admin - Get Dataflow Datasources as Admin API to get all the dataflows which were associated to the data sources we needed to swap out in favour of the new one.
• We would then use another API to update the dataflows to point at the new Gateway data source. 
• I've looked at the Update Dataflow API but that doesn't seem to update data sources. Is there any other option?

Do I have to do this dataflow by dataflow!?? (eek if so!).

I hope my question is clear and I would welcome any ideas - even if a total departure from what I've tried so far.

Thanks,

Rob

[hawkerr1 · Accepted solution]

Thanks for the reply. It was helpful, but didn't really cover the problem.

The problem is that I didn't want to have to edit hundreds of dataflows one by one. 

It seems that this is the only way and it is good to have that confirmed at least. I will raise a Power BI idea for the possibility of an API which allows editing of Datasources in Dataflows. I understand why the data sources on the gateway can't be edited - because each dataflow needs to be edited in parallel.

[Anonymous]

HI @hawkerr1,

Perhaps you can try to parameterize query connection string with query parameter on desktop side. Then you can then use this parameterized query in Dataflow by creating a new source transformation and selecting the parameterized query as the source. 

After these steps, you can change the parameters on the source to dynamic effect the Dataflow data source mapping.

Regards,

Xiaoxin Sheng