Case #
In an SQL client connecting to a SQL Server database instance, you receive any of the following errors:
Error1
ERROR: (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
Error 2
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, 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: 1326)
Error 3
A network-related error 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: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)
Error 4
Error: 'System.Data.SqlClient.SqlException (0x80131904): 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) ---> System.ComponentModel.Win32Exception (0x80004005): Access is denied
Solution #
There are various potential root causes to the above errors. What they all share in common is that they indicate a failure of the SQL client to connect to a SQL Server based instance.
The following steps should be executed in the order below in order to isolate the root cause of the issue:
- Quickly check connectivity between the client and the server using the .udl file test which must be created on the SQL client end. Ensure that the correct SQL Server instance connection string is being used in the format [hostname or IP of SQL instance]\[SQL instance name],[SQL instance port if static, do not use if dynamic].
- Check health and integrity of the SQL client endpoint and of the SQL Server endpoint. This means to check the application/client event logs and receiver verbose tracing on the one end as well as check the running status of SQL Server services (SQL Server instances and SQL browser service) on the other end and check SQL Server error logs. On the SQL server side, check both Windows/Application events logs on the server hosting the SQL instance in question, as well as the SQL Server errorlog itself. For reading the SQL Server error logs you should consider the following built-in two stored procedures: sp_readerrorlog and xp_readerrorlog.
- Check SQL Server Configuration Manager for the following parameters: TCP and Named Pipes are enabled, static vs dynamic ports are being used and usage of any alias is in place.
- Check the there is successful TCP socket communication between the SQL client and the server. This means checking the IP addresses and TCP/UDP ports are allowed from the SQL client to the SQL server. If dynamic ports are being used, then the SQL dynamic port range (49152–65535) must be allowed in the interVLAN routing configuration of the L3 device as well as in the Windows Firewall of any involved machine.
- Check SPN configuration is correct for SQL Server
- Remember that there are various ways an SQL client can connect to a SQL Server database using different SQL Server connection drivers. Ensure that the application configuration (for instance web.config file and patch level of the development framework used) is compatible with the version of the SQL Server instance.
