TL;DR: Your SQL Server instance is using dynamic ports due to which it is not working. Force the SQL Server service to use static port # 1433 to get it working.
Detailed Explanation: Before starting with the details, let us first understand the scenario in which this issue can occur.
Whenever we run SQL Server setup on a machine then it asks us to input the name of the service instance. Why it asks for it? The reason is that the SQL Server setup provides a facility through which you can install and run multiple instances of SQL Server service on the same machine. There can be variety of reasons due to which you may want to run multiple SQL Server service instances on the same box e.g. unavailability of more physical servers in your estate, isolating SQL Server instance failures, load balancing, etc. To differentiate between multiple instances on the same machine, you must give a unique name to each service instance. Have a look at my machine (Refer screenshot). I have only one service instance and it is the default service instance of SQL Server:
For each new instance that you setup via SQL Server installer, there will be a new row in this service console window with same SQL Server prefix in the Name column. Text in parenthesis (MSSQLSERVER) is the name of the service instance. To understand the naming convention of SQL Server service instances, please refer to the corollary section in the end of the answer.
So, if you have more than one instance of SQL Server service running on a given machine then this problem can occur. When you have two or more than two SQL Server instances running on the same box then there are two possible configurations:
- Combination of default instance and one or more named instances
- Combination of two or more named instances only i.e. no default instance. SQL Server setup does not mandates the installation of default instance if you wish to. I had followed this configuration on my server when I faced this issue.
Key concept: Each instance of Microsoft SQL Server service installed on a machine uses a different port to listen for incoming SQL connection requests. In other words each SQL Server service is a different process which is attached to a unique port number. Default instance of SQL Server uses port # 1433. As you install named instances then they will start using dynamic ports which is decided at the time of start-up of Windows service corresponding to named SQL Server instance.
My code was failing (with error code 40) to connect to the only named SQL Server instance that I had on my VM. You can try below possible solutions:
Solution # 1: Client code trying to connect to SQL Server instance takes help from SQL Server browser service to figure out port number at which your named instance is listening for incoming connections. Make sure SQL browser service is running on your computer.
Solution # 2: Check the port # (in yellow color) your named SQL Server instance is using from SQL Server configuration manager as shown in the snapshot below:
Use that port number explicitly in your connection string or with sqlcmd shown below:
sqlcmd -s mymachinename,11380 -i deleteDB.sql -o SQLDelete.txt
Solution # 3: Force your named instance to use port # 1433 which is used by the default instance otherwise. Remember this solution will work only if you do not have any default SQL Server instance running on as machine as the port # 1433 would be already in use. In any operating system, a given port number can never be used by two processes at the same time. At the end of day, our SQL Server service instance is also a process.
Set TCP Dynamic ports field to blank and TCP Port field to 1433.
Change the port number in your connection string as shown below:
sqlcmd -s mymachinenameinstanceName -i deleteDB.sql -o SQLDelete.txt
OR
sqlcmd -s mymachinename,1433 -i deleteDB.sql -o SQLDelete.txt
Note: Please restart the SQL Server service instance after saving the TCP/IP settings.
Interestingly enough after resolving the error when I went back to dynamic port setting to reproduce the same error then it didn’t happen. Not sure why.
Please read below interesting threads to know more about dynamic ports of SQL Server:
How to configure SQL Server Port on multiple instances?
When is a Dynamic Port “dynamic”?
When to use a TCP dynamic port and when TCP Port?
I got leads to solution of my problem from this blog.
Corollary: Name of a SQL Server instance follows below mentioned logic
-
For default instance, it is same as the name of the machine hosting the SQL Server service. It is predefined. It cannot be changed by the user during installation. Don’t get confused by the suffix (MSSQLSERVER) present in the service name. It is an internally managed name relevant for server side only. Client side apps will never get to know that.
-
For named instance, it is a concatenated string in specific format as shown here — <name of the machine hosting the SQL Server service><user defined instance name>
Pro Tip: Default SQL Server instance can be connected via few alias names as well which are convenient to type and intuitive to remember. But alias names can be used only while you are trying to connect to the service instance from the service host machine itself. In such a scenario, in place of host name you can also use below mentioned alias names:
- .
- (local)
While connecting to the SQL Server service instance from a remote machine only standard names will work.
Fix SQL Server Management Studio Error 40
Synopsis: The sole purpose of this article is to explain the SQL server error 40 & its possible solutions to users. The task isn’t quite tough to tackle if the users are aware of the right techniques. Just like error 983, error 1105, error 17053, etc, this also troubles users with a message that says could not open a connection to the SQL server database. Let’s dive deep into the topic with expert guidance.
Table of Content
- An Introduction
- Possible Solution
- Conclusion
Introduction
Evidently, 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: 40 – could not open a connection to SQL server 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 is 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. Otherwise, its not going to solve the error 40 in SQL server.
- 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 to move one step closer of solving this error 40 smartly.
-
- 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
Finally, 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
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.
Ошибка SQL Server 40 не удалось открыть соединение с sql server 2016
Связанные с этим ошибки:
Ошибка 40 не удалось открыть соединение с sql server
поставщику именованных каналов не удалось открыть соединение с sql server 53
вызов поставщика sqlcmd с именем pipes provider ошибка 40 не удалось открыть соединение с sql server
поставщик именованных каналов не смог открыть соединение с SQL server 5 linked server
причины ошибок:
Эта ошибка возникает, когда SQL Server не может быть найден в Сети, что означает либо IP-адрес недоступен, либо номер TCP-порта либо не открыт, либо не является правильным номером порта, либо заблокирован брандмауэром и т. д
Решение:
Приведенные ниже шаги помогут устранить эти проблемы
1.Проверьте, можете ли вы пинговать сервер
2.Проверьте, открыт ли брандмауэр для подключения
3.Проверьте, работает ли служба SQL Server и если именованный экземпляр, то служба браузера
4.Проверьте, включен ли протокол TCP / IP в диспетчере конфигурации SQL Server
5.Проверьте, установлен ли в свойствах SQL Server параметр “Разрешить удаленные подключения” на уровне экземпляра
Для экземпляра по умолчанию используется порт TCP / IP 1433, но это может быть изменено вручную. Поэтому пожалуйста проверьте TCP IP порт все еще находится на уровне по умолчанию 1433
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.