MS SQL Server on Linux <=> Management Studio (SSMS) on Windows (via ssh)

If you use MS SQL Server for your database needs, probably you also use SQL Server Management Studio (SSMS) for several day to day database management needs. SSMS is a common tool for managing MS SQL Server whether you need to do DDL, DML or DCL related tasks. While all these things can be done using plain T-SQL and SQLCMD utility, SSMS does things beautifully without us worrying about the SQL syntax and falling into the complexities of queries.

MS SQL Server’s support for Linux was very much awaited among its users. Most of the .Net Framework developers generally uses MS SQL Server for database but the windows-only support not only limited the usage but also increases of hosting cost as compared to linux based db servers.

If you are still unaware, Microsoft SQL Server now runs on Linux!! Follow this link for download & install instructions for SQL Server on Linux.

Installing MS SQL Server on linux is an easy bit, the (not so) hard part comes now. How to connect Management Studio (SSMS) that is running on the development computer (probably Windows 7,8 or 10) to the SQL Server that is running on the linux server (Ubuntu, Redhat, Docker etc)? Well, that’s what this post is for 🙂

Use SQL Server Management Studio on Windows to manage SQL Server on Linux

There are 2 ways to connect to Linux SQL Server from SSMS:

1. Allow Remote Connections and configure linux firewall to keep the SQL Server port wide open. (INSECURE & NOT RECOMMENDED)
2. SSH Tunneling (RECOMMENDED & SECURE).

Lets discuss them one by one:

1. Open Linux Firewall port to connect SQL Server on Linux from Management Studio.

Well, I personally feel this method is very insecure and would recommend you to abstain yourself from using it. So we’ll not discuss this method here, but if you still insist, you can read this is excellent post and then read this doc from Microsoft official website.

2. Use SSH Tunneling to connect SQL Server on Linux from Management Studio (SSMS).

Wait. SSL Tunneling? What the heck is that??

If you’ve never heard of SSH tunneling before, i guess you probably have had spend your life on windows-only development. Welcome to the world of Linux dude 🙂 Ever seen Alice in Wonderland? That’s what Linux is.

Curious about SSH Tunneling? Read this blog post. You’ll be amazed.

Even if you don’t know (or don’t want to know) about SSH Tunneling, just follow my instructions below to securely connect to SQL Server on Linux from the Management Studio on your Windows machine.

Prerequisites:

1. Linux Server with MS SQL Server installed.
2. SSH access to the Linux Server.
   • If you own a vps server, you should already have SSH credentials or SSH keys.
   • If you are using someone else’s server, contact your host to get SSH login for your account.
3. An SSH Client like PuTTY or Bitvise. I personally recommend Bitvise.
4. SQL Server Management Studio (SSMS) v17 or higher.

Let’s start putting things together:

MS SQL Server on Linux:

I am using Bitvise SSH Client to connect to Linux Server. See this screencast:

On SSH terminal, run this command to check if SQL Server is installed on Linux:

systemctl status mssql-server --no-pager

The output should be something like this:

If SQL Server is not installed on your server, you can follow this link for detailed steps for installing the same.

Optionally, you can also install SQL Server command-line tools if you would like to use sqlcmd utility to manage MS SQL Server through terminal.

Setup SSH Tunneling:

Through SSH tunneling, SQL Server Management Studio (SSMS) will be able to connect to Linux’s MS SQL Server as if it is locally installed. The SSH tunnel will act as proxy that forwards all packets from SSMS & SQL Server and vice versa. That is why, there is no need to open port on firewall of Linux server. Hence, its more secure.

Steps to setup SSH Tunneling between Windows client & Linux server using Bitvise:

1. Start Bitvise. Go to C2S tab, then click Add.

2. Enter these details on the newly added row:

• Listen Interface: 127.0.0.1
• List. Port: 2143
• Destination Host: localhost
• Dest. Port: 1433
• Comment: ms sql server on linux

3. After setting up above configuration, hit Apply.

4. If you are already logged into SSH, you should see something like this in the logs:

If you are not already logged into SSH, click “Log in” and Bitvise will automatically start the tunnel for you and show it in logs as shown in above screenshot.

Connect SSMS to Linux’s MS SQL Server

After setting up the tunnel, we are pretty much ready to connect our Management Studio(on windows) to SQL Server (on linux).

The SSH tunnel is acting as a tunnel between server’s 1433 port and the client’s 2143 port. So, all packets to port 2143 will be forwarded to server’s 1433 port.

In short, Linux’s 1433 port is now virtually available locally at 2143 port. That’s the magic of SSH tunneling!

From Management Studio’s point of view, it will be talking to local 2143 port only. Since, the SSH tunnel will forward all packets to server’s 1433 port, the Management Studio will think its directly talking to the SQL Server.

Now we are ready to fire up Management Studio, just use these connection details:

Server name: 127.0.0.1,2143
Authentication: SQL Server Authentication
Login: sa
Password: <sa password you've set during installation>

Hit Connect. What do you see? Something like in this video:

If everything works, you can fire up the query window to run this query to see the server version:

select @@version

The output should be something like this:

As you can see, we are connected to Ubuntu’s Sql Server. Now you can do pretty much anything on the SQL server using the Management Studio.

That is all.

amitm

Amit Mittal is a seasoned tech entrepreneur and software developer specializing in SaaS, web hosting, and cloud solutions. As the founder of Bitss Techniques, he brings over 15 years of experience in delivering robust digital solutions, including CMS development, custom plugins, and enterprise-grade services for the education and e-commerce sectors.