The short answer
Use TCP, on a fixed port such as 1433. On an office LAN the speed is about the same, and TCP breaks far less often.
Why TCP
| Named pipes | TCP | |
|---|---|---|
| Speed on a LAN | Fine | Fine. In practice no slower |
| Depends on | File sharing (port 445) and its Windows login rules | Only its own port (1433) |
| Breaks when | Guest-logon or SMB-signing rules change, for example on a new Windows Server 2022 | Only when a firewall blocks the port |
| Over a VPN or between offices | Poor | Works |
| Same PC as the server | A local connection is fast either way (SQL Server can use shared memory) | The same |
The important row is breaks when. Named pipes ride on file sharing, so anything that goes wrong with file sharing, as in this Server 2022 case, can take the database connection down with it. A database connection should not depend on that.
Why so many older programs use named pipes
Named pipes are often on by default and work without any setup on a small LAN, so many old programs were set up with them. TCP/IP is off by default on SQL Server Express, so someone has to turn it on once.
How to switch
- Turn on TCP/IP, set the fixed port and open the firewall. The steps are in ERP can't find SQL Server.
- Test with
Test-NetConnection PC1 -Port 1433. - Change the program's connection string. A named-pipe string looks like this:
Server=\\PC1\pipe\MSSQL$SQLEXPRESS\sql\query;Database=MyDb;...
Replace it with a TCP one:
Server=PC1,1433;Database=MyDb;...
Adding tcp: in front of the server name (Server=tcp:PC1,1433) forces TCP even if the client would otherwise try another method.
- Keep the old string until TCP has run without trouble for a few days.
When named pipes are fine
Small offices where everything sits on one PC, or a LAN where file sharing has never been a problem. If it works and nothing changes, there is no need to touch it. But when you move to a new server or Windows version, switch to TCP then.
Related guides
- ERP Can't Find SQL Server: 'PC\SQLEXPRESS' Not Found (Error 26 or 40)
- Ping Works, but the New Windows Server Can't Open Other PCs' Shared Folders
- After Installing a VPN, the Server Can't Find Office PCs by Name
- Remote Desktop Won't Connect Even Though the Firewall Rule Is Right
← All troubleshooting guides