Database errors can be frustrating. Especially when even the login to the SQL Server fails.
Usually, the MSSQL error 40 occurs when a user can’t connect to the SQL server.
At Bobcares, we often receive requests to fix MSSQL error 40 as part of our Server Management Services.
Today, let’s analyze the error and see how our Support Engineers fix it for our customers.
Causes for the error MSSQL Error 40
Now, let’s get into the details of the “Error: 40 – Could not open a connection to SQL Server“.
This error primarily happens when there are problems establishing database connection. The common causes for the error are MSSQL service failure or the closed MSSQL port.
How we fix MSSQL error 40
We just saw on what causes the MSSQL error 40.
We’ll now take a look at different error scenarios and how we fix the MSSQL error.
1. SQL service down
Recently, we had a customer who was facing a problem to get into the database. We helped him to fix the error by verifying the following details.
The most common reason that can cause the error was SQL service failure.
Therefore, we first verified whether the service was running. We verified the service from
Click Start >> Search services.
Here the server was in a stopped state. So we did a restart of the service. Also, we made sure that the SQL Server Agent was running.
In a similar way, the SQL service can also be verified from the command prompt. To do this, our Support Engineers use the command.
sc query mssqlserver
sc query sqlserveragent
2. Enable TCP/IP
Another common reason for the error can be disabling of TCP/IP protocol on the server.
Here, our Dedicated Engineers verify the details and enable TCP/IP.
We enable TCP/IP from the SQL server configuration manager.
Open SQL server configuration manager >> Expand SQL server network configuration >> Right click on TCP/IP >> Enable.
We will also verify the port mentioned in TCP/IP. By default, MSSQL runs on port 1433. We always make sure that the server listens correctly on the port.
3. Port not open in the firewall
In most cases, when the SQL service is running and the server has TCP/IP enabled, there should not be any errors.
However, if still the error happens our Support Engineers analyze the firewall setting in the server. The SQL port 1433 must be open on the server. If the port is not open, we open it in the Windows firewall.
Start >> Administrative Tools >> Windows Firewall with Advanced Security
Click on outbound rules >> Select new rule.
Now our engineers will add the rule to open MSSQL port.
That fixes the MSSQL error 40.
[Need assistance to fix MSSQL errors? We’ll help you.]
Conclusion
In short, MSSQL error 40 happens due to a lack of connection to the server. Today, we discussed how our Support Engineers fix the error by verifying the service and enabling the port 1433 in the 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.
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 – “ComputerNameInstanceName“.
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-XXXXSQLEXPRESS“.
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 TCPIP. And we can also right-click it and click on the “Enable” option to enable it.
Next, we can also confirm whether TCPIP is working on the default ports or not. For this, first, right-click TCPIP 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 TCPIP, 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.
Fix SQL Server Management Studio Error 40
Introduction
Sometimes you may get the issue to connect to SQL Server and get the error message error 40 could not open a connection to sql server 2016.
This error message occurs due to several reasons and is the most frequent error message happens when we connect to SQL Server. In this blog post I am sharing the technique to fix SQL Server Error 40.
Tip: Methods to Resolve SQL Server Error 26
Method to Resolve SQL Server Error 40
- First of all you have to make sure that your SQL Server are running fine.
- Open SQL Server Configuration Manager and click on SQL Server Services to check the state (Stopped/Running) of SQL Server (SQLEXPRESS) and also check SQL Server (MSSQLSERVER) and SQL Server Browser is in the Running state. There is a possibility that your SQL Server instance is not running.
- Now expend SQL Native Client 10.0 Configuration, click on Aliance, you always have to make sure that the Aliance should be empty
- Open Control Panel, click on System and Security and select Windows Firewall and click on Advanced Settings to check the SQL Server Default Portal 1433
- Windows Firewall with Advanced Security window will open, Under the Windows Firewall with Advanved Seccurity on Local Computer, click on Inbound Rules and on the right panel, under the Action column, click on New Rules
- New Inbound Rule Wizard window will open, click on Protocol and Ports under the Steps column and enter SQL default port1433 in Specific local ports box. Then click on Name under the Steps column and enter a SQL Port Name in Name box and click on the Finish button
- From the SQL Server Configuration Manager, select Client Protocols then right click on the TCP/IP and select properties to check the default port 1433
- Open SQL Server Management Studio, under the Object Explorer column right click on the server name and form drop down window click on Properties
- In Server Properties windows, click on the Connections under Select a page, column and select Allow remote connections to the server option and click on Ok button
Conclusion
In this article we have discussed how to fix SQL Server Error 40. This error message could be for TCP/IP connection or Named Pipes connections. To avoid these types of errors use dynamic ports when database engines have been configured
SQL Server Error 26 and SQL Server Error 40 appear when you try to connect to SQL Server. We will troubleshoot and try to fix them in the same article as both are related to connection issue. We recommend to use the below solutions for the both errors and to try to localize the problem.
The error Messages:
(provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)”
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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
“ Named Pipes Provider Error: 40 – Could not open a connection to SQL Server“.
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)
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 the SQL Server is configured to allow remote connections. (provided: Named Pipes Provider, error: 40- Could not open a connection to the SQL Server) (Microsoft SQL Server, Error: 2).
sql server error 26
SQL Server Error 26 and SQL Server Error 40
What is SQL Server Error 26?
You will get this error message while trying to connect to a SQL Server named instance and not when you use default instance. The client stack could not receive SSRP response UDP packet from SQL Browser.
How to fix Error 26 on SQL Server?
- Recheck the server name for any error and fix it
- Check the instance name for any error
- Check if SQL Server browser is running
- Control if this instance exist on server
- Ping the server and check if DNS can be resolved correctly
What is SQL Server Error 40?
This error appears mostly when SQL Server does not allow remote connections, Firewall Settings or TCP/IP is not enabled. We will explain below all those in order to troubleshoot easy.
Read also other SQL Server Helping Posts:
- How to fix SQL Server Error 229
- Fix SQL Server Error 233
- SQL Server Error 17002
How to fix error 40 on SQL Server?
- Check SQL Server is running
- Make sure that Allow Remote Connections is enabled on sql server properties
- Check if TCP/IP is enabled
- Configure to allow SQL Server in Firewall Settings
- Check for working fine SQL Server Browser
- Make sure that you can ping the SQL Server
Below we will explain the above steps in long way for better understanding and easy fix of SQL error 26 and sql error 40.
1. Check if SQL Services are running
Hint! If you are running SQL Server by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections.
How to check if SQL Server Service is running?
- Go to SQL Server Configuration Manager > SQL Server Services
- Find the service SQL Server (MSSQLSERVER) if default or the name of your instance
- Check if is running – should be running state with a green indication
Using Windows Command Prompt
sc query mssqlserver
For named instance name use below by by replacing instancename with the actual SQL Server instance name.
sc query mssql$instancename
How to check if SQL Server Browser is running?
- Navigate to SQL Server Configuration Manager > SQL Server Services
- Find the SQL Server Browser Service
- Check if it is configured to start automatically and is started – should be with a green indication
Using Windows Command Prompt
sc query sqlbrowser
Check if SQL Server Service
2. Try to Flush DNS
How to flush DNS?
- Click Start Button
- Type CMD and press enter
- Type this into the black window that appears: ipconfig /flushdns and press enter.
Fluish DNS
3. Make sure that allow Remote Connection is enabled
Check if remote connections is allowed for applications/client. This option need to be allowed to establish connections and perform operations on the server. Often this is the reason of SQL Server Error 26 and SQL Server Error 40.
How to check if the remote connection is enabled?
- Open ‘SQL Server Management Studio’
- Right click on database name and select ‘Properties’
- In ‘Properties’ window, select ‘Security’, enable `SQL Server and Windows Authentication mode` and click `OK`.
- Now restart the SQL server.
Allow Remote Connections
4. Check Firewall
Check the firewall status for any troubles:
- Open ‘Control Panel’ and after that ‘Windows Firewall’.
- Select ‘Change Settings’ In ‘Windows Firewall’,
- Add an exception for port ‘1434’ and name it ‘SQL Server’
- Enable by clicking on the checkbox
5. Connection String
This solution is on cases when you are using connection string and are you writing it wrongly. For example connection string used by .NET framework:
Server=serverAddress;Database=dbName;User Id=username;
Password=password;
- Check for each parameter passed in the connection string for any typographical errors.
- Control the validity of the username/password.
- Confirm if the given database exists.
6. TCPIP Protocol
This solutions can work when you have mix of default and named instance or named instances only.
How to Enable TCP/IP port?
- Open SQL Server Configuration Manager
- Click on SQL Server Network Configuration and click on Protocols for Name
- Right-click on TCP/IP
- Click Enable
- Restart your SQL Server instance
Enable TCP-Ip port
If you want to use a static port for your instance (instead of dynamic that changes after every restart) you can change it here.
- Open Properties for TCP/IP protocol
- Go to IP Addresses tab
- Scroll down to IPAll section
- Remove 0 value from TCP Dynamic Ports
- Specify your port in TCP Port
You can use this port to connect to your instance by providing <servername>,<port> or <IP>,<port> as Server Name (yes, there is a comma, not a colon).
TCP-Ip static port
Conclusion:
We have explained some solutions to fix SQL Server Error 26 and SQL Server Error 40 and hope that you find the fix on those solutions. The both errors are included on the same article because they have almost the same troubleshoot. If you have any other solution worked for you comment below and we will try to include it on our article.
Hi All,
I inherited a web server (I’ll refer to as WS1) that is running MS 2003 SP2 with Microsoft SQL Web Data Administrator (I have no clue what version of SQL Server this is? Any idea on how to find it is greatly appreciated, but not necessarily what I’m after in this post). There is a default website (website.com) which resides on the C: drive. This was built with asp. On the default.asp page there is a link to an administrative portion (admin.com). This was built with DotNetNuke (.aspx) and resides on the D: drive. All works fine.
I recently was tasked to set up a new web server (I’ll refer to as WS2) with MS 2003 SP2 and SQL Server 2005. I imported the database and websites from WS1 that I needed. Both websites are now on the C: drive. Since WS1 is currently live, I can’t use website.com and must use the IP address. No problem…the default website website.com opens fine. I also changed how I call the admin.com site. Since I can’t open it as href=admin.com and because I already have a default website, I tried creating a virtual directory under the default site to open it. But I’m getting the following error:
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)
Looking through the posts on the web, I’m not sure what to do. I originally thought the problem might be with my web.config. But now I’m not sure. I’m not even close to being a network admin or a dba so keep that in mind when you respond. Here’s what I’ve checked:
1) Surface Area Configuration for Services and Connections >> Remote Connections: Local and remote connections is selected with the «Using both TCP/IP and named pipes» option selected.
2) Computer Management >> SQL Server Configuration Manager >> SQL Server 2005 Network Configuration >> Protocols for MYWEBSITE: TCP/IP & Named Pipes are enabled
I can connect to the database via SQL Server Management Studio, so I know the database is up and running.
Also, the original web.config file for the admin.com website looks something like this:
<add key=»WebDSN» value=»Server=(local);Database=Database1;uid=userid1;pwd=password1;» />
<add key=»SiteSqlServer» value=»Server=(local);Database=Database2;uid=userid2;pwd=password2;» />
Hope I haven’t confused you, but I probably have. Any help is greatly appreciated.
Many Thanks,
Mark
- Remove From My Forums
-
Question
-
I have the SQL server 2014 version and I keep coming up with an error message 40. «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) ( Microsoft SQL server, Error: 2) The system cannot find the file specified.»The TCP for Express and SQLServer are also enabled. The Browser is Running but the Express is not. That also gives me an error message. I can not change my settings for Firewall, due to my job’s security.
Can someone please!!!!! HELP ME!!
Answers
-
Hi fenscs08,
(provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL server) ( Microsoft SQL server, Error: 2) The system cannot find the file specified.»
The above error message means that the client can reach the server but there is no Named Pipe listener with specific pipe name on the target machine. It also means the account can have a file sharing session without a problem. Possible reasons are:
a) typo in instance name or wrong instance name. The instance name is not the one you are targeting. Please make sure that you use the correct instance name.
b) Target SQL Server is not running. You can check the SQL Server Services running status in SQL Server Configuration Manager (SSCM).
Lydia Zhang
TechNet Community Support
-
Edited by
Friday, February 27, 2015 3:22 AM
-
Proposed as answer by
Lydia ZhangMicrosoft contingent staff
Thursday, March 5, 2015 3:32 AM -
Marked as answer by
Lydia ZhangMicrosoft contingent staff
Friday, March 6, 2015 9:27 AM
-
Edited by
