Secure SQL Server Linux containers
Applies to: SQL Server - Linux
SQL Server 2017 (14.x) containers start up as the root user by default. This can cause some security concerns. This article talks about security options that you have when running SQL Server Linux containers, and how to build a SQL Server 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 the steps below to build a SQL Server 2017 (14.x) container that starts up as the
mssql (non-root) user.
SQL Server 2019 (15.x) and later version containers automatically start up as non-root, so the following steps only apply to SQL Server 2017 (14.x) containers, which start as root by default.
Download the sample Dockerfile for non-root SQL Server containers and save it as
Run the following command in the context of the dockerfile directory to build the non-root SQL Server container:
cd <path to dockerfile> docker build -t 2017-latest-non-root .
Start the container.
SA_PASSWORDenvironment variable is deprecated. Please use
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=MyStrongPassword@" --cap-add SYS_PTRACE --name sql1 -p 1433:1433 -d 2017-latest-non-root
--cap-add SYS_PTRACEflag is required for non-root SQL Server containers to generate dumps for troubleshooting purposes.
Check that the container is running as non-root user:
docker exec -it sql1 bash
whoami, which will return the user running within the container.
Run container as a different non-root user on the host
To run the SQL Server 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 SQL Server with a custom UID. For example, the command below starts SQL Server with UID 4000:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=MyStrongPassword" --cap-add SYS_PTRACE -u 4000:0 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
Ensure that the SQL Server container has a named user such as
root, otherwise sqlcmd will not be able to run within the container. You can check if the SQL Server 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. This would also grant all file permissions automatically to the container because it has higher privilege.
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=MyStrongPassword" -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 SQL Server with an existing user on the host machine with the following command:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=MyStrongPassword" --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 SQL Server 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=MyStrongPassword" --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, ensure 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 SQL Server 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 SQL Server 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
When configuring Active Directory authentication or encryption options such as Transparent Data Encryption (TDE) and SSL for SQL Server 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
root users. When configuring persistent storage for SQL Server containers, please 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
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 SQL Server Linux containers, you'll need a certificate with the following requirements.
Below is an example of how the connection can be encrypted to SQL Server 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.
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,
sql1is 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.
Ensure you set the right permissions on the
mssql.pemfiles, so you avoid errors when you mount the files to SQL Server container:
chmod 440 /container/sql1/mssql.pem chmod 440 /container/sql1/mssql.key
Now create a
mssql.conffile 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
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. Please verify the path before updating the
mssql.conffor SQL Server containers. The location you set in the
mssql.confwill be the location where SQL Server in the container is going to search for the certificate and its key. In this case, that location is
mssql.conffile is also created under the same folder location
/container/sql1/. After running the above steps, you should have three files:
Deploy the SQL container with the command shown below:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssw0rd" -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 command above, we have mounted the
mssql.keyfiles to the container and mapped the 1433 (SQL Server default port) port in the container to port 5434 on the host.
If you are using RHEL 8 and above, you can also use
podman runcommand instead of
Follow the "Register the certificate on your client machine" and "Example connection strings" sections documented in Client Initiated Encryption to start encrypting connections to SQL Server on Linux containers.
- Get started with SQL Server 2017 (14.x) container images on Docker by going through the quickstart
- Get started with SQL Server 2019 (15.x) container images on Docker by going through the quickstart
- Get started with SQL Server 2022 (16.x) container images on Docker by going through the quickstart