2

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!!

Improve this question
6
  • is this a 'named' instance or default instance? in SSMS connection window, did you try your ip address without the port number? I think Sql Browser will resolve the whole dynamic port thing. have you enabled remote connections on that sql server instance? - check this link for other possible solutions - blogs.msdn.microsoft.com/sql_protocols/2008/04/30/…
    – Scott Hodgin - Retired
    May 9, 2017 at 10:54
  • when you rdp connect to this server, do you use any VPN?
    – McNets
    May 9, 2017 at 10:55
  • thanks for coming back to me. It's a named instance. I've tried my IP Address without port number and still no luck, even with browser running and I have enabled remote connections.
    – GoingMadDBA
    May 9, 2017 at 10:56
  • with a named instance, did you try 12.34.56.789\namedinstance?
    – Scott Hodgin - Retired
    May 9, 2017 at 10:58
  • Yes Scott. No luck I'm afraid. I think David and McNets might be on to something with the VPN.
    – GoingMadDBA
    May 9, 2017 at 11:11

3 Answers 3

Reset to default
1

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.

Improve this answer
4

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.

Improve this answer
2
  • Thanks David. Excuse my ignorance as I don't have a great deal of knowledge with networking. We have 3 servers with the same IP address but different port numbers, eg 12.34.56.789:1234 and 12.34.56.789:5678. I take it that likely means we go through a VPN and are directed to the correct server by using the Port number?
    – GoingMadDBA
    May 9, 2017 at 11:09
  • 1
    Probably not though you'll have to check with your network/infrastructure people to confirm. Most likely 12.34.56.789 is the public address of a firewall, which has "network address translation" rules to forward incoming connections on specific ports to other ports on machines behind the firewall.
    – David Spillett
    May 9, 2017 at 13:37
0

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/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.