Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Why Use Named Pipes?

The other day a colleague and I were shadowing a vendor who was installing their 3rd party application on a hosted server. The vendor went ahead and created an alias for the application but initially created it with Named Pipes. Needless to say, the alias didn't work. Given that this application is hitting against a SQL Server 2005 installation, the Named Pipes protocol is disabled by default for remote connections. Only TCP is enabled. The vendor originally went back and configured the alias to use TCP, which did work. But this got me to thinking, "Why did the vendor choose Named Pipes?"

If we look in Books Online, we see the following quote:

Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

This caused me to pause. Over the network I've seen issues with Named Pipes, such as unexpected disconnects that I've not seen with TCP/IP. I saw this last year, as a matter of fact, when an application would periodically disconnect from the database. The problem was that this application did not attempt to reconnect nor did it fail gracefully. It crashed out every time it lost the database connection. This application commited one of the fallacies of distributed computing: assuming the network is reliable. And this is on a modern switched LAN at typical LAN speeds.

So I'm not in agreement with the assessment given in Books Online. As a matter of fact, I would definitely disagree with respect to SQL Server 2005. In SQL Server 2005, only the TCP/IP protocol supports Kerberos authentication, which overall is a better security protocol than NTLM. Named Pipes has to use NTLM. Until SQL Server 2008, there is no option to use anything but. And even in SQL Server 2008, it means being aware of a potential additional SPN that's not needed for TCP/IP.

Which all leads to the question that's still nagging at me, "Why use Named Pipes? What's the benefit of doing so over the default of TCP/IP (or shared memory, for local connections)?"

 

Comments

Posted by Abbs on 11 August 2009

I had to turn named pipes on in order to import a pipe delimited data extract using a vendor tool. I find that pipe delimited files are more robust than CSV as commas and double quotes are often used in text strings in the files that I import.

Posted by Anonymous on 11 August 2009

Pingback from  Dew Drop – August 11, 2009 | Alvin Ashcraft's Morning Dew

Posted by K. Brian Kelley on 11 August 2009

Abbs, I understand what you're saying, but what I'm speaking of is one of the ways to communicate with SQL Server, not how data is delimited within an extract file.

Posted by gideon_king on 19 October 2010

The main reason to use Named Pipes is so all clients can connect to your server. We have found that Access Applications connecting in with ODBC almost always use TCP/IP but on some occations use Named Pipes, the Access Link Table was established with Named Pipes. The only solution to this is drop and recreate the Linked Table on the Client or enable Named Pipes on the server.  thx

Leave a Comment

Please register or log in to leave a comment.