Resolve SQL Server connectivity issues
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 |