How to enable remote connections to SQL Server

How to enable remote connections to SQL Server

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 postConnect to SQL Server instance from remote server.Image for postGo to server properties.

Now right click on the server and go to Properties.

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

Now open SQL Server Configuration Manager.

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 postImage for post

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

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

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

Image for post

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

Now lets configure the firewall.

Image for post

Open up Windows Firewall.

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 postImage for postImage for postImage for postImage 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

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

Rule Type: Program

Program: C:Program FilesMicrosoft SQL Server<Server version>.<Instance name>MSSQLBinnsqlservr.exe

e.g. C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBinn

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLSERVER

Image for postImage for postImage for postImage for post

One more rule for the SQL Server Browser.

Rule Type: Program

Program: C:Program Files (x86)Microsoft SQL Server90Sharedsqlbrowser.exe

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLBROWSER

Image for postImage for post

Now all four rules are setup.

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

Under Service tab, select Automatic for Start mode.

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

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

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 postImage for post

Awesome! You did it. 🙂

Image for post

No Responses

Write a response