Provider named pipes provider error 40 ошибка

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:

1. Combination of default instance and one or more named instances
2. 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

1. 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.

2. 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:

1. .
2. (local)

While connecting to the SQL Server service instance from a remote machine only standard names will work.

First published on MSDN on Mar 31, 2007

This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting tips for this specific error message.

First, take a look at below MSDN forum link lists about this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1287189&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1334187&SiteID=17

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1292357&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

The various causes fall into five categories:

1 Incorrect connection string, such as using SqlExpress.

2 NP was not enabled on the SQL instance.

3 Remote connection was not enabled.

4 Server not started, or point to not a real server in your connection string.

5 Other reasons such as incorrect security context.

Let’s go throught the detail one by one:

I.   Incorrect connection string, such as using SqlExpress.

Check out:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

The typical error when dealing with Express includes:

a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify «.»,»localhost» etc instead of «.SqlExpress» or «<machinename>Sqlexpress».

b. Np was disabld by default after installing SqlExpress.

c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

Please read the following blog for best practice of connecting to SqlExpress.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

II. NP was not enabled on the SQL instance.

Check out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

1) Go to SQL Server Configuration Manager, See Server has NP enabled.

2) %windir%program filesmicrosoft sql servermssql.1mssqllog, notepad ERRORLOG, see whether Server is listening on NP.  You should see «Server named pipe provider is ready to accept connection on [

.pipesqlquery

] or [.pipemssql$<InstanceName>sqlquery]»

3) Notice that «sqlquery» is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is «sqlquery1», then you would see in the errorlog that server listening on [

.pipesqlquery1

], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch «cliconfg.exe» and make sure NP enabled and right pipe name specified.

III. Remote connection was not enabled.

Check out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

a. «File and Printer Sharing» was opened in Firewall exception list.

b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issu…

IV. Server not started, or point to not a real server in your connection string.

Check out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

a. use «sc query mssqlserver» for default instance or «sc query mssql$<instancename>» to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it’d better for you to double check.

b. User specified wrong server in their connection string, as described in the forum discussion, «MSSQLSERVER» is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename><instancename> as data source in your connection string.

V. Other reasons such as incorrect security context.

Check out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

a. During data operation, you are normally asked to type in the destination server name whether it is default to «(local)» or another server «<remotemachinename>». So, remember the exact string that represent the target instance, then when the error repros, open command line, use «sqlcmd -S<representitive> -E» ,see what happens, if the connection fail, please follow up above I — IV troubleshooting lists. otherwise continue.

b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

Summary, give checklist:

1. Is your target server started?

2. Is your target server listening on NP? Which Pipe?

3. Has your client enabled NP? Use the same pipe to connect as Server?

4. Are you making local connection? If so, what is the instance, default or remote?

5. Did you put correct instance name in the connection string? Remember, Sqlexpress is a named instance.

6. Did you enable remote connection? Firewall? IPSec? «File and Printer Sharing» opened? Can access server?

7. Can you make basic connection by using <servername> or <servername><instancename>? Use sqlcmd or osql.

8. What is your repro step? What was your client APP doing during this error occuring? Which DB operation, detail?

MING LU

SQL Server Protocols

Disclaimer: This posting is provided «AS IS» with no warranties, and confers no rights

Regular readers of my blog are aware of the fact that I have written about this subject umpteen times earlier, and every time I have spoken about a new issue related to it. Few days ago, I had redone my local home network. I have LAN setup with wireless router connected with my four computers, two mobile devices, one printer and one VOIP solution. I had also formatted my primary computer and clean installed SQL Server 2008 into it. Yesterday, incidentally, I was sitting in my yard trying to connect SQL Server located in home office and suddenly I stumbled upon the following error. Finding the solution was the most infuriating part as it consumed my precious 10 minutes.

Let us look at few of the common errors received:

An error has occurred while establishing a connection to the server.

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

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) (Microsoft SQL Server, Error: 1326)

I eventually found the resolution to this error on this blog itself; so I was saved from hunting for the solution. I am sure there are a number of developers who had previously fixed this error while installing SQL Server 2008 or SQL Server 2005 but in due course forgot the right solution to this error. This is just a quick refresher. Make sure to bookmark this as you never know when you would need this solution.

Let us check into the steps to resolve this error.

1) SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network.

I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.

2) Enable TCP/IP in SQL Server Configuration

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

2) Enable TCP/IP in SQL Server Configuration

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

Right Click on TCP/IP >> Click on Enable

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

3) Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running by firewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser should also be added to the exception, as described in Step 7.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

3) Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running by firewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser should also be added to the exception, as described in Step 7.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP

Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP

4) Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

4) Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

Go to Left Tab of Connections and check “Allow remote connections to this server”

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

Right Click on SQL Server Browser >> Click on Enable

6) Create exception of sqlbrowser.exe in Firewall

As elucidated in Step 6, sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to add exception for the same in windows firewall.

Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe and create the exception of the file in Firewall, as delineated in Step 3.

7) Recreate Alias

It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply create alias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases

Delete the alias that is giving problem and recreate it with identical parameters.

I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Reference : Pinal Dave (https://blog.sqlauthority.com)

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.

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 – “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.

I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Reference : Pinal Dave (https://blog.sqlauthority.com)

Источник

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.

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 – “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.

Источник