How a client finds SQL Server: instances, SQL Browser and ports

After the monthly updates and a restart of the database server, the HR application can no longer connect to its database. Its log contains this message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

The instance service is running, and SSMS started on the server itself connects without any problem. What is stopped is the SQL Server Browser service, whose startup type someone had earlier changed to manual. The application connects with the address srv-sql-01\HR, and without that service it has no way to learn the instance port.

To establish a connection, the client needs the server address, the protocol, and the port or pipe name of the instance. The connection string supplies some of this, and the driver works out the rest on its own. This post shows where it gets each piece and how to build a connection that does not depend on services that may fail to start after a restart.

The default instance and named instances

One server can run one default instance and many named instances. Each is a separate Windows service with its own sqlservr.exe process, its own network configuration and its own port:

MSSQLSERVER    default instance         srv-sql-01         TCP 1433
MSSQL$CRM      named instance CRM       srv-sql-01\CRM     TCP, dynamic port
SQLBrowser     SQL Server Browser       -                  UDP 1434

The default instance has no name in the address: the client connects with the server name alone and port 1433, and over named pipes with the pipe \\srv-sql-01\pipe\sql\query. A named instance has the service MSSQL$NAME, the address server\NAME and the pipe \\srv-sql-01\pipe\MSSQL$CRM\sql\query. Two instances cannot use the same port, which is why named instances get a dynamic port by default, chosen when the service starts. In the registry, each instance has an identifier with a version number, for example MSSQL17.CRM in SQL Server 2025, and keeps its protocol configuration under it. SQL Server Express installs by default as the named instance SQLEXPRESS, so the same rules apply to it.

How SQL Browser works

At startup, the SQL Server Browser service (SQLBrowser) claims UDP port 1434, reads all instances on the server from the registry, and records their TCP ports and pipe names. A client given the address srv-sql-01\CRM first sends a short query with the instance name to UDP 1434. The format is described in the [MC-SQLR] specification (SQL Server Resolution Protocol, SSRP). Browser replies with text containing the port and the pipe, and only then does the driver open a TCP connection to the right port.

Query:       04 43 52 4D 00     byte 0x04 (CLNT_UCAST_INST), instance name, byte 0x00
Response:    05 <2-byte length> ServerName;SRV-SQL-01;InstanceName;CRM;IsClustered;No;Version;17.0.1000.7;tcp;14331;;

Three rules follow from this mechanism:

• Without a running Browser, a connection in the form server\INSTANCE without a port fails with error 26. Connections that specify the port keep working.
• The default instance does not need Browser as long as it listens on port 1433. When it uses another port, the client has to specify it, because it does not ask Browser when given the server name alone.
• According to the documentation, Setup configures Browser to start automatically when a named instance is installed, Express included, although depending on the installation options the service may be left stopped. Changing the startup type to manual or disabled produces exactly the symptom from the start of this post, but only at the next restart.

Browser accepts queries without authentication, so the firewall rule for UDP 1434 should allow only the application subnets, here 10.10.30.0/24. Browser does not advertise an instance with the HideInstance setting, and such an instance accepts connections only with an explicitly specified port.

Dynamic or static port

A dynamic port can change after an instance restart. For clients that ask Browser the change is invisible, but a firewall rule for a specific port, a manually registered SPN and connection strings with a port no longer match. What remains is a firewall rule for the sqlservr.exe file, which an upgrade to a new SQL Server version invalidates, because the path to that file changes.

In this series the instances have static ports: CRM 14331, ERP 14332, HR 14333, and each has its own A record, for example sql-crm.corp.example.com. Setting the port in the IPAll section, the DNS records and the firewall rules in a GPO are covered in the part about Kerberos. With a static port and a name,port address, applications do not need Browser. It remains a convenience for administrators who type srv-sql-01\CRM into SSMS.

Address formats in the connection string

The server address has the general form [protocol:]server[\instance][,port]. The port is separated with a comma, because the colon is reserved for the protocol prefix.

Server=srv-sql-01\CRM                              port from SQL Browser
Server=sql-crm.corp.example.com,14331              port specified directly
Server=tcp:sql-crm.corp.example.com,14331          TCP only, no attempts with other protocols
Server=np:\\srv-sql-01\pipe\MSSQL$CRM\sql\query    named pipe over SMB
Server=lpc:srv-sql-01\CRM                          shared memory, local only

