How to enable remote connections to SQL Server

Image for post
Image for post

Problem in hand

So you have a VM or a remote server, that you have installed SQL Server, but you also want to connect to SQL Server using SQL Server Management Studio on your local system, but gives you this error.

This is because you haven’t configured it to allows inbound connections from firewall or from SQL Server itself, could be both.

Prerequisites

  1. Remote server/VM must have SQL Server Management Studio and SQL Server Configuration Manager Installed.
  2. Remote SQL Server must have a user that have read and write privileges (Here I’m using sa user)

All set, let’s start

Connect to the server/VM using Remote Desktop Connection and open up SQL Server Management Stdio on remote server, then connect to the SQL Server instance.

Image for post
Image for post
Connect to SQL Server instance from remote server.
Go to server properties.

Now right click on the server and go to Properties.

Image for post
Image for post

On the Connections page under Remote server connections, make sure that the Allow remote connections to this server is checked.

Image for post
Image for post

Now open SQL Server Configuration Manager.

Image for post
Image for post

Visit Protocols for <instance name>, in my case Protocols for MSSQLSERVER under SQL Server Network Configuration node, go to TCP/IP and make sure the ‘Status’ is set to Enabled. If not, right click and select Enable.

Image for post
Image for post
Image for post
Image for post

After that, again right click and select Properties of TCP/IP protocol.

Image for post
Image for post

Goto IP Addresses tab and go down until you see IPALL section. Make sure the TCP Port is set to 1433. If not set it to 1433.

Image for post
Image for post

This warning will appear, so we need to restart the server. we can do it by using SQL Server Configuration manager.

Image for post
Image for post

Locate SQL Server under SQL Server Services, right click and select Restart.

Now lets configure the firewall.

Image for post
Image for post

Open up Windows Firewall.

Image for post
Image for post

Go to Inbound Rules and select New Rule.

Add the following rule. Follow images if unclear.

Rule Type: Port

Protocol and Ports: TCP, 1433

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLTCP1433

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Click finish. We need to add another rule for UDP connections as well.

Rule Type: Port

Protocol and Ports: UDP, 1434

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLUDP1434

Image for post
Image for post

Let’ make SQL Server and SQL Server Browser accessible through firewall.

Rule Type: Program

Program: C:\Program Files\Microsoft SQL Server\<Server version>.<Instance name>\MSSQL\Binn\sqlservr.exe

e.g. C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLSERVER

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

One more rule for the SQL Server Browser.

Rule Type: Program

Program: C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLBROWSER

Image for post
Image for post
Image for post
Image for post

Now all four rules are setup.

Image for post
Image for post

Make sure the SQL Server Browser and SQL Server is set to start automatically. Go back to SQL Server Configuration Manager and right click on SQL Server Browser under SQL Server Services, click Properties.

Image for post
Image for post

Under Service tab, select Automatic for Start mode.

Image for post
Image for post

Click Apply. Now let’s make sure that we keep the service failures to minimum.

  1. Click the Windows key + R to open the Run window.
  2. Type services.msc in the Open: box.
  3. Click OK.
Image for post
Image for post

Find the SQL Server service, right click and select Properties.

Image for post
Image for post

On the Recovery tab select Restart the Service for First failure, Second failure, Subsequent failures.

and Done!.

Try again to connect.

Image for post
Image for post
Image for post
Image for post

Awesome! You did it. :)

Image for post
Image for post

Written by

Systems Design • Social Innovation • Cloud • ML

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store