Home / server / Questions / 830533
In Process
test
Asked: 2017-02-04 20:18:00 +0800 CST

How can I force a linked SQL Server to use TCP/IP instead of Named Pipes?

  • 772

I have a SQL Server instance, Instance1, that has a linked SQL Server, Instance2. 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.

Can I force the linked SQL Server on Instance1 to use TCP/IP when connecting to Instance2? I have not been able to find any information relating to this, do I have any other options if this can't be done?

I tried creating the linked server by specifying the port but it seems to use Named Pipes anyway, i.e., I created it as Instance2,1433. Is there maybe a way to tell the master database to use TCP/IP for linked servers?

Any help is greatly appreciated. I'm hoping the answer is not "Turn off Named Pipes in Instance2 SQL configuration" although I realize I might have to do that if I can't get this to work.

Edit: I have without a doubt verified that it is Named Pipes that is causing the problem, and I've seen this same problem with Named Pipes before (it's well documented behaviour of Named Pipes). I just haven't seen how to solve it when using Linked Servers.

sql-server tcpip named-pipes

1 Answers

  1. I tried, with success, the Connection String method to force a protocol: Data Source=protocol:servername, portnumber;

    Example:

    EXEC master.dbo.sp_addlinkedserver @server = N'DB_OBRA', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'tcp:10.0.34.33'
    • 2