An Error Occurred While Retrieving the Information for Table session categories Please Try Again

Resolving could not open a connection to SQL Server errors

By:   |   Updated: 2019-05-29   |   Comments (84)   |   Related: 1 | 2 | three | More > SQL Server Configurations

Problem

Sometimes y'all may accept problems connecting to SQL Server and yous may become messages such as the following:

Fault: (provider: Named Pipes Provider, mistake: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Fault:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: forty - 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 acquired by the fact that under the default settings SQL Server does non permit remote connections. (provider: Named Pipes Provider, error: 40 - Could non open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

Or

A network-related fault or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not attainable. Verify that the instance name is right and that SQL Server is configured to allow remote connections. (provider: TCP Provider, fault: 0 - No such host is known.) (Microsoft SQL Server, Mistake: 11001)

These errors could exist for either Named Pipes connections or TCP/IP connections. In this tip, we look at what may exist causes to these errors and how to resolve.

Solution

There could be several reasons y'all become these error messages. Follow the below steps to see if y'all tin can resolve the effect.

Step i - Check that you lot can ping the SQL Server box

Brand sure you are able to ping the physical server where SQL Server is installed from the client motorcar. If not, you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Example Name for a named instance.

If it resolves using an IP address, you tin add the SQL Server auto into /etc/host file. To add the entry in the /host file type %SystemRoot%\system32\drivers\etc\ in the run window and open the host file using Notepad. In the beneath image I added IP address 74.200.243.253 with a machine name of SQLDBPool. At present I should be able to use the automobile name instead of the IP address to connect to the SQL Server.

Step ii - Check that the SQL Services are running

Make sure the SQL services are running You tin cheque 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 Director

Y'all can use SQL Server Configuration Manager to make sure the services are running.  If for some reason you cannot find SQL Server Configuration Director check out this article.

Using SC control

From a Windows command line you can issue the following control to see the status of the services.

Delight annotation for a named instance you have to write the command as follows using the correct example name, by replacing instancename with the bodily SQL Server example proper noun.

sc query mssql$instancename            

Step 3 - Check that the SQL Server Browser service is running

Check that the SQL Server Browser service is running. If yous accept installed a SQL Server named example 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 Server Browser service enabled and running. You tin check the browser service condition using either SQL Server Configuration Manager or the SC command equally follows.

Footstep iv - Bank check that you are using the correct SQL Server case name

Brand certain you are using the correct case proper noun. When you connect to a default instance, machinename is the best representative for the instance proper name and when you connect to a named case such as sqlexpress, you need to specify the instancename as follows: machinename\instancename where you enter the SQL Server case name for instancename.

Footstep 5 - Cheque that you tin find the SQL Server

Check that SQL Server is in the network. Yous can utilize the SQLCMD -50 command to retrieve the list of SQL Servers installed in the network. Note that this will but return SQL Servers if the SQL Server Browser service is running.

Step 6 - Cheque that TCP/IP and Named Pipes are enabled

Check the 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.

For the TCP/IP protocol, correct click and select properties to cheque the TCP/IP communication port as well. The default port is 1433, which can exist inverse for security purposes if needed.

Step seven - Check that permit remote connections for this server is enabled

Check to meet if allow remote connections for this server is enabled. In SSMS, right click on the case name and select Properties. Become to the Connections tab and make sure Allow remote connections to this server is checked. If you need to make a alter, y'all must restart the SQL Server case to employ the change.

You can also configure the remote server connections using the below commands. If you make changes you will need to restart SQL Server for these to accept affect.

The settings beneath are equivalent to the settings in the image above.

exec sp_configure "remote access", one          -- 0 on, one off exec sp_configure "remote query timeout", 600 -- seconds exec sp_configure "remote proc trans", 0      -- 0 on, one off            

Step 8 - Check the port number that SQL Server is using

 Locally connect to SQL Server and check the fault log for the port entry. You can execute XP_READERRORLOG process to read the errors or use SSMS by going to Management > SQL Server Logs and select the Electric current log. Scroll to the bottom which volition be the first entries in the error log and await for entries similar to beneath that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433 in this instance.

Stride 9 - Check that the firewall is non blocking access to SQL Server

Configure the Windows Firewall for the SQL Server port and SQL Server Browser service. Become to Command Panel and click on  Windows Firewall.  Become to exceptions tab as shown below. You can read this tip Configure Windows Firewall to Work with SQL Server for more than information.

Click on Add Port... and enter the port number and name.

Click on Add Program... to add together the SQL Server Browser service. Here you need to go the browser service executable path, normally it is located at C:\Program Files\Microsoft SQL Server\ninety\Shared location for SQL 2005 or similar for other versions of SQL Server. Browse the location and add the SQLBrowser.exe in the exception list.

Step x - Check that the Service Principal Proper noun has been registered

If you lot are able to connect to SQL Server by physically logging on to the server, simply unable to connect from a client computer so execute the below query in a query window to check the SPN.

-- run this command to run across if SPN is not found EXEC xp_readerrorlog 0,1,"could not register the Service Principal Name",Zero            

If the SPN is not institute, read this Register a Service Master Name for Kerberos Connections and this How to Configure an SPN for SQL Server Site Database Servers for more than details most how to setup and register an SPN.

Next Steps

• Next time yous take issues connecting, check these steps to resolve the result.
• Related tips:
  • Understanding SQL Server Cyberspace-Libraries

Related Manufactures

Popular Articles

Well-nigh the author

Jugal Shah has 8+ years of all-encompassing SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips

Commodity Last Updated: 2019-05-29

bellourt1961.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/

Share this post