Address	What the client does	Ports and firewall rules on the server
srv-sql-01	connects to the default instance	TCP 1433
srv-sql-01\CRM	asks SQL Browser for the port, then connects over TCP	UDP 1434 and the instance port; with a dynamic port, a rule for sqlservr.exe
sql-crm.corp.example.com,14331	connects straight to the specified port	TCP 14331
srv-sql-01\CRM,14331	the port takes precedence, Browser is not asked	TCP 14331
tcp:sql-crm.corp.example.com,14331	uses TCP only	TCP 14331
np:\\srv-sql-01\pipe\MSSQL$CRM\sql\query	opens a named pipe over SMB	TCP 445
lpc:srv-sql-01\CRM	uses shared memory	none, works only on the same computer

Application connection strings should hold the full DNS name rather than an IP address. For an IP address Windows does not use Kerberos, and the driver cannot match it to the name in the server certificate.

Protocols and SQL Server Express

SQL Server supports three protocols:

• TCP/IP is the main protocol for network connections.
• Named pipes run over SMB (TCP 445). After installation they are disabled for network connections in all editions.
• Shared memory is enabled after every installation and works only on the same computer.

Without a prefix, the driver tries the enabled protocols in turn. When TCP fails, the message may refer to the last attempt, for example Named Pipes Provider, error: 40, although the cause lies elsewhere. The tcp: prefix in the application connection string removes this ambiguity.

In a new installation of the Express and Developer editions, the TCP/IP protocol is disabled, unlike in the Enterprise and Standard editions. The instance runs, local connections succeed, and nobody can connect from another computer. TCP/IP is enabled in SQL Server Configuration Manager (for SQL Server 2025 the file SQLServerManager17.msc): SQL Server Network Configuration, Protocols for HR, TCP/IP, Enable. After that, the instance service and the SQL Browser service must be restarted. The same in PowerShell, for an example HR instance running the Express edition:

# Requires the SqlServer module from the PowerShell Gallery
Import-Module SqlServer
$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer 'srv-sql-01'
$tcp = $wmi.GetSmoObject("ManagedComputer[@Name='srv-sql-01']/ServerInstance[@Name='HR']/ServerProtocol[@Name='Tcp']")
$tcp.IsEnabled = $true
$tcp.Alter()

# The protocol works after an instance restart; Browser rereads the ports
Restart-Service -Name 'MSSQL$HR' -Force
Restart-Service -Name 'SQLBrowser'

The limits of the Express edition, and when it is enough, are covered in the part about SQL Server Express.

Encryption required by default

Current versions of Microsoft drivers and tools require connection encryption by default and validate the server certificate:

Driver or tool	From version	Default setting
Microsoft.Data.SqlClient	4.0	Encrypt=True, from 5.0 Mandatory
ODBC Driver for SQL Server	18	Encrypt=yes (Mandatory)
OLE DB Driver for SQL Server (MSOLEDBSQL19)	19	Encrypt=Mandatory
SQL Server Management Studio	20	Encryption: Mandatory

An instance without a configured certificate generates a self-signed certificate at startup and writes A self-generated certificate was successfully loaded for encryption. to the error log. No client trusts it, so after a driver upgrade an application that has worked for years reports this error:

A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

The quickest workaround is TrustServerCertificate=True in the connection string. The connection stays encrypted, but the client does not verify the server’s identity, so anyone who intercepts the traffic can substitute their own server. With Encrypt=Strict (TDS 8.0, from SQL Server 2022) this setting has no effect, because the certificate is always validated. Encrypt=False is a worse way out: when the server does not force encryption, only the login is encrypted and the data travels in plain text.

The proper solution is a certificate from an internal certification authority (AD CS). Domain computers trust the enterprise root certification authority without extra configuration, while computers outside the domain and containers have to be configured separately. The certificate must meet the SQL Server requirements:

• the Server Authentication enhanced key usage (1.3.6.1.5.5.7.3.1);
• all names the clients use in the SAN extension, because the driver compares the name from the connection string with the certificate: sql-crm, sql-erp and sql-hr in the corp.example.com domain plus srv-sql-01.corp.example.com, and short names only when clients use them;
• a key in a legacy CSP, for example Microsoft RSA SChannel Cryptographic Provider (KeySpec AT_KEYEXCHANGE), because SQL Server will not accept certificates with a KSP;
• the computer store (LocalMachine\My) and read access to the private key for the service account CORP\svc-sql$, granted in certlm.msc through All Tasks, Manage Private Keys.

The template is most conveniently prepared as a copy of the Web Server template, with the Enroll permission for the GG-SQL-Hosts group and the subject supplied in the request. A request sent from the server in an administrator session saves the certificate in the computer store:

