Why changing the order of network protocols does not make any sense?

<a href=enter image description here" />

All I want to do is to connect to SQL Server though Named Pipes without using an explicit way like NP:ServerName because mentioned approach works. I want to connect through Named Pipes using just ServerName and give a SQL Server a chance to choose the right network protocol. So I have 3 VMs: What I did step by step:

  1. In Server Configurations of SQL Server A I enabled all network protocols.

<a href=enter image description here" />

  1. I am also familiar with the Order column, however, changing it appears to have no affect. I swapped the order of TCP/IP and Named Pipes: <a href=enter image description here" />
  2. I tried to connect from SQL Server B into SQL Server A using Computer Name(WIN-VKHOKLJ3IJG) and I hoped that it will use Named Pipes protocol but unfortunately it chose TCP/IP as a connection protocol: <a href=enter image description here" />

Could someone explain why TCP/IP was chosen instead of Named Pipes? According the order, SQL Server should choose Named Pipes but it rather preferred to use TCP/IP.

asked Jan 3, 2020 at 18:12 Rauf Asadov Rauf Asadov 1,303 12 12 silver badges 33 33 bronze badges Did you restart SQL Server A after enabling Named Pipes? Commented Jan 3, 2020 at 19:58 Yes. I did it as SQL Server prompts message about restarting it Commented Jan 3, 2020 at 20:04

1 Answer 1

I believe the issue has to do with what client is used when establishing connectivity to Server B from Server A rather than how you order your protocol order on the server itself.

The protocol must be enabled on both the client and server to work. The server can listen for requests on all enabled protocols at the same time. Client computers can pick one, or try the protocols in the order listed in SQL Server Configuration Manager.

I suspect most clients determine the desired order of the protocols being used rather than delegate that to the server.

Further digging through MS documentation yields this interesting note in the Client Protocols Properties (Order Tab) article:

Note

These settings (e.g. Order) are not used by Microsoft .NET SqlClient. The protocol order for .NET SqlClient is first TCP, and then named pipes, which cannot be changed.

As we can see for the .NET SqlClient, if TCP/IP is an enabled protocol on the server and the .NET SqlClient is being used, it doesn't matter what you do, you're getting a TCP/IP connection. So with this client in particular the protocol specified on the server most definitely doesn't matter.

With these examples, I think it's pretty clear that the client you're using to connect to a server has more bearing on the protocol being used rather than the connection protocol ordering on the server itself.

In your specific case, if you really want to use Named Pipes instead of TCP/IP, you may be able to force it via a Client Alias:

<a href=enter image description here" />

You would need to create this alias on Server A for Server B. Just note that aliases come with their own headaches (e.g. you've now got another thing to manage whenever you deal with connectivity changes on Server B), but as a workaround, it may do the trick.

Also of note, Named Pipes can become problematic on slow connections where the same issues are not as impacting via TCP/IP connections, which is likely why most clients gravitate toward the TCP/IP protocol by default.