SQLConnection use randomly namedpipes instead of tcp

Question

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(); 
…

Blackboard

can you post your connection string code – Slew

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

Either is a web server or client's pc connection to the sql server. – Blackboard

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

SERVERNAME is just an example of our connection string format. We have the actual server name in our test application. – Blackboard

I have the similar problem: c# program (WCF, net framework 4.8) running on clean Win 2016 server rarely (once a week) tries named pipes instead of tcpip when connecting to sql 2017. There are no sql client settings in registry. Have you managed to find the root cause of such behavior? – Selfdenial

@AntonKrouglov, the reason the app tries named pipes is simply because the TCP connection failed. The client driver catches the TCP connection failure and then tries to use named pipes as a fallback, obfuscating the original TCP error. – Overline

@DanGuzman indeed, that is true. The funny thing is that even if tcp 1433 connection attempt was successful, but took too long, client library tries to ping 1st, named pipes 2nd and only then throws exception. – Selfdenial

Answer 1

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.

Neurosurgery

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

This is only testing code, we are not compiling our connection string btw. – Blackboard

I appended my answer with a new suggestion. – Neurosurgery

That's a solid approach especially in an enterprise environment with many client machines (+1). – Hansiain

@Igor. Right, the DSQUERY stuff goes right in line with your answer. Just another piece of the puzzle. Upvoting your answer as well. – Neurosurgery

We check the web server that try to connect and it doesn't have the ConnectTo registry. Then we checked the sql server and the registry is there but nothing is set. – Blackboard

You can also look here: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo (I think they will be here if you installed 64 bit version of Sql Server) ("WOW6432Node" being the magic subkey) – Neurosurgery

Our server is 64 bit and I checked the this registry doesn't exits. – Blackboard

No values means it is probably using default values. If you want to "prove" that it is the network library causing the issue, you can use this temporary trick. Go to ODBC (Control Panel / Admin Tools) and create a System DSN. Add a new entry choosing "SQl Server" as the Driver. Do not pick "Sql Server Native Client XX.YY". The most important button to pick in the wizard is "Client Configuration". Here you will pick "tcp/ip". Finish the wizard, test connection (go back if test fails). Now test your server. And look at the two regedit keys mentioned before to see how/what was written – Neurosurgery

In my case (because I picked tcp/ip and "dynamically pick port", my registry had this in it : Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo] "MyMachine\\MyInstance"="DBMSSOCN,MyMachine\\MyInstance" – Neurosurgery

Answer 2

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

Hansiain

@Blackboard - good as in it looks correct but the problem is persisting or good as in this solved the problem? – Hansiain

Sorry, the problem is still there. It's the order that is good. (my bad) – Blackboard

SERVERNAME isn't an FQDN so the driver has to try named pipes first. Either use a full name or specify the TCP library – Tribune

Answer 3

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?

It is the client rather than the server that determines the protocol order. Most client drivers will use TCP/IP first and then fall back on named pipes when the TCP connection fails. Below is the relevant excerpt from the documentation that applies to SqlClient as well.

For example, if a client computer has both TCP and Named Pipes available, and the order is:

• TCP
• Named Pipes

When the client computer tries to make a TCP connection to the server and the connection attempt returns a non-zero return code, the client transparently tries a connection by using the next protocol in the list, which is Named Pipes. In this scenario, the client cannot make a TCP connection; however, the client successfully makes a Named Pipes connection.

Note

The client does not receive an error that indicates the first protocol failed.

If the client application uses the second protocol, and it also returns an error, an error is returned to the client.

So to answer your question more directly, named pipes is attempted only because the initial TCP connection failed. The client error message indicates a named pipes connection failure but that's just a side effect of the initial TCP connection failure. The root cause based on your symptoms is an intermittent database or network connectivity problem, not at all related to using named pipes.

Overline

Answer 4

what about the server side?

what I would double check in SQL Server instance configuration on the servers:

• which protocols are enabled on the servers behaving as expected
• which protocols are enabled on the server that's showing that unexpected behaviour

my guess is that the 'broken' server has named pipes enabled while other servers does not so when the .NET client is choosing the connection protocol sometimes makes the wrong choice.

that's a wild guess because I don't know how the choice of the connection protocol is made by .NET...

Watkin

We checked the sql server config and everything is int the right order. Our application and web server takes the tcp protocol for a certain amount of time and out of the sudden takes the namedpipes and we received an error. After we try again and it takes the tcp. – Blackboard

That does not cover my suggestion. The configuration is the same of the 'properly working' servers? – Watkin

What about disabling named pipes on the server you are having issues with? – Watkin

Yeah, all of our sql servers has the same configuration. No difference.We tried to found out what cause this problem that recently surface without any changes that we know between applications and sql servers. – Blackboard

Answer 5

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.

Gon