1

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:

enter image description here

What I did step by step:

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

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

Improve this question
3
  • Did you restart SQL Server A after enabling Named Pipes?
    – Josh Darnell
    Jan 3, 2020 at 19:58
  • Yes. I did it as SQL Server prompts message about restarting it
    – Rauf Asadov
    Jan 3, 2020 at 20:04
  • Someone also faced with this problem but nobody was able to give precise answer. sqlservercentral.com/forums/topic/…
    – Rauf Asadov
    Jan 3, 2020 at 20:05

1 Answer 1

Reset to default
3

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 first hint comes from Choosing a Network Protocol: Enabling the Protocol (highlighting mine):

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:

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.