Changing Data Gateway on an existing DataFlow

[Imrans123]

HI All, 

Was wondering if anyone had a similar situation. I had a SQL Server running on a computer with on-premise gateway installed. Created multiple tables in a dataflow and everything was working fine. Unfortunately, the computer which was running the SQL Server changed as well as the Authentication for getting into SQL Server.

I installed the On-Premise Data gateway on the new server. When I head back to dataflows, hit Get New Data >SQL Server source, I populate the SQL Server Name, select the new on premise gateway from the dropdown, give in the new authentication for SQL Server. I get a warning that changing the gateway will affect all the existing tables within the data flow which I am fine with

However, when I hit next, it gives me an "Unexpected error" Any fix for this??

EDIT: If I try to create an entirely new Data Flow, the error message it gives me is "Invalid Credentials". Even though I've meticulously tested loggin into SQL Server and querying tables

[v-rongtiep-msft]

Hi @Imrans123 ,

I am sorry that I cannot reproduce your issue. I can change the gateway directly.

Could you please tell me what is your Authentication kind?

Could you please check the information as below. Can you  sign in the Authentication kind in SQL Server? And if the gateway online?

If these are not problems. I think the best way for you is to add dataflow to new gateway. and then reset related credentials, change the selection of datasource(map to new one) and apply the setting in the dataset. I did a test, the reference is as follows:

If the problem is still not resolved, please provide detailed error information screenshoot. Looking forward to your reply.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.