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();
…
5条答案
按热度按时间yshpjwxd1#
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.
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.
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.
lvmkulzt2#
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 Configurationand it can also be overriden in the connection string.To change the default protocol or the protocol order for client computers
To configure a client to use TCP/IP
See Configure Client Protocols
sy5wg1nm3#
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:
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
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.
afdcj2ne4#
what about the server side?
what I would double check in SQL Server instance configuration on the servers:
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...
1zmg4dgp5#
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.