On-premises Gateway for SQL Server Instance

[CeeVee33]

Hi Experts

I'm looking to create a connection to an On-Premise instance of SQL server from the On-Premise Data Gateway.

So the SQL Server sits on a server ABC_PRD_000560 and it is an instance called I_M_INSTANCE. 

I connect to the DBs using ABC_PRD_000560\I_M_INSTANCE in SSMS.

For Auth - I'm using win Auth on a service account. 

I am unable to create a new connection on On-Premise Data gateway.

I am unable to use SSMS on the gateway server to connect.

In both (locally and via new PBI portal connection ), I get below error -

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: 26 - Error Locating Server/Instance Specified). 

Thank you 

[AndyDDC]

Hi @CeeVee33 is the SQL server instance you're trying to connect to a named instance?  If so can you check if TCP/IP is enabled and the SQL Serrver Browser Service on the server is started?

Is there a default instance installed on the SQL Server?  If so are you able to connect to that using SSMS from the gateway server?

[CeeVee33]

Hi @AndyDDC - Yes, trying to connect to named instance. I do not have enough access to check TCP/IP settings. I can connect to it from my laptop, so I guess Browser Service is on.

There is no default instance installed.

Do I need to get the named instance's port open?

[v-hashadapu · Accepted solution]

Hi @CeeVee33 , Thank you for reaching out to the Microsoft Community Forum.

Please consider below:

1. Ensure TCP/IP is enabled for the SQL Server instance in SQL Server Configuration Manager. Since you mentioned limited access, request your database administrator (DBA) to verify this.
2. Test Connectivity from the Gateway Server. the port may be blocked by a firewall or network policy. Also, Ensure SQL Server is configured to allow remote connections.
3. Ensure the SQL Server Browser Service is running, as it helps resolve named instances to their correct ports. If this service is stopped, one may not find the instance unless a static port is used.
   SQL Server Browser service
4. Named instances use dynamic ports by default, meaning the port can change. Ask your DBA to find the current port. If possible, configure a static port and ensure the firewall allows inbound traffic on that port.
   Configure SQL Server to listen on a specific TCP port
5. If using a dynamic port, find the actual port using SQL Server Configuration Manager or DBA. Connect using ServerName,PortNumber to bypass SQL Server Browser.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[CeeVee33]

It worked by opening the dynamic port of the named instance. However, I'm scared on when the port changes, hope it is never.

[CeeVee33]

Hey @v-hashadapu - thank you!!

I've asked questions and raised port open ticket. I'll ping here if I can not get it resolved through it.

Thanks 

[v-hashadapu]

Hi @CeeVee33 , thank you for reaching out to the Microsoft Fabric Community Forum.

Thanks for the update. Hope your issue gets resolved soon. when it does, please share the insights here and mark it or any other helpful answer 'Accept as Solution', which will help others with similar queries.

Thank you.