Data Gateway error

[Anonymous]

Hi

My client has moved their SQL-server to a new IP-address. After this I can not get the data gateway to work. It works in the desktop and after publishing I want to configure the datagateway with the new SQL-server address and port no.. And I get an error, see below.

So to recap: it works fine in the desktop when I refresh but not in the server. What could be the problem. According to my client the server is sitting on a whitelisted IP, just as my own desktop.

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details
Activity ID:	a486738a-0a3b-4f48-84e5-503e3f6c25e1
Request ID:	01f22066-a60f-c60a-b937-55c7758921be
Cluster URI:	https://wabi-europe-north-b-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Tue Dec 17 2019 09:03:51 GMT+0100 (Central European Standard Time)
Service version:	13.0.11747.192
Client version:	1912.2.023
Spring Gateway:	SQL Server connection failed
Underlying error code:	-2146232060
Underlying error message:	A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)
DM_ErrorDetailNameCode_UnderlyingHResult:	-2146232060
DM_ErrorDetailNameCode_UnderlyingNativeErrorCode:	87

[Anonymous]

Hi @Anonymous ,

Make sure your server is running.
Also, you need to add your source to gateway under Setting->Manage gateway->Add datasource

Regards,
Amit

[Anonymous]

Server is running and I have added datasource. It is actually when testing it I get the above error. 

[Anonymous · Accepted solution]

Found the error. It turns out that when providing a Port-no it has to be preceeded with a "," and not a ":" as in the desktop (and elsewhere).

[SidraM]

@Anonymous How did you change ":" to ","? The option is greyed out for me when I open my dataset on Power BI service. Thank you!

UPDATE: I was able to change it in Power BI desktop and publish the dataset again. Thank you so much for sharing your solution! I spent DAAAAYS trying to figure out why I wasn't able to connect.

[bo_afk]

Hi @Anonymous  @SidraM, I'm also getting the same issue, where can I find the option to change the Port-no?

[SidraM]

Go to File > Options and settings > Data source settings

From there you should be able to update port# using Change Source option.

[bo_afk]

I'm trying to set up a gateway within PowerBI service within one of my Gateway clusters but I don't seem to be able to see this option in there?