Share via

Resolve SQL Server connectivity issues

  • Article

 

In this post, I am sharing various tests that should help you debug the problem. The steps below you help you to fix various issues like error message below:

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).

An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: xxx)

SERVER SIDE

Step 1: SQL Server Configuration Manager
Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if  :
- SQL Server service status is “Running”.
- SQL Browser service status is “Running”. (if you have got an instance that is not by default with port 1433)
- Check if TCP/IP and NP are enabling

Step 2: Firewall
Disable your Firewall and check if the issue doesn't occur follow the both articles below

  How do I open the firewall port for SQL Server on Windows Server 2008
  Configuring the Windows Firewall to Allow SQL Server Access

Step 3 : Enable Remote Connection
Right click on the server node and select Properties. Go to Left Tab of Connections and check “Allow remote connections to this server”

  To configure the Database Engine to accept remote connections

CLIENT SIDE

Step 1 : Connection string is not correct or point to not a real server. Be ensure of the instance name

Step 2 : Check if the port is opened
telnet <your_target_machine> <TCP_Port>

Step 3 : Ping server
ping -a <your_target_machine>   
ping -a <Your_remote_IPAddress>
Run “net view \\your_target_machine”

Step 4 : SQL Alias.
On my end I was working on a 64 bit machine but had to remove an alias under the SQL Configuration Manager -> 32 Bit Config -> Alias.
Also confirm with cliconfg.exe

The registry keys are: 
32 bit:  HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
64 bit:  HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
 
Step 5 : Try the connection out of your application
SQLCMD –E -S tcp:<IPAddress>[\<InstanceName>],<Port>
SQLCMD –E -S tcp:<Your_target_machine>[\<InstanceName>],<Port>

Try the FQDN to connect to the server that is running Microsoft SQL Server.
SQLCMD –E -S tcp:<Your_target_machine>.<FQDN>[\<InstanceName>],<Port>

Others relevant documentations

  SQL_Protocols Team 
  How to configure SQL Server 2005 to allow remote connections
  How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port
  Troubleshooting Server and Database Connection Problems
  SQL Server 2005 Connectivity Issue Troubleshoot - Part I
  Troubleshoot Connectivity Issue in SQL Server 2005 - Part II
  Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
  Steps to troubleshoot SQL connectivity issues
  Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error: XXX)

 

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Comments

  • Anonymous
    February 25, 2014
    Hello,
    Below are steps to check for fine working
    1 SQL Server Services
    2 SQL Server (MSSQLSERVER)
    3 SQL Server Browser
    4 Aliases
    5 SQL Server Default Port 1433
    6 Client Protocols, "TCP/IP", Property, 1433
    7 SQL Server Management Studio, right click, property and click on "Connections", "Allow remote Connections to this server"
    8 Ping
    9 Firewall default port block 1433

    I have provided link http://www.technologycrowds.com/2014/02/could-not-open-connection-to-sql-server.html for detailed steps with snapshots.

    thanks,