SQL Server 2016 allow remote connection installed on windows server 2016

Scenario:

You have SQL Server 2016 installed on a Windows Server 2016 and you wish to allow remote connection to the server from your local area network computers.

The following configuration solution is only meant to demonstrate for a LAN scenario within the same network segment. If you need to connect from outside your LAN segment, you’ll have to jump through probably a few more firewall hoops that are in between your connecting computer and the SQL Server Instance.

 

Solution:

To allow SQL Server 2016 on your Windows Server 2016 to be connected remotely, you will have to do the followings.

1. Open the inbound firewall for the Windows Server 2016, to allow the SQL Server 2016 TCP port incoming traffic
2. Enable SQL Server TCP/IP traffic listening via the SQL Server Configuration Manager

Once you are done with the two things, test your connection, and it should work!

 

Followings are the instructions.

---

Open the inbound firewall for the Windows Server 2016, to allow the SQL Server 2016 TCP port incoming traffic

Launch Windows Firewall with Advanced Secuirty, and create a new inbound Port rule.

Set the TCP port to SQL Server default port of 1433.

Allow the connection.

Allow connection from the specific network location choices.

Give a name to your firewall rule.

You are finished with the server firewall configuration.

 

---

Enable TCP/IP traffic listen from the SQL Server Configuration Manager

Launch SQL Server Configuration Manager.

Enable TCP/IP.

For the TCP/IP Properties, Set IPAll to listen to TCP Port 1433 (the default port for SQL Server).

Restart the SQL Server Service to effect the changes.

Your SQL Server is now ready to accept remote connections.

 

---

Test your connection (SQL Management Studio Connecting Example).

Launch SQL Management Studio and key in your [target server]\instance,port. Then Connect…

In my case, it is TheServer\MSSQL2016,1433

That’s all to it! Enjoy your remote connection!!!