On Premise Data Gateway - Can only connect to SQL Server if I open 1433 TCP In

[markive]

Dear All,

I've read just about every thread on here regarding OnPremise GWs.

- I have an Azure Windows Server 2016 VM running SQL Server 2017 (Default instance port 1433)

- I have installed On-Premise Data Gateway on the VM, created one in Azure and connected the two.

Everything works fine if I turn off Windows Firewall Completely on the VM, or if I create an inbound rule allowing all 1433 connections on any IP address. But it stops working as soon as I turn off this rule. I have created a rule that allows my work computer to connect to SQL Server instance over 1433 limited to that IP address.

I have tried opening all outbound ports required by Azure Service Bus, I have tried On-Premise Data Gateway https only mode on/off

My understanding is that the Gateway continually polls Azure Service Bus for requests, when there is one from PowerBI on the Queue it will decrypt the credentials, send the query to SQL Server.

Why do I need to open inbound 1433, the documentation as I understand it says that only outbound ports need to be opened?

There's so much information out there but no-one with this exact problem it would seem.

[markive · Accepted solution]

I was able to fix the issue. For anyone else who may get confused if they have a similar setup.

I was connecting to my SQL Server VM by IP Address. When the Enterprise Gateway took over in production, it was decrypting the connection credentials and trying to connect, the connection would fail as it was trying to connect to SQL server via it's own external IP address, which wouldn't work.

If I changed the conn string to (local) or 127.0.0.1 it works straight away. The way I will move forwards is to setup a dns entry like sql1.mydomain.com so that I can develop the reports locally. I will then setup a hosts file entry on the SQL Server VM to point sql1.mydomain.com > 127.0.0.1 so it will work in production too.

[v-yuezhe-msft]

@markive,

Do you install SQL Server and on-premises gateway in same VM or different VM?

1433 TCP port is used by SQL Server, which has no relation with the outbound ports required by Azure Service Bus. If you install gateway on a different VM from the machine installing SQL Server, you would need to create inbound rule for 1433 in the SQL Server VM to make the remote connection between gateway VM and SQL Server VM successful.

Regards,
Lydia