How to enable remote connections to SQL Server

<img src="https://miro.medium.com/max/1400/1*18lrHvJ8YtADJDT7hxIThA.jpeg" class="ge n o gd ab" width="700" height="553"/>

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.

<img src="https://miro.medium.com/max/1400/1*ruDlsPJeSGTDk7pDBaM7Hw.jpeg" class="ge n o gd ab" width="700" height="394"/>

Connect to SQL Server instance from remote server.

<img src="https://miro.medium.com/max/892/1*0v10_MB-4tlE3sgO28RfCA.jpeg" class="ge n o gd ab" width="446" height="501"/>

Go to server properties.

Now right click on the server and go to Properties.

<img src="https://miro.medium.com/max/1378/1*yUx2BK492C1HnanRCyemPQ.jpeg" class="ge n o gd ab" width="689" height="625"/>

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

<img src="https://miro.medium.com/max/1270/1*TW0Of_vNs-tVyAZxQwhc1Q.jpeg" class="ge n o gd ab" width="635" height="635"/>

Now open SQL Server Configuration Manager.

<img src="https://miro.medium.com/max/1164/1*bZCvhiRUMqj99ZenptRITA.jpeg" class="ge n o gd ab" width="582" height="268"/>

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.

<img src="https://miro.medium.com/max/1238/1*9LrrYcU6ufwT1J5pgj-L-Q.jpeg" class="ge n o gd ab" width="619" height="353"/>

 

<img src="https://miro.medium.com/max/1174/1*e4MOVOGwNWMMp6I8dqAlCA.jpeg" class="ge n o gd ab" width="587" height="362"/>

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

<img src="https://miro.medium.com/max/824/1*JvIellVWjttA5hHa2V9uVw.jpeg" class="ge n o gd ab" width="412" height="473"/>

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.

<img src="https://miro.medium.com/max/964/1*fvu90GTkPhnAOxuVUiSbBg.jpeg" class="ge n o gd ab" width="482" height="164"/>

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

<img src="https://miro.medium.com/max/1400/1*RCkiN4lzvBTTiYixjDVF9A.jpeg" class="ge n o gd ab" width="700" height="315"/>

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

Now lets configure the firewall.

 

<img src="https://miro.medium.com/max/786/1*Eu26_9BcTkA851nvnT1ULw.jpeg" class="ge n o gd ab" width="393" height="640"/>

Open up Windows Firewall.

<img src="https://miro.medium.com/max/1400/1*OoGUQcvnAN6UNoM5QR9MqA.jpeg" class="ge n o gd ab" width="700" height="450"/>

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

<img src="https://miro.medium.com/max/1400/1*SC7gG6jqyQov8v-EBNQ_MA.jpeg" class="ge n o gd ab" width="700" height="570"/>

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

<img src="https://miro.medium.com/max/1400/1*wIqqXIQR3x-5M-qrWaNQVg.jpeg" class="ge n o gd ab" width="700" height="571"/>

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

<img src="https://miro.medium.com/max/978/1*EzYoTre_NC6_GgWgTtl4fg.jpeg" class="ge n o gd ab" width="489" height="246"/>

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

<img src="https://miro.medium.com/max/974/1*W8CGEp_f0wdMSQrBtTXI_A.jpeg" class="ge n o gd ab" width="487" height="230"/>

Now all four rules are setup.

<img src="https://miro.medium.com/max/1340/1*Nv3-cfcE7UE7q43dYI0-cA.jpeg" class="ge n o gd ab" width="670" height="208"/>

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.

<img src="https://miro.medium.com/max/1240/1*XWolZOGQEAyFiYHtTDRIEg.jpeg" class="ge n o gd ab" width="620" height="304"/>

Under Service tab, select Automatic for Start mode.

<img src="https://miro.medium.com/max/828/1*s037cncXvmNMpMvMzyojYQ.jpeg" class="ge n o gd ab" width="414" height="475"/>

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.

<img src="https://miro.medium.com/max/1400/1*bvILU6p0juJa7-ZcXng-6g.jpeg" class="ge n o gd ab" width="700" height="325"/>

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

<img src="https://miro.medium.com/max/810/1*xWzgRZcBiQ93qoXNqAc3ig.jpeg" class="ge n o gd ab" width="405" height="467"/>

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

…and Done!.

Try again to connect.

<img src="https://miro.medium.com/max/718/1*L2xBPov_1qKCQTm5OFFwKg.jpeg" class="ge n o gd ab" width="359" height="308"/>

Awesome! You did it. :)

转载于:https://www.cnblogs.com/Javi/p/11382424.html