The Key to Monitoring SQL Server on Linux Containers With SQL Sentry

Did you know you can monitor SQL Server on Linux containers with SQL Sentry? You can! You need to know a few additional things upfront to be successful when monitoring SQL Server running in a container. We will cover a few concepts to start; then, I will walk through how to add SQL Server on Linux containers as a SQL Sentry monitored target.

Setting the hostname

When you run a container using Docker, or any container runtime, the container is assigned a unique id (generated using a SHA256 hash based on multiple facets of the container configuration). If you do not define the hostname during the run command, Docker uses the id that was generated as the hostname. We can see this by running the SQL Server image and comparing the id to the hostname in the database.

Here is the server hostname (1e5b0d494f32), which matches the id created from our run command above.

The mismatch between hostname and server name will not work with SQL Sentry. Most programming languages default to require that when TrustServerCertificate is set to false and Encrypt is true, the server name in the connection string must match the hostname in SQL Server. Given that the Docker container is exposed on localhost over the port passed in and the container hostname is the container id, these will definitely not match. This means that clients such as SQL Sentry will fail to connect, which is actually good because of the security implications. Tools such as sqlcmd, Azure Data Studio, DataGrip, and SSMS use different default settings that allow this behavior, which explains why we can connect using those tools. If you change the tool settings to those mentioned above, then they would also fail to connect.

So, I'm sure you are asking, "How do you solve this issue?" The first step is to ensure that when you run the SQL Server container, you set the hostname to a unique name using the command below.

Now that the hostname is configured, let's verify that it is working using Azure Data Studio.

We can see on the Home view that the Computer Name has been updated to sqlserver. Now it’s time for the tricky part of this setup—mapping the hostname that you used to the server that is running your container.

Mapping the hostname

This blog post assumes it is all on the localhost; however, this process can be applied to most configurations. We will take the most effortless approach and edit the hosts file on the server running our client. Open the hosts file located at “C:\Windows\System32\drivers\etc\hosts” as an administrator and add the following on line 24.

In that file, 127.0.0.1 localhost can be replaced with the server's name or IP running your containers. When we connect, we can leverage that hostname, which will resolve to the correct server.

Open the SQL Sentry client and add a target. Enter the hostname, port, and switch to SQL authentication. Enter the sa account’s password set during the run command in the Credentials section. After a few seconds, you should see it connecting to a SQL Server on Linux target, and the target then shows up in the client.

The following screenshot shows the Add Target dialog box.

Here is what it looks like after the target has been added and detects that it is on Linux:

And finally, here it is in the SQL Sentry client:

Monitoring SQL Server on Linux Containers

I hope this blog post helps you get started by answering the question as to how to set up SQL Server on Linux containers as monitoring targets in SQL Sentry. Note that I purposely keep this all on localhost, as it's more straightforward to understand the concepts behind what is happening. Now that you are armed with this information, you can decide if you need to take it to your networking team (if you have one) to create a more robust solution.