After installing Microsoft SQL Server on a Windows server, it does not allow remote connections to the database by default. Therefore, it is necessary to enable the TCP/IP protocol on the server's interface IP address. After enabling it, assign a port for the process (it will be set to dynamic ports by default), and it will also be necessary to configure the firewalls to not filter the connections.
Warning
Allowing public connections to SQL Server can pose a serious security problem. Therefore, we recommend using a VPN connection (for example, How to set up OpenVPN Server with pfSense). This way, it will be possible to activate the TCP/IP port on the private network for the connection, and you will have the additional encryption of the established tunnel. An alternative for configuring it on the public interface is to filter it in the Firewall using a specific rule for the specific IP addresses that need access to SQL Server.
Procedure
First, you need to access SQL Server Configuration Manager under the section SQL Server Network Configuration > Protocols for [SQL Version]. You will see the list of protocols, right-click on TCP/IP, and then click Enable to activate it.
Then, repeat the same action but click on Properties, a new window will open, and in its IP Addresses tab, activate it by selecting Yes on the desired interface. Additionally, remove the value from TCP Dynamic Ports and enter the desired port in TCP Port (the default port for Microsoft SQL Server is TCP 1433). Here is an example configuration for the public network interface:
It is also possible to modify it globally for all interfaces in the IPAll section of the same window by setting the desired port number in TCP Port:
Once these actions are completed, it will be necessary to restart the service to apply the changes, and you can do this from the SQL Server Configuration Manager itself. In the SQL Server Services section, right-click on the respective service, and you will have the Restart option in the dropdown menu to restart the service as shown in the following screenshot:
Verifications
To check the operation of the protocol and verify the status of the port, you can execute the following command:
netstat -onaf | findstr 1433
As shown in the screenshot below, the port should appear in the LISTENING state, and in the case of connections, they should also appear:
Then, check from the end-user's computer if it has access to the port. If not, recheck the Windows Firewall rules and the Clouding Firewall panel (How to create a firewall rule). In particular, for the Windows firewall, you can quickly allow access by running the following command via CMD:
netsh advfirewall firewall add rule name="SQL Server Port" dir=in action=allow protocol=TCP localport=1433
We hope this article has been helpful to you. If you have any questions about this or any other server-related issues on Clouding, feel free to contact soporte@clouding.io. We are here to help you!