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
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.
Let’s first discuss some of the main reasons behind this error message.
- We might be using wrong server name to connect due which the failure has occured.
- The SQL Server Service is not running properly or it might have wrong default settings.
- The SQL Server do not have permission to allow an remote connection.
- 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. An example for this is shown below.
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.
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 that 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.
Next, we should also confirm whether the SQL Server Browser is running or not in the same way.
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.
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.
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.
- 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.
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.
- 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.
- Next, we need to select the “Allow the connection” option and click on the “Next” button.
- Next on the Profile page, tick marks the options as per your requirements and again click on the “Next” option.
- On the last Name page, specify the name and description for the exception and click on the “Finish” button.
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.
- Full-text search in SQL Server
- SQL Server find text in stored procedure
- SQL Server check user permissions on table
- How to execute stored procedure in SQL Server
- How to check if SQL Server is running
- How to get list of users in SQL Server
- What is a stored procedure in sql server
- SQL Server create stored procedure
- Remote procedure call failed in SQL Server
- msg 3609 the transaction ended in the trigger
- Create Foreign Key in SQL Server
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.