Yes, I know SharePoint 2010 is out but in case anyone is interested in knowing what I found out 3 years back:
As per the documentation in the MOSS installation guide, it’s been noted that both TCP/IP and Named Pipes are supposed to be turned on. What’s interesting is that I’ve come across an environment with a farm setup that only had TCP/IP turned on yet was still functioning. I spoke with a Microsoft Engineer about this and they couldn’t really explain it either. In any case, best practice is to follow the guide to ensure this is turned on.
Configure surface area settings in SQL Server 2005
1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. In the SQL Server 2005 Surface Area Configuration dialog box, click Surface Area Configuration for Services and Connections.
3. In the tree view, expand the node for your instance of SQL Server, expand the Database Engine node, and then click Remote Connections.
4. Select Local and Remote Connections, select Using both TCP/IP and named pipes, and then click OK.
