Issue with adding gateway to cluster

[Jalindo]

Hi everyone,

We've run into an issue with adding a new server and creating a gateway cluster. The original server is working properly by itself. When we try to add the new server to the cluster, it adds successfully and the cluster shows as online, including both gateways showing as online. However, when any refreshes run, the queries that go to the new server return errors.

For example, we have datasets that refresh successfully and have correct credentials when refreshing through the original gateway. However, once we add the new gateway to the cluster, SQL Server datasets start getting the error "DM_GWPipeline_Gateway_InvalidConnectionCredentials" and "The credentials provided for the SQL source are invalid" even though we haven't changed the credentials and they work fine on the other gateway. Other datasets start reporting "AccessUnauthorized" for Windows credentialed SSAS queries, but again no credentials have changed and only a new gateway was added to the cluster

When I test the connections on the cluster after adding the new gateway server, many (but not all) of them show "Partially Online). I've gone through the gateway requirements documentation and can't find an obvious problem.

The gateways are both on the same gateway version, but the servers are different in OS version and sizing. However I didn't see any mention of OS version or sizing differences being an issue in documentation.

Both gateways are joined to the same Azure AD and pass network port tests. They are on different Azure subscriptions and resource groups, but same Azure region and tenant.

Mainly trying to determine if the issue is because the two servers aren't similar enough to work together, or if it's a networking issue with the new server, or if it's a separate issue entirely.

[Jalindo]

Thanks for the reminder on SSMS. I was focused too hard on the gateway configurations and firewall to think of just testing with SSMS and resolve any issues there first. That should do it.

[lbendlin · Accepted solution]

They are on different Azure subscriptions and resource groups, but same Azure region and tenant.

wait what?!  Enterprise Gateways are supposed to be on-premise VMs, not Azure.  You should be using VNet gateways for Azure.

On the new cluster member install SSMS and verify that you can see all the databases from there.

[JasonWelch]

I might be mistaken but isn't that still only in preview?  And only in some regions?

https://learn.microsoft.com/en-us/data-integration/vnet/overview

And we've been using Azure VMs to provide secure gateway to Azure PaaS since 2018:

https://azure.microsoft.com/en-us/blog/securing-the-connection-between-power-bi-and-azure-sql-databa...

[Jalindo]

We're doing the same, Azure VMs for gateways.  We tested the connections via SSMS and ended up standing up two identical VMs on the same Vnet, same subscription and same configuration, then clustering those together. It looks like the vastly different OS versions between the two were causing compatibility issues. Either that, or the different vnets were causing firewall issues with our data sources. Either way, it's working better now.

[JasonWelch]

Ah great, thanks for replying, that's interesting to know.  We're wanting to automate joining the second VM into the gateway cluster but it seems you can't do it using the PS cmdlets.  Did you install yours manually or have you managed to automate?  (I've asked separately here)