The symptom
Your ERP, billing or accounting program keeps its data in SQL Server Express on one PC or server. From another PC it shows one of these:
- A network-related or instance-specific error occurred while establishing a connection to SQL Server
- Error 26 - Error Locating Server/Instance Specified
- Error 40 - Could not open a connection to SQL Server
- Or just "server not found".
Yet a connection string that uses a named pipe, such as \\PC1\pipe\MSSQL$SQLEXPRESS\sql\query, works. And ping works.
Why only the named-pipe string works
There are two different paths to SQL Server:
- Named pipes travel over file sharing (port 445). That port is open on any office LAN, so it works.
PC1\SQLEXPRESSuses TCP. The client first asks a service called SQL Server Browser (UDP port 1434) which port the instance uses, because SQL Express picks a random one. It fails if TCP/IP is switched off for the instance (the default on Express), the Browser service is stopped, or the firewall blocks the port.
The fix is to give SQL Server a fixed TCP port so the client can connect without guessing.
Step 1: Check what is running, on the SQL PC
Run PowerShell as Administrator:
Get-Service | Where-Object Name -like "*SQL*" | Select Name, Status, StartType
Get-NetTCPConnection -State Listen | Where-Object { $_.LocalPort -in 1433,1434 }
If nothing listens on 1433, TCP/IP is off or the instance uses a random port. That is the case this guide fixes.
Step 2: Turn on TCP/IP and set port 1433
- Open SQL Server Configuration Manager. Press Start and type it, or run
SQLServerManager16.mscfor SQL Server 2022 (use15for 2019,14for 2017). - Go to SQL Server Network Configuration and then Protocols for SQLEXPRESS.
- Right-click TCP/IP and choose Enable.
- Double-click TCP/IP and open the IP Addresses tab. Scroll to the bottom, to IPAll. Clear TCP Dynamic Ports (leave it empty) and set TCP Port to 1433.
- Click OK. In SQL Server Services, restart SQL Server (SQLEXPRESS).
- Set SQL Server Browser to Automatic and start it.
Step 3: Open the firewall on the SQL PC
New-NetFirewallRule -DisplayName "SQL 1433" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow
New-NetFirewallRule -DisplayName "SQL Browser" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow
Step 4: Test from another PC
Test-NetConnection PC1 -Port 1433
It must show TcpTestSucceeded : True. If it does not, the firewall (or an antivirus firewall such as Quick Heal or Seqrite) is still blocking.
Step 5: Change the connection string
Best option first:
| Connection string | Why |
|---|---|
Server=PC1,1433;... |
Most reliable. No Browser, no instance name. |
Server=tcp:192.168.1.50,1433;... |
Same, by IP, so it doesn't depend on name lookup. |
Server=PC1\SQLEXPRESS;... |
Works now, because the Browser answers. |
Keep the old named-pipe string in a note until TCP works for a few days. If you wonder which of the two to use for the long term, see named pipes or TCP.
If TCP works but you still can't log in
That is a different problem: the SQL login or password. Check that the login exists on this server and that Mixed Mode authentication is on if your software uses sa or SQL logins. A database restored from another server does not bring its logins with it.
Related guides
- Named Pipes or TCP for SQL Server: Which Should Your ERP Use?
- 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