2

I am at the end of my rope troubleshooting a remote SQL Server connection problem. I am a DBA but not a networking expert.

Certain information below has been sanitized using "x" in place of a letter.

I have access to a remote Windows 2008 R2 server through RDP. I connect to this server with the address jxxxxxx.sxxxx:5000. This succeeds.

Using this RDP connection I installed MS SQL Server 2014 Express on the server. This has been upgraded to SP2 Cumulative Update 8. I used the checkbox to install a default, unnamed instance. Using SQL Server Management Studio from the server I can connect to and use this new instance.

I have already performed the following configuration steps:

  • SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER > enabled TCP/IP. I did remember to restart the service by going to SQL Server Services and restarting SQL Server (MSSQLSERVER).
  • In the firewall I created an inbound rule to allow all TCP traffic on 1433.

On my home computer I opened SQL Server Management Studio 2017 and attempted to connect with jxxxxxx.sxxxx. I am unable to connect, receiving the error message below:

Cannot connect to jxxxxxx.sxxxx.

------------------------------
ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server)

To troubleshoot this I have gone to extremes:

  • On the server I used SSMS to connect to the instance and checked the instance properties. "Allow remote connections to this server" is checked.
  • I pinged jxxxxxx.sxxxx successfully and tried using that IP address for my home computer's SSMS connection string.
  • In Configuration Manager I right-clicked on the TCP/IP protocol and scrolled down to IP/All. Dynamic Ports is null and TCP Port is 1433. Though it shouldn't be necessary, I set every other IP type in that menu to "Enabled".
  • By using SSMS on the server I checked the SQL Server log and confirmed that it is listening to ip4 and ip6 for port 1433.
  • Though it shouldn't be necessary, I enabled SQL Server browser (I allowed UDP port 1434 inbound on the server firewall, set the browser service to automatic in Configuration Manager, and started it)
  • Though it shouldn't be necessary, I tried a more explicit connection string from my client SSMS: jxxxxxx.sxxxx,MSSQLSERVER:1433.
  • Again shouldn't matter because outbound should be allowed, but I fully turned off the client firewall.
  • Enabled named pipes, and in the Native Client settings enabled TCP/IP and Named Pipes, and restarted the service. I know that this should be unnecessary, but I'm going crazy.
  • Restarted both computers
  • Slept on it.

I have no more ideas and I need help. Is there some way that my connection string is wrong, or could be incorrect for some network environments? The Server is not in a domain and is named Oxxxxxxx, if that helps...

  • The server is not on a domain, but from the server I can reach it with this: tcp:oxxxxxxx,1433 (the oxxxxxxx is it's hostname). I should have been more specific. The two machines are not on the same network. You are most likely correct about port forwarding. I am going to check with the person who owns and administrates it about port forwarding. In this case the port forwarding will have to allow Internet traffic, right? – MasterOfNone Nov 3 '17 at 22:49
2

Something else you can have a look at is whether the TCP protocol for SQL Server is listening on the correct IP address(es). You can do this from SQL Server Configuration Manager.

Go the same screen where you enabled the TCP/IP protocol, in your case SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER and right-click on TCP/IP. Once the new screen loads, click on the IP Addresses tab, and you will see something similar to this:

TCP/Properties

The IP that you are connecting to remotely should show up in one of these fields (e.g. IP1, IP2, etc.). To find out what the IP you are using resolves to, run the following in a cmd session:

    C:\Users\hs>nslookup jxxxxxx.sxxxx
    Server:  UnKnown
    Address:  192.xxx.xxx.xxx

    Non-authoritative answer:
    Name:    jxxxxxx.sxxxx
    Addresses:  192.xxx.xxx.xxx <- This is the address

By default SQL Server listens on all addresses, but in some cases, the default isn't used and addresses need to be explicitly added. Note that if you are using NAT or port-forwarding, you might not see the external IP, in which case you will need to work with your network administrators to make sure the translation or forwarding address is correct.

0

The solutions is provided in the error message you are receiving on your "local" computer.

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: Named Pipes Provider, error: 40 -  
Could not open a connection to SQL Server)

...the important bit possibly being:

...(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)...

Forcing a TCP/IP Connection

In the SSMS connection window perform the following steps to force a TCP/IP connection:

Basic SSMS Login window

Basic SSMS Login window
Click on the Options >> button to display the login windows with Advanced Properties

Advanced SSMS Login window

Enter your Login and Password for a SQL Server Authentication login or select Windows Authenticated from the pull-down menu to login using your windows account.

Advanced SSMS Login window

CAUTION
Possible cause of errors here: Not in an Active Directory environment and trying to login with a "domain" or "local" Windows account.

Advanced SSMS Connection Properties window

In the Network section of the Connection Properties change the Network Protocol to TCP/IP.

Advanced SSMS Connection Properties window

Advanced SSMS Connection Properties window recommendations

Your connection window should look like this:
Advanced SSMS Connection Properties window recommendations

(I changed the network connection time-out from 15 to 60 in this example)

Click on OK to connect to your SQL Server instance via TCP/IP.

Alternate Solutions

If this doesn't work, then you may have additional issues with the network connection.

  • Firewall at Service Provider/Company
  • Company Firewall Configuration
  • NAT
  • Forwarding Proxy Rules
  • Network WAF

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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