SQLConnection use randomly namedpipes instead of tcp

sql server connection problem
provider: named pipes provider, error: 40 - could not open a connection to sql server
sql server connection string protocol
sql server connection string shared memory
microsoft sql server error 2
sql server error 1326
tcp provider, error: 0
cannot connect to sql server after windows update

SQLConnection use randomly namedpipes(445) instead of tcp(1433). The namedpipes port is blocked by our firewall but not the tcp. This only happens when trying to connect to one of our sql servers. Most of the time the application use the tcp but randomly is trying to use namedpipes port. Our sql connection is very simple and we doesn't do something fancy with it.

We don't want to hardcoded the tcp port on our connection string. We already try and it's fixed the problem. The problem only appears during the last week and our web application that try to connection is live for a while.

Why the sql connection sometimes trying to connect with 445 instead of 1433? Is it a bug introduced by .net latest updates or does the server can dictate the next port to use?

UPDATE 2016-09-23 11:00

Here's a sample of the code we are using to connect

string connectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASE;uid=username;pwd=mypass;MultipleActiveResultSets=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    try {
        connection.Open(); 
…

We don't want to hardcoded the tcp port on our connection string.

You don't necessarily have to put the IP address/Port# in your connection string.

BUT, you can force the network protocol in the connection string.

Network Library=DBMSSOCN;

https://www.connectionstrings.com/define-sql-server-network-protocol/

But when I've had random named-pipes issues that slow performance, I make the connection string as "specific" as possible. Which is...specify the network-library and the ip address and the port number.

By the way, I really really hope you are not actually coding your connection string in compiled code, and the below is not your actual code.

string connectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASE;uid=username;pwd=mypass;MultipleActiveResultSets=True";

APPEND:

You can "fish" around this registry-setting on the problem machines.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\

I would look specifically if DSQUERY is set or not set.

https://support.microsoft.com/en-us/kb/328306

Check the protocol that is specified in the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\DSQUERY This value typically reflects the settings in the CNU, but sometimes it does not.

If the value is DBNETLIB, it uses one of the protocols in the enabled protocols list of the CNU. If a specific protocol is listed, that protocol is used instead.

If this is the issue.........ultimately, any other program can alter this value. So you don't have full-control. Again, the better solution is to put the network-library in the connection string, so "outside forces" cannot change the game on you, mid-game. The reason I know this is because I got burnt at a client site......some other program changed the DSQUERY value one about 1/3 of the client machines (that also was using our application) and our application performance went to a crawl. Aka, some other company....did something to make our application performance horrible. So instead of fighting it, I put in the network library in our connection-string to permanently address the issue.

Named pipes port number, are also used for named pipes when communicating with remote machines. The linked server technically works, however I am positive that Instance1 is connecting to Instance2 via Named Pipes. I can see this very clearly by testing a sample query using TCP/IP and Named Pipes. Query run times are a difference between <1 second for TCP/IP and >45 seconds for Named Pipes.

You never mention if this connection is made from end user PCs, a web server, other db servers, etc. However, whether named pipes is used or TCP as the primary protocol is a setting on the PC that creates the connection. This can be configured using SQL Server Native Client Configuration and it can also be overriden in the connection string.

To change the default protocol or the protocol order for client computers
  1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Client Protocols, and then click Properties.
  2. In the Enabled Protocols box, click Move Up or Move Down, to change the order in which protocols are tried, when attempting to connect to SQL Server. The top protocol in the Enabled Protocols box is the default protocol.
To configure a client to use TCP/IP
  1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Client Protocols, and then click Properties.
  2. In the Enabled Protocols box, click the up and down arrows to change the order in which protocols are tried, when attempting to connect to SQL Server. The top protocol in the Enabled Protocols box is the default protocol.

See Configure Client Protocols

Resolve Error 40: Could Not Open a Connection to SQL Server, Can't open a connection to SQL Server Named Pipes Provider? SQLConnection use randomly namedpipes (445) instead of tcp (1433). The namedpipes port is blocked by our firewall but not the tcp. This only happens when trying to connect to one of our sql servers. Most of the time the application use the tcp but randomly is trying to use namedpipes port. Our sql connection is very simple and we doesn't do something fancy with it.

What are named pipes?, SQLConnection use randomly namedpipes(445) instead of tcp(1433). However, whether named pipes is used or TCP is a connection setting  1 SQLConnection use randomly namedpipes instead of tcp Sep 23 '16 0 Microsoft Graph Missing Sites Sep 11 '18 0 Windows ReportViewer Export to Excel(2013) is Removing Hyperlinks Oct 7 '15

I had this issue. I suspect it was either AppLocker restrictions OR running the app off a network drive that caused this. I copied the app locally and resolved the applocker restrictions and now it seems to use the correct port.

I received no errors and even forcing parameters with the connection string did not work to fix it. Now it uses port 1433 as expected.

c#, These errors could be for either Named Pipes connections or TCP/IP connections​. Now I should be able to use the machine name instead of the IP but when I logged on to his computer, my SQL connection worked fine. When I try opening the SqlConnection using TCP provider (Network Library=DBMSSOCN or Data Source=TCP:HHVSVTSQL01) , there is NO network activity. Just to be sure, I switched back to the original ConnectionString and WireShark again shows NamedPipes access attempts (SMB protocol). This is CRAZY. .NET's not even trying to connect using TCP provider.

Resolving could not open a connection to SQL Server errors, Named pipes: Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ]. TCP/IP and port number: Server is listening on [ ::1 1433]. Check if you are able to connect to SQL server using a UDL file – if it works, to do a doublehop, but NTLM credentials are being used instead of  Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string.I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

