Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
markive
Advocate II
Advocate II

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

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.

 

 

1 ACCEPTED SOLUTION
markive
Advocate II
Advocate II

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.

 

 

View solution in original post

2 REPLIES 2
markive
Advocate II
Advocate II

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
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors