locked
Unable to connect to SQL Server Express 2012 over SSH RRS feed

  • Question

  • Hi,

    Our provider has forwarded a number of ports from the SSH server to our server, including http, RDP and VNC. They have also forwarded port 1433 so that I can tunnel over SSH to the database server, however, I am unable to connect. Here is what I have done so far.

    In Sql Server Configuration Manager

    * In Protocols for MSSQLSERVER I have ensured the TCP/IP protocol is enabled, and that the server is listening on [IP address] TCP Port 1433

    In Sql Server Management Studio

    * Server -> Properties -> Connections - Enabled "Allow remote connections to this server"

    Windows Firewall is OFF

    Connecting to SSH server I have forwarded 127.0.0.1:7777 > [IP address]:1433

    I try connecting to 127.0.0.1 7777 with Telnet and the SSH server reports:

    channel 10: open failed: connect failed: Connection timed out

    Our provider reports that the connection to port 1433 is being refused.

    Have I missed something?

    Does SQL Server Express 2012 accept remote connections?

    Thanks for any help.

    Friday, February 6, 2015 1:56 AM

Answers

  • Hi Erland,

    Thanks for your (and Tibor's) reply. I guess my point was that 127.0.0.1 and 192.168.10.3 are essentially the same endpoint, even though they are different addresses. 

    I will contact our provider again, see if there is some way we can trace exactly where the connection is being refused. I just wanted to make sure I had ticked all the boxes before getting back to them.

    I will see if I can use a packet sniffer like Wireshark to determine if the packet is making it through the tunnel to the server. As you suggest, it is probably a problem outside of SQL Server.

    Thanks for your help.

    Jason

    Tuesday, February 10, 2015 12:50 AM

All replies

  • <Does SQL Server Express 2012 accept remote connections?>

    Yes, assuming you have enabled that netlib (TCP/IP). You can verify this by looking for startup information in the SQL Server errorlog file. It will state what port(s) it is listening to, if any.

    The rest happens outside SQL Server (firewalls, network config, and such stuff).


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Michelle Li Monday, February 9, 2015 9:05 AM
    Friday, February 6, 2015 9:41 AM
  • In addition to Tibor's post, make sure that you restarted the SQL Server
    after making the changes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Michelle Li Monday, February 9, 2015 9:05 AM
    Friday, February 6, 2015 10:24 PM
  • Hi Tibor,

    Thanks for your reply. I looked in the log and I can see the following:

    02/03/2015 13:13:12,spid12s,Unknown,Server is listening on [ fe80::65e0:e3bf:532b:de72%15 <ipv6> 1433].
    02/03/2015 13:13:12,spid12s,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1433].
    02/03/2015 13:13:12,spid12s,Unknown,Server is listening on [ 192.168.10.3 <ipv4> 1433].

    I have restarted the SQL Server after applying all of the changes I mentioned in my post.

    We are not using IPv6 for anything, should I disable it? Will listening on the same port on the loopback address and the actual IP address cause an issue? Will one supersede the other?

    Thanks for any assistance you can give.

    Jason


    Monday, February 9, 2015 6:49 AM
  • No, there is no issue with port 1433 being used on different IP-addresses, just you can live on 234 Broad Street and I can live on 234 Flintstone Avenue without conflicts. Nor is there any reason to disable IPv6.

    Without having the full picture, it is difficult to say what it's wrong, but it seems to me that the problem is outside SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 9, 2015 8:34 AM
  • Hi JasonWilliams79,

    According to your description, you are unable to connect to SQL Server Express 2012 over SSH. Besides Erland and TiborK's posts, the issue could also be due to that SQL Server Browser service is not started, or SQL Server is listening on dynamic ports. To troubleshoot the issue, please follow the solutions below.

    1.Click SQL Server Services node of SQL Server Configuration Manager, and check if SQL Server Browser service is running. If it is not running, please start it.

    2.Expand SQL Server Network Configuration node of SQL Server Configuration Manager, click 'Protocols for [instanceName]', right-click 'TCP/IP' and click 'Properties'. In the tab 'IP Addresses', check if the value of TCP Dynamic Ports in IP All is an empty value. If it has value, please make it blank.

    Regards,
    Michelle Li

    Monday, February 9, 2015 9:42 AM
  • Michelle, accoring to the errorlog file, SQL Server is listening on port 1433. This means that the SQL Server Browser service has no relevance here, and same goes for "dynamic port" config...

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, February 9, 2015 10:10 AM
  • Michelle, accoring to the errorlog file, SQL Server is listening on port 1433. This means that the SQL Server Browser service has no relevance here, and same goes for "dynamic port" config...

    Furthermore, if I understand Jason's post correctly there is a tunnel set up with the target port specified, so even if the port would something else than 1433, the Browser service would have nothing to do with it.

    The Broswer service comes into play when you specify servername\instance. In this case the Browser service returns the port number to connect to.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Michelle Li Friday, February 13, 2015 2:03 PM
    Monday, February 9, 2015 11:46 AM
  • Hi Erland,

    Thanks for your (and Tibor's) reply. I guess my point was that 127.0.0.1 and 192.168.10.3 are essentially the same endpoint, even though they are different addresses. 

    I will contact our provider again, see if there is some way we can trace exactly where the connection is being refused. I just wanted to make sure I had ticked all the boxes before getting back to them.

    I will see if I can use a packet sniffer like Wireshark to determine if the packet is making it through the tunnel to the server. As you suggest, it is probably a problem outside of SQL Server.

    Thanks for your help.

    Jason

    Tuesday, February 10, 2015 12:50 AM