Troubleshoot remote access to SQL Server -

Troubleshoot remote access to SQL Server

Last updated on: 2021-07-09

Authored by: Rackspace Support

When you can’t connect to a Microsoft® SQL server® instance remotely through ODBC, Visual Studio®, or a SQL Server Management Studio® connection, the Windows® firewall is usually blocking access. Use the resolutions in this article to troubleshoot the issue.

Verify port traffic

Verify the Windows firewall allows traffic over port 1433:

Open PowerShell® on your remote workstation.

Run the following command:

  test-netconnection <public IP address> -port 1433

Open TCP ports

To troubleshoot this issue, open TCP port 1433 for the service itself. If you need to use the SQL Browser service, also open port 1434. This resolution applies to SQL Server® versions 2005, 2008, 2008 R2, and 2012.

Open the Run window by pressing the keys Windows + R, type cliconfig, and make sure to enable the TCP/IP protocol if it isn’t enabled.
Verify that the SQL Server service is running.
- SQL Server 2005, 2008, and 2008 R2:
  
  Select Start > Administrative Tools > Services and verify that the SQL Server (MSSQLSERVER) service is running.
- SQL Server 2012, 2014, and 2016:
  
  Use the Windows key or hover the mouse pointer over the lower-left corner of the desktop, select Administrative Tools > Services and verify that the SQL Server (MSSQLSERVER) service is running.
Ensure that you are using the correct credentials to authenticate. The default SQL Server administrator account is named sa. If you built the server from a server image with SQL Server pre-installed, the password is in a text file on the root of the C partition.
From a command prompt, run netstat -an.
In the output, verify whether the server is listening for SQL Server traffic on ports 1433 and optionally 1434. If not, proceed with the following steps to use the SQL Server Configuration Manager to change the ports.
Open the SQL Server Configuration Manager as follows:
- SQL Server 2005, 2008, and 2008 R2:
  
  Go to Start > All Programs > Microsoft SQL Server 2005 (or 2008 or 2008 R2) > Configuration Tools > SQL Server Configuration Manager.
- SQL Server 2012, 2014, and 2016:
  
  Use the Windows key or hover the mouse pointer over the lower-left corner of the desktop and select All Programs > Microsoft SQL Server 2012 (or 2014 or 2016) > Configuration Tools > SQL Server Configuration Manager.
In the navigation pane, expand SQL Server Network Configuration and select the protocols for your SQL Server instance.
In the right pane, right-click TCP/IP and select Properties.
Select the IP Addresses tab.
Ensure that all TCP port mentioned on all interfaces is 1433.
Click OK.
Restart the SQL Server service.

Specify the SQL Server instance name when using named instances

Named instances let you host SQL Server versions or service types. If you are using named instances when installing SQL Server, you must specify the name of the SQL Server instance when connecting rather than using the server name or IP address. Append a backslash and the SQL Server instance name to theserver name or IP address. For example, 12.34.56.78\sqlInstanceName or sqlServerName\sqlInstanceName.

Cloud Servers