Connecting Remotely to SQL Server on Linux

If you followed our previous post you should now have a working SQL Server instance running on Linux. The instance at this point accepts local connections. This means that if you try to connect from your Windows SSMS you most likely won't be able to, however, that mainly depends on whether you are running a firewall or not.

SQL Server listens for connection requests on TCP port 1433 (by default) and the Dedicated Administrator Connection does it on TCP port 1434. Let's check if SQL Server is actually listening on those ports. Use the command netstat as follows:

As you can see, SQL Server is using those ports(highlighted above) and is actively listening for connections.

To enable these connections remotely you will need to open the Linux firewall to accept traffic on those ports. This action will depend on the type of firewall you are running on Linux. If you are not running any firewall, then you don't need to do anything since all traffic will be allowed by default. There are two widely used firewalls that run on Ubuntu. One is Uncomplicated Firewall (UFW) and the other is firewalld which is a separate package that requires installation. These two firewall packages run on different distros.

Let's find out if any of them are running:

As shown in the example above, your instance shows no firewall running. Therefore, you should be able to remotely connect to SQL Server from SSMS without any issues.

Now let's make a simple scenario where a firewall is enabled and remote connections are not possible. First, enable UFW and then try to connect. You should be seeing something as shown below:

Next, we will open the ports so connections can be made:

Note that the command opened the ports for both IPv4 and IPv6 because it detected that both protocols were enabled. If you try to connect now, you will be successful.

Please remember that you will need to enable DAC before you can use it. All you need to do is to run a simple configuration command:

When making a connection using DAC, SSMS will give you an error because the program opens multiple connections, and DAC will only allow one. So if you see the following just click OK and continue working normally.

You are now able to work and manage SQL Server on Linux from SSMS in your Windows machine or any other client that can connect remotely such as Azure Data Studio.

Stay tuned for our next installment in our ongoing series of SQL Server on Linux. If you want further insight from our friendly team, you can always contact us. We are here to help!