During new system installs I'll sometimes run into database client connection issues. Nine times out of ten the issue boils down to configuration at one of these points:

  • Firewall
  • Protocol Settings
  • SQL Browser Service

The following client error messages all have the same root cause - the firewall is not configured to allow connections to the server.

Connection attempt via SSMS:

ConnectionIssues

SQLCMD connection error message:

ConnectionIssues

ODBC connection as part of a Vcenter setup:

Connection3

Before troubleshooting any connections double check the spelling of the server name and then perform a ping. If ping fails it might indicate the machine is turned off, ping is disabled, or ICMP is blocked by the firewall. Work with the network admin to at least verify the machine is up. If ping works then run the following Powershell command:

get-service -ComputerName [ServerName] -name '*sql*'

The output verifies if SQL Server is running.
posh

Configuring the Firewall for Remote Connections

Open Windows Firewall with Advanced settings. Click Inbound Rules:
Connection4

Click Add New Rule, Make sure Program is selected. You can also open by Port if you wish.

Connection5

Browse to server's .exe. The default path for SQL 2016 is:

%ProgramFiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

Conection6

Ensure "Allow the connection" is enabled, select the appropriate profile i.e. domain, private or public and then name the rule. You will now be able to connect assuming firewall was the root issue.

Disabled or Misconfigured Protocols

SQL Server configuration manager provides an interface for managing services and protocols. Ensure the proper protocols are enabled and the correct port numbers are being used.

ConfigMan

SQL Browser Service
The SQL Browser Service allows named instances with dynamic ports to connect. If it's stopped clients won't connect. Start the browser service from configuration manager. It might be a good idea to change the start mode to Automatic.

configman4

Many things can cause connection problems. These are just the three I see time and time again.