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:
SQLCMD connection error message:
ODBC connection as part of a Vcenter setup:
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.
Configuring the Firewall for Remote Connections
Open Windows Firewall with Advanced settings. Click Inbound Rules:
Click Add New Rule, Make sure Program is selected. You can also open by Port if you wish.
Browse to server's .exe. The default path for SQL 2016 is:
%ProgramFiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
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.
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.
Many things can cause connection problems. These are just the three I see time and time again.
Comments