# SQLServerTLS template: copy of Web Server, Enroll for GG-SQL-Hosts, Microsoft RSA SChannel provider
$names = 'srv-sql-01.corp.example.com', 'sql-crm.corp.example.com', 'sql-erp.corp.example.com', 'sql-hr.corp.example.com'
Get-Certificate -Template 'SQLServerTLS' -SubjectName 'CN=srv-sql-01.corp.example.com' -DnsName $names -CertStoreLocation 'Cert:\LocalMachine\My'

The certificate is selected for each instance separately in Configuration Manager (Protocols for CRM, Properties, the Certificate tab), and the new setting takes effect after a service restart. From SQL Server 2025, linked servers and a remote replication distributor use OLE DB Driver 19 with the same defaults, so a trusted certificate is also needed for traffic between instances. SQL Server checks certificate validity only at configuration time, while some clients reject an expired certificate on every connection, which is why the expiry date belongs in certificate monitoring.

Check your own environment

The instance and SQL Browser services on the server, and the process listening on UDP 1434. A good result is Running and Automatic for every MSSQL$… service and for SQLBrowser if clients use server\INSTANCE addresses, plus a row for port 1434 owned by the sqlbrowser process. Manual or Disabled on Browser is the outage from the start of this post, waiting for the next restart.

Get-Service -Name 'MSSQLSERVER', 'MSSQL$*', 'SQLBrowser' -ErrorAction SilentlyContinue |
    Select-Object Name, Status, StartType

# Process listening on UDP 1434
Get-NetUDPEndpoint -LocalPort 1434 -ErrorAction SilentlyContinue |
    Select-Object LocalAddress, LocalPort, @{ n = 'Process'; e = { (Get-Process -Id $_.OwningProcess).ProcessName } }

The TCP ports each running instance listens on. A good result is a port that matches the convention, for example 14331 for CRM, on the addresses 0.0.0.0 and ::. An extra port on 127.0.0.1 and ::1 is the local dedicated administrator connection (DAC). No rows for a running instance means TCP/IP is disabled, and a port outside the convention is a dynamic port.

Get-CimInstance -ClassName Win32_Service |
    Where-Object { ($_.Name -eq 'MSSQLSERVER' -or $_.Name -like 'MSSQL$*') -and $_.State -eq 'Running' } |
    ForEach-Object {
        $svc = $_.Name
        Get-NetTCPConnection -State Listen -OwningProcess $_.ProcessId -ErrorAction SilentlyContinue |
            Select-Object @{ n = 'Service'; e = { $svc } }, LocalAddress, LocalPort
    }

A test from a computer in the application subnet: reachability of the instance port over TCP and the SQL Browser response. TcpTestSucceeded : True means the path to the port is open. The script sends a single CLNT_UCAST_INST query and prints the response, where a good result contains tcp;14331. A timeout means Browser is not running, a firewall blocks UDP 1434, the instance is hidden, or the instance name is wrong, because Browser does not respond to unknown names. The script changes nothing.

Test-NetConnection -ComputerName sql-crm.corp.example.com -Port 14331 |
    Select-Object RemoteAddress, RemotePort, TcpTestSucceeded

$server = 'srv-sql-01.corp.example.com'
$instance = 'CRM'
$udp = New-Object System.Net.Sockets.UdpClient
$udp.Client.ReceiveTimeout = 3000
try {
    # CLNT_UCAST_INST: byte 0x04, instance name, byte 0x00
    $request = [byte[]](@(0x04) + [System.Text.Encoding]::ASCII.GetBytes($instance) + @(0x00))
    $udp.Connect($server, 1434)
    [void]$udp.Send($request, $request.Length)
    $from = New-Object System.Net.IPEndPoint ([System.Net.IPAddress]::Any, 0)
    $response = $udp.Receive([ref]$from)
    # SVR_RESP: byte 0x05, length (2 bytes), semicolon-separated text
    [System.Text.Encoding]::ASCII.GetString($response, 3, $response.Length - 3)
}
catch {
    "No response from UDP 1434: $($_.Exception.Message)"
}
finally {
    $udp.Close()
}

Encryption of current network connections and the certificate the instance loaded at startup. A good result is TRUE in encrypt_option for application connections and a log entry about a certificate with a thumbprint (Cert Hash). The entry A self-generated certificate was successfully loaded for encryption. means a self-signed certificate, and Named pipe in net_transport is a client that does not connect over TCP.

SELECT s.program_name, s.host_name, c.net_transport, c.encrypt_option, c.local_tcp_port
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
WHERE c.net_transport <> 'Shared memory'
ORDER BY c.encrypt_option, s.program_name;

EXEC xp_readerrorlog 0, 1, N'certificate';