SQL Server remote connection not working

Question

SQL Server remote connection not working

Rory Folster 1

Hi all, I have been trying to connect to my SQL Server instance remotely for hours now.. I have set up the configuration correctly inside of SSCM, allowing connections and changing the TCP to 1433. I have added Windows Firewall settings for both TCP and UDP using 1433, I have port forwarded TCP and UDP using 1433, I have enabled remote connections in SSMS.

I am able to connect to the instance using either the instance name or the local ip address, but not the public ip. I am using a home network.

I can see the port is open, using www.canyouseeme.org.

I have restarted the MYSQLSERVER service, and the Server Browser, I have even restarted my PC.

I am stuck as I have followed multiple tutorials but they all contain similar instructions, which as far as i know, are set up correctly.

Any help is much appreciated!

PandaPan-MSFT 1,931 Reputation points

2022-11-17T03:05:35.29+00:00

Hi @Rory Folster ,
Could you provide more information like error information? Cuz now that you TCP/IP is set correctly......It's very confusing. And pls tell me the version of sql and what driver you are using to do the connection.

1 answer

Your answer

PandaPan-MSFT 1,931 Reputation points

2022-11-17T03:05:35.29+00:00

Hi @Rory Folster ,
Could you provide more information like error information? Cuz now that you TCP/IP is set correctly......It's very confusing. And pls tell me the version of sql and what driver you are using to do the connection.

Answer 1

Dan Guzman 9,401

If I understand correctly, you are running a SQL Server instance on your local home network and you cannot connect remotely from the internet.

To verify network connectivity, run the command below from a command-prompt on the remote machine, replacing "127.0.0.1" with the actual IP public address you are trying to connect to.

powershell -Command "tnc -ComputerName 127.0.0.1 -Port 1433"

If the above fails but www.canyouseeme.org succeeds, it suggests your public IP and/or port 1433 is not allowed from the remote machine.

Note that the SQL Server Browser and UDP are not involved when you specify only the IP address or host name in the connection string. Only port TCP 1433 is used in this scenario.

Rory Folster 1 Reputation point

2022-11-16T12:26:04.007+00:00

Hi there, thanks for the reply. I have just executed the command successfully. 'TcpTestSucceeded: True'
Rory Folster 1 Reputation point

2022-11-16T22:12:14.587+00:00

@Dan Guzman I have just executed the command successfully. 'TcpTestSucceeded: True'
Dan Guzman 9,401 Reputation points

2022-11-17T11:37:07.087+00:00
@Rory Folster , that verifies you have port connectivity. Try a SQL connection with the command below and post the error message, if any. If this succeeds, post the error you get from the application.

powershell -Command "try {$c = New-Object System.Data.SqlClient.SqlConnection('Data Source=127.0.0.1;User ID=YourUser;Password=N@YourPassword');$c.Open();$c.Close();Write-Host 'Success'} catch {throw;}"

Share via

SQL Server remote connection not working

1 answer

Your answer