Disabling the named pipes protocol for SQL Server using Powershell

This is my first ever T-SQL Tuesday blog post – big thanks to Rob Sewell for being this month’s host and of course for picking such a fascinating topic as Powershell. To learn more about T-SQL Tuesday, check out the #tsql2sday hashtag on Twitter and read Rob’s introductory post for the 94th T-SQL Tuesday blog party.

Windows and POSIX systems both support something referred to as “named pipes”, although they are different concepts. For the purposes of this post I am referring only to the Windows version. By default on most editions of SQL Server (every edition except Express Edition), there are three supported and enabled protocols for SQL server to listen on – Shared Memory, TCP/IP and Named Pipes. The inclusion of named pipes has always confused me somewhat. In theory, named pipes allow communication between applications without the overhead of going through the network layer. This advantage disappears when you want to communicate over the network using named pipes. In all modern versions of SQL Server, named pipes does not support Kerberos, so for most shops you likely will not be using or should not be using named pipes to communicate with SQL Server.

Security best practices dictate that if you are not using a particular protocol, you should disable it. There is an option to disable this is in the GUI in Configuration Manager but since this T-SQL Tuesday blog post is about using Powershell it does not make sense to cover it here. Nor is it particular easy to use the GUI to make a configuration change across hundreds of SQL instances. Unfortunately, I have not found a good way to make this change that does not involve using WMI, if anybody is aware of a better method, I welcome your feedback.

This particular script grabs a list of servers using Get-CmsHosts but you could easily pull from a file instead using get-content. Get-CmsHosts is a Powershell function written and maintained by my colleague Mark Wilkinson, he blogs about it here and you can easily grab a copy of it from Github. Another interesting use case for Get-CmsHosts is covered by another colleague Tracy Boggiano here. As you can probably tell, we use this little Powershell gem extensively at work.

Disabling named pipes requires a SQL Server restart to take effect so I have included a commented out command to restart a default instance of SQL server here as well.