Solving Connectivity errors to SQL Server, It turns out the problem is related to missing TCP/IP protocol support which is disabled by default. Everytime I make a new SQL Connection there's a 2 second delay for the connection to occur. I'm pretty sure that I've used Named Pipes in the past and didn't see this Use localhost or an FQDN instead. This article describes how to programmatically specify the client network library in the connection string when you connect to a SQL Server database. In Microsoft Data Access Components (MDAC) 2.6 and later, you can specify the client access library by using the server name parameter in connection string. Therefore, you can specify a specific

Slow Connections with Sql Server, Navigate to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <machine instance>. Double-click Named Pipes. The Named  To create a valid connection string using TCP/IP, you must: Specify an Alias Name. For the Server, enter either a server name to which you can connect using the PING utility, or an IP address to which you can connect using the PING utility. For a named instance append the instance name. Specify TCP/IP for the Protocol.

Comments
  • can you post your connection string code
  • You never mention if this connection is made from end user PCs, a web server, other db servers, etc. However, whether named pipes is used or TCP is a connection setting on the local PC that initiates the connection. See msdn.microsoft.com/en-us/library/ms181035.aspx for additional details.
  • Either is a web server or client's pc connection to the sql server.
  • No bug, it's what your connection string requested. SERVERNAME is not an FQDN, so can't be considered a TCP server name. The SQL Server will have to check each available protocol to determine which one can actually connect. If named pipes is enabled, it will be tried first.
  • SERVERNAME is just an example of our connection string format. We have the actual server name in our test application.
  • We know that and we don't want to specified the protocol to use because everything works fine since the web application is live (2 years ago). Why out of the sudden the sql connection has this weird behavior to use 445.
  • This is only testing code, we are not compiling our connection string btw.
  • I appended my answer with a new suggestion.
  • That's a solid approach especially in an enterprise environment with many client machines (+1).
  • @Igor. Right, the DSQUERY stuff goes right in line with your answer. Just another piece of the puzzle. Upvoting your answer as well.
  • @sebascomeau - good as in it looks correct but the problem is persisting or good as in this solved the problem?
  • Sorry, the problem is still there. It's the order that is good. (my bad)