DB-유

Enabling TCP/IP and Named Pipes Protocols in SQL Server

· mssql, networking, configuration manager, connectivity

Every time I install a Developer or Express instance, the first connection from another machine dies with "error: 26" or "error: 40", and every time I have to remember why. A fresh install only answers locally through Shared Memory; TCP/IP is off. Here's my routine for SQL Server 2012 through 2017 on Windows: enable the protocols, pin the port, open the firewall, confirm it's listening.

Step 1: Open Configuration Manager

It's an MMC snap-in, and its shortcut often vanishes from the Start menu. Launch it directly by version:

SQLServerManager14.msc   # SQL Server 2017
SQLServerManager13.msc   # SQL Server 2016
SQLServerManager12.msc   # SQL Server 2014
SQLServerManager11.msc   # SQL Server 2012

Expand SQL Server Network Configuration → Protocols for MSSQLSERVER (or the instance name). You will see Shared Memory, Named Pipes and TCP/IP with their status.

Step 2: Enable TCP/IP and set a fixed port

Right-click TCP/IP → Enable. Then open its Properties and go to the IP Addresses tab. Scroll to the bottom to the IPAll section:

  • Clear TCP Dynamic Ports (delete the 0 or the random port number there).
  • Set TCP Port to 1433 for a default instance, or a fixed port like 1434–1500 range for named instances, for example 1450.

A named instance defaults to a dynamic port that can change after a restart, which forces clients to rely on the SQL Browser service (UDP 1434) to find it. I always pin the port. A fixed port removes the Browser dependency and makes firewall rules sane.

The same settings live in the registry if you need to script them, for example on SQL Server 2017:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
    TcpDynamicPorts = ""
    TcpPort = "1433"
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp
    Enabled = 1

With PowerShell and the SQL Server WMI provider, the whole thing is:

$wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'
$tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$tcp.IsEnabled = $true
$tcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value = ''
$tcp.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = '1433'
$tcp.Alter()

(Import-Module SqlServer or loading the SMO assemblies is required first.)

Step 3: Named Pipes, only if you need it

I leave it off unless something asks for it. Named Pipes is mostly useful on the local machine and in domains where Windows authentication over SMB is already trusted; it is not needed for typical TCP clients and is slower over a WAN. Some older tools and a few third-party backup agents still ask for it. Right-click Named Pipes → Enable. The pipe name defaults to \\.\pipe\sql\query for a default instance and \\.\pipe\MSSQL$INSTANCE\sql\query for a named one.

If you enable it, clients connect with np: in the server name: sqlcmd -S np:\\sqlprod01\pipe\sql\query.

Step 4: Restart the service

Protocol changes take effect only after a restart of the Database Engine service. Do it from Configuration Manager under SQL Server Services, or:

Restart-Service MSSQLSERVER -Force

The -Force is needed because the Agent service depends on the engine. After the restart, the ERRORLOG should contain a line like:

Server is listening on [ 'any' <ipv4> 1433].

Check it with:

EXEC xp_readerrorlog 0, 1, N'listening';

Step 5: Open the Windows Firewall

Setup does not create firewall rules. Add them for the engine port and, if you use named instances without fixed ports, the Browser:

New-NetFirewallRule -DisplayName "SQL Server 1433" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow -Profile Domain,Private
New-NetFirewallRule -DisplayName "SQL Browser" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow -Profile Domain,Private

For Named Pipes, file and printer sharing (TCP 445) must be allowed, which is usually already the case on a domain.

Is it really listening?

From the server itself:

netstat -ano | findstr :1433
Get-Process -Id (netstat -ano | findstr :1433 | ForEach-Object { ($_ -split '\s+')[-1] } | Select-Object -First 1)

sqlservr should own the listening socket. From a client machine:

Test-NetConnection -ComputerName sqlprod01 -Port 1433
sqlcmd -S tcp:sqlprod01,1433 -E -Q "SELECT net_transport, local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID"

The tcp: prefix forces the protocol, so you know the test used TCP and not a fallback. net_transport should return TCP.

Error messages and what they usually mean

  • "A network-related or instance-specific error occurred ... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)" – the client cannot resolve the named instance. Either the Browser service is stopped and blocked, or you should connect with server,port instead of server\instance.
  • "(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" – the client fell through all protocols and ended with Named Pipes. Usually TCP/IP is still disabled or the firewall blocks 1433.
  • "TCP Provider: No connection could be made because the target machine actively refused it." – the port is reachable but nothing is listening on it. The service was not restarted after the change, or a different port is configured under an individual IP entry (IP1, IP2) with Active = Yes overriding IPAll.
  • Connections work by IP but fail by name with a Kerberos error. Register an SPN for the service account: setspn -S MSSQLSvc/sqlprod01.example.com:1433 CONTOSO\svc_sql.

Fixed port, matching firewall rule, Browser only where named instances need it. I do this right after setup on every instance now.