Skip to content

Latest commit

 

History

History
218 lines (140 loc) · 12.2 KB

sql-server-linux-docker-container-security.md

File metadata and controls

218 lines (140 loc) · 12.2 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom monikerRange
Secure SQL Server Linux Containers
Understand the different ways to secure SQL Server Linux containers and how you can run containers as different non-root users on the host.
amitkh-msft
amitkh
vanto, randolphwest
01/21/2025
sql
linux
how-to
engagement-fy23
linux-related-content
>=sql-server-linux-2017 || >=sql-server-2017

Secure SQL Server Linux containers

[!INCLUDE SQL Server - Linux]

[!INCLUDE sssql17-md] containers start up as the root user by default, which can cause some security concerns. This article talks about security options that you have when running [!INCLUDE ssnoversion-md] Linux containers, and how to build a [!INCLUDE ssnoversion-md] container as a non-root user.

The examples in this article assume that you're using Docker, but you can apply the same principles to other container orchestration tools including Kubernetes.

Build and run non-root SQL Server 2017 containers

Follow these steps to build a [!INCLUDE sssql17-md] container that starts up as the mssql (non-root) user.

Note

Containers for [!INCLUDE sssql19-md] and later versions automatically start up as non-root, while [!INCLUDE sssql17-md] containers start as root by default. For more information on running SQL Server containers as non-root, see Secure SQL Server Linux containers.

  1. Download the sample Dockerfile for non-root SQL Server containers and save it as dockerfile.

  2. Run the following command in the context of the dockerfile directory to build the non-root [!INCLUDE ssnoversion-md] container:

    cd <path to dockerfile>
docker build -t 2017-latest-non-root .

  3. Start the container.

    [!IMPORTANT]
    The SA_PASSWORD environment variable is deprecated. Use MSSQL_SA_PASSWORD instead.

    docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" --cap-add SYS_PTRACE --name sql1 -p 1433:1433 -d 2017-latest-non-root

    [!NOTE]
    The --cap-add SYS_PTRACE flag is required for non-root [!INCLUDE ssnoversion-md] containers to generate dumps for troubleshooting purposes.

  4. Check that the container is running as non-root user:

    docker exec -it sql1 bash

    Run whoami, which returns the user running within the container.

    whoami

Run container as a different non-root user on the host

To run the [!INCLUDE ssnoversion-md] container as a different non-root user, add the -u flag to the docker run command. The non-root container has the restriction that it must run as part of the root group unless a volume is mounted to /var/opt/mssql that the non-root user can access. The root group doesn't grant any extra root permissions to the non-root user.

Run as a user with a UID 4000

You can start [!INCLUDE ssnoversion-md] with a custom UID. For example, the following command starts [!INCLUDE ssnoversion-md] with UID 4000:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" --cap-add SYS_PTRACE -u 4000:0 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Warning

Make sure that the [!INCLUDE ssnoversion-md] container has a named user such as mssql or root, otherwise sqlcmd will not be able to run within the container. You can check if the [!INCLUDE ssnoversion-md] container is running as a named user by running whoami within the container.

Run the non-root container as the root user

You can run the non-root container as the root user if necessary, which also grants all file permissions automatically to the container, because it has higher privilege.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" -u 0:0 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Run as a user on your host machine

You can start [!INCLUDE ssnoversion-md] with an existing user on the host machine with the following command:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" --cap-add SYS_PTRACE -u $(id -u myusername):0 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Run as a different user and group

You can start [!INCLUDE ssnoversion-md] with a custom user and group. In this example, the mounted volume has permissions configured for the user or group on the host machine.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" --cap-add SYS_PTRACE -u $(id -u myusername):$(id -g myusername) -v /path/to/mssql:/var/opt/mssql -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Configure persistent storage permissions for non-root containers

To allow the non-root user to access database files that are on mounted volumes, make sure that the user or group you run the container under, can read from and write to the persistent file storage.

You can get the current ownership of the database files with this command.

ls -ll <database file dir>

Run one of the following commands if [!INCLUDE ssnoversion-md] doesn't have access to persisted database files.

Grant the root group read/write access to the database files

Grant the root group permissions to the following directories so that the non-root [!INCLUDE ssnoversion-md] container has access to database files.

