Can’t connect to SQL Server through SSMS remotely

Posted on

Question :

Ok, look apologies if this is a really stupid question, but I’m struggling here.

Just started a company and they use VM’s hosted externally for SQL Server. You access the VM by RDP by putting in an IP address and a port number; e.g. 12.34.56.789:1234.

How do I connect to an instance on this server using SSMS on my local machine?

I’ve reviewed multiple help articles online. I notice my SQL Server is set to TCP Dynamic Ports, and not a static port like 1433. Windows Firewall is turned off so no issue there. I’ve checked the netstat -ano and see that TCP is listening on:
[::]:49166 and
I’m able to telnet to the server using the 12.34.56.789:1234 address, but not able to telnet using the 12.34.56.789:(dynamic port address).

Am keen to even try to connect to it through the dynamic port but can’t get through to it either.

SQL Browser is on.

Thank you in advance for you help. Going mad here!!

Answer :

As already mentioned, you would almost certainly have other firewalls active. For example the one that translates 12.34.56.789:1234 to server1:3389 and translates 12.34.56.789:5678 to server2:3389.

That’s basically a firewall doing that (it’s called NAT).

RDP is a service that runs on port 3389 (by default). SQL Server is a service that runs on a port (often 1433). So you need to use exactly the same means to allow outside users to connect to SQL Server.

The only thing is that you probably need to configure your SQL Servers to run on the same port every time. That means configuring it to listen on a static port (the same port every time), rather than a dynamic port (the port changes randomly every time it restarts).

And once you’ve done that you also need to configure SQL Server so that it actually listens for remote connections. All of these are security configurations so that your vanilla SQL install is not a security risk.

Windows Firewall is turned off so no issue there.

There will be some form of firewall operating between the machine and the outside world (unless there is something really wrong with your company’s infrastructure setup!) and that is most likely blocking access to the relevant ports for external connections.

How do I connect to an instance on this server using SSMS on my local machine?

It may be that you cannot access the SQL Server directly at all, in which case you’ll need to use tools installed on the VM(s) rather than local copies. It may be that there is a VPN seetup that you can connect through. It may be that there is a route through the firewalls connecting to a specific address+port from your location. In any case you will need to talk to your infrastructure people, we know nothing of your network arrangement so are unlikely to be able to help beyond these generic pointers.

May be this helps you:
You can use DAC. By default SQL Server listens for DAC on TCP port 1434. And for this you need to enable DAC. To enable you need to go to Surface Area Configuration. Here go to Remote DAC enable and make it true.
Have a look: https://www.mssqltips.com/sqlservertip/1801/enable-sql-server-dedicated-administrator-connection/

Leave a Reply

Your email address will not be published. Required fields are marked *