Error: 40 – could not open a connection to sql server

In this SQL Server tutorial, we will learn how to resolve the “provider: named pipes provider, error: 40 – could not open a connection to sql server” error. So, in this tutorial, we will discuss the root cause of this error and will also discuss how to solve the error, could not open a connection to sql server error 40.

could not open a connection to sql server error 40

Could not open a connection to sql server

Recently, I have encountered this error while connecting to the Database Engine using SQL Server Management Studios. The complete error message is shown in the image below.

Cannot connect to
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. error 40 – could not open a connection to sql server.

could not open a connection to sql server error 40
could not open a connection to sql server error 40

Let’s first discuss some of the main reasons behind this error message.

  1. We might be using wrong server name to connect due which the failure has occured.
  2. The SQL Server Service is not running properly or it might have wrong default settings.
  3. The SQL Server do not have permission to allow an remote connection.
  4. The host machine might have wrong firewall settings.

Don’t worry if you don’t know how to resolve these issues to overcome this error. Here are some of the steps that we can follow to overcome these issues.

Fix 1: Use Correct Server Name

Many times the main reason behind this error is the use of the wrong server name while connecting. To connect to the Database Engine in the SQL Server, we need to provide some login details such as username, password, and server name.

Now, it is important to use the correct server name. By default, the server name is in the following format – “ComputerName\InstanceName“.

In this format, the ComputerName is the name of the machine or computer. Now, if you are using Windows 10 OS, you can get this name by opening the About page in your System settings. The example for this is shown below.

Error: 40 - could not open a connection to sql server
Computer name for SQL Server Instance

Next, in the server name is InstanceName which is the name of your SQL Server instance. Now, to get the instance name, we need to open the Services setting in our system.

And from the list, we have to find the SQL Server Service. The instance name is there in the brackets of the service name. The example is demonstrated below.

could not open a connection to sql server error 40
SQL Server Services

In our case, the name of the instance is SQLEXPRESS. So, the server name will be similar to “DESKTOP-XXXX\SQLEXPRESS“.

Fix 2: SQL Server should be up and running

The second root cause for this error can be because your SQL Server instance is not running. Now, to check wheater the instance is running or not. First, we need to open the “SQL Server Configuration Manager“.

In SQL Server Configuration Manager, first, click on SQL Server Services then, a list of services will appear on the right pane. From the list check the state of the “SQL Server” service. And we can also right-click the service and click on “Start” to start the service.

A network-related or instance-specific error occurred while establishing a connection to sql server
Starting the SQL Server Service

Next, we should also confirm whether the SQL Server Browser is running or not in the same way.

error 40 could not open a connection to sql server error 53
Starting the SQL Server Browser

Fix 3: Enable TCP/IP in SQL Server Configuration

Whenever multiple SQL Server instances are connected across a network, they all use TCP/IP for communication. So, to resolve this error, we have to confirm whether the TCP/IP is enabled or not. For this, again we will use the “SQL Server Configuration Manager“.

In SQL Server Configuration Manager, first, expand the “SQL Native Client 11.0 Configuration” then, click on “Client Protocols“. A list of protocols will appear on the right pane. From the list check the state of TCP\IP. And we can also right-click it and click on the “Enable” option to enable it.

error 40 could not open a connection to sql server error 53
Enable TCP/IP in SQL Server Configuration

Next, we can also confirm whether TCP\IP is working on the default ports or not. For this, first, right-click TCP\IP and click on Properties. Next, from the General category, we can confirm whether the default port is 1433 or not.

error 40 could not open a connection to sql server
Default TCP/IP port in SQL Server Configuration

Fix 4: Allow Remote Connections

Other than enabling the TCP\IP, we have to enable the remote connection settings from the SQL Server properties. For this task, we can use SQL Server Management Studio. Here are steps to enable the property using SQL Server Management Studio.

  • From the Object Explorer, right-click on the server name and click on “Properties” option.
error 40 could not open a connection to sql server
Properties option in SSMS
  • Next, open the Connection properties section and tick mark the “Allow remote connections to this server” option. In the end, clcik on “OK” to save changes.
error 40 - could not open a connection to sql server
Allow Remote Connections using SSMS

Fix 5: Allow SQL Server in Firewall Settings

The Windows Firewall is quite effective at safeguarding the operating system from various dangerous threats. By default, the firewall prevents numerous ports and services from running.

To overcome this issue, we can add a firewall exception for TCP/IP ports 1433 or 1434. So, the SQL Server can run without any issue.

The steps to add the exception in the Windows firewall are as follows.

  • First, search for “Windows Defender Firewall with Advanced Security” in our start and open it.
  • Next, from the left pane click on “Inbound Rules” and then, click on “New Rule“. It will open a new “Inbound Rule Wizard” window.
error 40 - could not open a connection to sql server 2019
Adding New Firewall Rule for SQL Server
  • In the new window, first, select “Port” and then click on “Next“. After this, select the “TCP” option and specify the port number as 1433.
error 40 - could not open a connection to sql server management studio
Adding TCP\IP ports for SQL Server Remote Connection
error 40 - could not open a connection to sql server
error 40 – could not open a connection to sql server
  • Next, we need to select the “Allow the connection” option and click on the “Next” button.
a network-related or instance-specific error in sql server 2014 error: 40
Allow SQL Server in Firewall Settings
  • Next on the Profile page, tick marks the options as per your requirements and again click on the “Next” option.
error 40 - could not open a connection to sql
Allow SQL Server in Firewall Settings
  • On the last Name page, specify the name and description for the exception and click on the “Finish” button.
error 40 could not open a connection to sql server error 53
Allow SQL Server in Firewall Settings

With this, we have added the new firewall which will allow having a remote connection in SQL Server.

By following all the given solutions, we can resolve the error and we can easily connect to the SQL Server instance.

So, in this tutorial, we have learned how to resolve the “provider: named pipes provider, error: 40 – could not open a connection to sql server” error. In this, we have discussed the root cause of this error and also the possible solution for it.

You may also like to read the following SQL Server tutorials.

I hope this will help to fix the below errors:

  • named pipes provider, error: 40 – could not open a connection to sql server
  • a network-related or instance-specific error in sql server 2014 error: 40
  • a network-related or instance-specific error in sql server 2019
  • named pipes provider: could not open a connection to sql server (53)
  • could not open a connection to sql server error 40
  • error 40 – could not open a connection to sql server
  • error 40 could not open a connection to sql server error 53