chgrp -R 0 <database file dir>
chmod -R g=u <database file dir>

Set the non-root user as the owner of the files

This can be the default non-root user, or any other non-root user you'd like to specify. In this example, we set UID 10001 as the non-root user.

chown -R 10001:0 <database file dir>

Encrypt connections to SQL Server Linux containers

Important

When configuring Active Directory authentication or encryption options such as Transparent Data Encryption (TDE) and SSL for [!INCLUDE ssnoversion-md] on Linux or containers, there are several files, such as the keytab, certificates, and machine key, that are created by default under the folder /var/opt/mssql/secrets, and access to which is restricted by default to mssql and root users. When configuring persistent storage for [!INCLUDE ssnoversion-md] containers, use the same access strategy, ensuring that the path on the host or shared volume that is mapped to the /var/opt/mssql/secrets folder inside the container is protected and accessible only to the mssql and root users on the host as well. If the access to this path/folder is compromised, a malicious user can gain access to these critical files, compromising the encryption hierarchy and/or Active Directory configurations.

To encrypt connections to [!INCLUDE ssnoversion-md] Linux containers, you need a certificate with the following requirements.

Following is an example of how the connection can be encrypted to [!INCLUDE ssnoversion-md] Linux containers. Here we use a self-signed certificate, which shouldn't be used for production scenarios. For such environments, you should use CA certificates instead.

  1. Create a self-signed certificate, which is suited for test and non-production environments only.

    openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=sql1.contoso.com' -keyout /container/sql1/mssql.key -out /container/sql1/mssql.pem -days 365

    In the previous code sample, sql1 is the hostname of the SQL container, so when connecting to this container the name used in the connection string is going to be sql1.contoso.com,port. You must also ensure that the folder path /container/sql1/ already exists before running the above command.

  2. Ensure you set the right permissions on the mssql.key and mssql.pem files, so you avoid errors when you mount the files to [!INCLUDE ssnoversion-md] container:

    chmod 440 /container/sql1/mssql.pem
chmod 440 /container/sql1/mssql.key

  3. Now create a mssql.conf file with the below content to enable the Server Initiated encryption. For Client initiated encryption, change the last line to forceencryption = 0.

    [network]
tlscert = /etc/ssl/certs/mssql.pem
tlskey = /etc/ssl/private/mssql.key
tlsprotocols = 1.2
forceencryption = 1

    [!NOTE]
    For some Linux distributions the path for storing the certificate and key could also be : /etc/pki/tls/certs/ and /etc/pki/tls/private/ respectively. Verify the path before updating the mssql.conf for [!INCLUDE ssnoversion-md] containers. The location you set in the mssql.conf will be the location where [!INCLUDE ssnoversion-md] in the container is going to search for the certificate and its key. In this case, that location is /etc/ssl/certs/ and /etc/ssl/private/.

    The mssql.conf file is also created under the same folder location /container/sql1/. After running the above steps, you should have three files: mssql.conf, mssql.key, and mssql.pem in the sql1 folder.

  4. Deploy the [!INCLUDE ssnoversion-md] container with the following command (replace <password> with a valid password):

    docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" -p 5434:1433 --name sql1 -h sql1 -v /container/sql1/mssql.conf:/var/opt/mssql/mssql.conf -v   /container/sql1/mssql.pem:/etc/ssl/certs/mssql.pem -v /container/sql1/mssql.key:/etc/ssl/private/mssql.key -d mcr.microsoft.com/mssql/server:2019-latest

    In the previous command, we have mounted the mssql.conf, mssql.pem, and mssql.key files to the container and mapped the 1433 ([!INCLUDE ssnoversion-md] default port) port in the container to port 5434 on the host.

    [!NOTE]
    If you use RHEL 8 and later versions, you can also use podman run command instead of docker run.

Follow the "Register the certificate on your client machine" and "Example connection strings" sections documented in Client Initiated Encryption to start encrypting connections to [!INCLUDE ssnoversion-md] on Linux containers.

Related content

::: moniker range="=sql-server-linux-2017 || =sql-server-2017"

::: moniker-end

::: moniker range="=sql-server-linux-ver15 || =sql-server-ver15"

::: moniker-end

::: moniker range=">= sql-server-linux-ver16 || >= sql-server-ver16"

::: moniker-end