What Steps to be Taken on Connections Issues & Login Issues on SQL Server
Resolving could not open a connection to SQL Server errors
ProblemSometimes you may have issues connecting to SQL Server and you may get messages such as the following:
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)Or
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)Or
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)These errors could be for either Named Pipes connections or TCP/IP connections. In this tip, we look at what may be causes to these errors and how to resolve.
SolutionThere could be several reasons you get these error messages. Follow the below steps to see if you can resolve the issue.
Step 1Make sure you are able to ping the physical server where SQL Server is installed from the client machine. If not, you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Instance Name for a named instance.
Step 2Make sure the SQL services are running You can check the SQL Server services by using the SC command or SQL Server Configuration Manager. Many times you may find that the SQL Server instance is not running.
Using SQL Server Configuration Manager
Using SC command
Step 3Check that the SQL Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Browser service enabled and running. You can check the browser service status using Step 2 and either using SQL Server Configuration Manager or the SC command as follows.
Step 4Make sure you are using the correct instance name. When you connect to a default instance, machinename is the best representative for the instance name and when you connect to a named instance such as sqlexpress, you should specify machinename\instancename
Step 5Check that SQL Server is in the network. You can use the SQLCMD -L command to retrieve the list of SQL Server installed in the network. Note that this will only return SQL Servers if the SQL Browser service is running.
Step 6Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes and TCP/IP protocol.
Step 7Check to see if remote connections is enabled. Right click on the server name in SSMS and select Properties. Go to the Connections tab and make sure Allow remote connection to this server is checked. If you need to make a change, you must restart the SQL Server instance to apply the change.
The settings below are equivalent to the settings in the image above.
exec sp_configure "remote access", 1 -- 0 on, 1 off exec sp_configure "remote query timeout", 600 -- seconds exec sp_configure "remote proc trans", 0 -- 0 on, 1 off
Step 8Check the error log for issues. Locally connect to SQL Server and check the error log for the port entry. You can execute XP_READERRORLOG procedure to read the errors or use SSMS. You should see entries similar to below that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433.
Step 9Configure the Windows Firewall for the SQL Server port and SQL Browser service. Go to Control Panel -> Click on Windows Firewall -> Go to exception tab as shown below. You can also read this tip for more information as well.
Step 10If you are able to connect to SQL Server by physically logging on to the server, but unable to connect from a client computer then execute the below to check the SPN.
-- run this command to see if SPN is not found
EXEC xp_readerrorlog 0,1,"could not register the Service Principal Name",Null