{"id":12835,"date":"2019-09-29T16:54:15","date_gmt":"2019-09-29T14:54:15","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/using-non-root-sql-server-containers-on-docker-and-k8s\/"},"modified":"2023-07-13T15:46:47","modified_gmt":"2023-07-13T13:46:47","slug":"using-non-root-sql-server-containers-on-docker-and-k8s","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/using-non-root-sql-server-containers-on-docker-and-k8s\/","title":{"rendered":"Using non-root SQL Server containers on Docker and K8s"},"content":{"rendered":"

This is something that I waited for a while, in fact since SQL Server 2017 \u2026 and the <\/span><\/span>news<\/span><\/a> came out on Wednesday 09th<\/sup> September 2019. Running Non-Root SQL Server Containers is now possible either on the next version of SQL Server (2019) and it has been backported on SQL Server 2017 as well. Non-root SQL Server containers will likely be part of hidden gem of SQL Server new features, but this is definitely a good news for me because it will facilitate the transition of SQL Server containers on production from a security standpoint. <\/span><\/span><\/span><\/p>\n

\"\"<\/p>\n

At this stage, no need to precise why it is not a best practice to run SQL Server containers or more generally speaking applications with root privileges within a container. For further information, I invite you to take a look at the different threats implied by a such configuration with your google-fu.\u00a0<\/span><\/p>\n

Let\u2019s start with docker environments. First, <\/b>Microsoft provides a <\/span><\/span>Docker file<\/span><\/a> to build an image either for SQL Server 2017 and SQL Server 2019. We may notice the Docker file is already based on a SQL Server docker image and performs some extra configuration for non-root privilege capabilities. I put here the interesting part:<\/span><\/b><\/span><\/span><\/p>\n

# Exmple of creating a SQL Server 2019 container image that will run as a user 'mssql' instead of root\n# This is example is based on the official image from Microsoft and effectively changes the user that SQL Server runs as\n# and allows for dumps to generate as a non-root user\n\n\nFROM mcr.microsoft.com\/mssql\/server:2019-latest\n\n# Create non-root user and update permissions\n#\nRUN useradd -M -s \/bin\/bash -u 10001 -g 0 mssql\nRUN mkdir -p -m 770 \/var\/opt\/mssql && chgrp -R 0 \/var\/opt\/mssql\n\n# Grant sql the permissions to connect to ports <1024 as a non-root user\n#\nRUN setcap 'cap_net_bind_service+ep' \/opt\/mssql\/bin\/sqlservr\n\n# Allow dumps from the non-root process\n# \nRUN setcap 'cap_sys_ptrace+ep' \/opt\/mssql\/bin\/paldumper\nRUN setcap 'cap_sys_ptrace+ep' \/usr\/bin\/gdb\n\n# Add an ldconfig file because setcap causes the os to remove LD_LIBRARY_PATH\n# and other env variables that control dynamic linking\n#\nRUN mkdir -p \/etc\/ld.so.conf.d && touch \/etc\/ld.so.conf.d\/mssql.conf\nRUN echo -e \"# mssql libs\\n\/opt\/mssql\/lib\" >> \/etc\/ld.so.conf.d\/mssql.conf\nRUN ldconfig\n\nUSER mssql\nCMD [\"\/opt\/mssql\/bin\/sqlservr\"]<\/pre>\n
Note the different sections where the mssql user is created and is used when running the image. So, the new image specification implies running the sqlservr process using this mssql user as shown below:<\/span><\/span><\/p>\n
$ docker exec -ti sql19 top<\/pre>\n
\"\"<\/p>\n
The user process is well identified by its name because it is already defined in the \/etc\/password file within the container namespace:<\/span><\/span><\/p>\n
$ docker exec -ti sql19 cat \/etc\/passwd | grep mssql\nmssql:x:10001:0::\/home\/mssql:\/bin\/bash<\/pre>\n
Let\u2019s go ahead and let\u2019s talk about persisting SQL Server database files on an external storage. In this case, we need to refer to the <\/span>Microsoft documentation<\/a><\/span> to configure volumes and underlying storage permissions regarding the scenario we will have to deal with.<\/span><\/span><\/p>\n
If you don\u2019t specify any user (and group) when spinning up the container, the sqlservr process will run with the identity of the mssql user created inside the container and as part of the root group. The underlying host filesystem must be configured accordingly, either a user with same UID = 10001 or the root group GUID = 0). Otherwise chances are you will experience permission issues with the following error message:<\/span><\/span><\/p>\n
SQL Server 2019 will run as non-root by default.\nThis container is running as user mssql.\nTo learn more visit https:\/\/go.microsoft.com\/fwlink\/?linkid=2099216.\n\/opt\/mssql\/bin\/sqlservr: Error: Directory [\/var\/opt\/mssql\/system\/] could not be created.  Errno [13]<\/pre>\n
If you want to run the container as part of a custom user and group created on your own, you must be aware of the different database file placement scenarios. The first one consists in using the default configuration with all the SQL Server logs, data and transaction log files in \/var\/opt\/mssql path. In this case, your custom user UID and GUID can be part of the security context of the hierarchy folder on the host as follows:<\/span><\/span><\/p>\n
$ ls -l | grep sqlserver\ndrwxrwx---. 6 mssql mssql 59 Sep 27 19:08 sqlserver\n\n$ id mssql\nuid=1100(mssql) gid=1100(mssql) groups=1100(mssql),100(users)<\/pre>\n
The docker command below specifies the UID and GUID of my custom user through the -u parameter:<\/span><\/span><\/p>\n
docker run -d \\\n --name sql19 \\\n -u $(id -u mssql):$(id -g mssql) \\\n -e \"MSSQL_PID=Developer\" \\\n -e \"ACCEPT_EULA=Y\" \\\n -e \"SA_PASSWORD=Password1\" \\\n -e \"MSSQL_AGENT_ENABLED=True\" \\\n -e \"MSSQL_LCID=1033\" \\\n -e \"MSSQL_MEMORY_LIMIT_MB=2048\" \\\n -v \"\/u00\/sqlserver:\/var\/opt\/mssql\" \\\n -p 1451:1433 -d 2019-latest-non-root<\/pre>\n
Note the username is missing and replaced by the UID of the mssql user created on my own. <\/span><\/span><\/p>\n
\"\"<\/p>\n
This is a normal behavior because my user is not known within the container namespace. There is no record from my user with UID = 1001. The system only knows the mssql user with UID = 10001 as shown below:<\/span><\/span><\/p>\n
I have no name!@e698c3db2180:\/$ whoami\nwhoami: cannot find name for user ID 1100\n\n$ cat \/etc\/passwd | grep mssql | cut -d\":\" -f 1,3\nmssql:10001<\/pre>\n
For a sake of curiosity, we may wonder how SQL Server makes the choice of using the correct user for the sqlservr process. Indeed, I created two users with the same name but with different UIDs and I think that after some investigations, taking a look at the <\/span><\/span>uid_entry point<\/span><\/a> definition in the microsoft\/mssql-docker github project could help understanding this behavior:<\/span><\/span><\/span><\/p>\n
\"\"<\/p>\n
If we don\u2019t specify the UID \/ GUID during the container\u2019s creation, the whoami command will fail and the mssql user\u2019s UID defined in the Dockerfile (cf. USER mssql) will be chosen.<\/span><\/span><\/p>\n
\u00a0<\/span><\/span>The second scenario consists in introducing some SQL Server best practices in terms of database file placement. In a previous <\/span><\/span>blog post<\/span><\/a>, I wrote about a possible implementation based on a flexible architecture for SQL Server on Linux and which may fit with containers. In this case, database files will be stored outside of the \/var\/opt\/mssql default path and in this case, the non-root container has the restriction that it must run as part of the root group as mentioned in the <\/span><\/span>Microsoft documentation<\/span><\/a>:<\/span><\/span><\/span><\/p>\n
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\u2019t grant any extra root permissions to the non-root user.<\/pre>\n
Here my implementation of the flexible architecture template with required Linux permissions in my context:<\/span><\/span><\/p>\n
$ ls -ld \/u[0-9]*\/sql*2\/\ndrwxrwx---. 2 mssql root    6 Sep 24 22:02 \/u00\/sqlserver2\/\ndrwxrwx---. 2 mssql root 4096 Sep 27 14:20 \/u01\/sqlserverdata2\/\ndrwxrwx---. 2 mssql root   25 Sep 27 14:20 \/u02\/sqlserverlog2\/\ndrwxrwx---. 2 mssql root    6 Sep 24 22:04 \/u03\/sqlservertempdb2\/\ndrwxrwx---. 2 mssql root    6 Sep 27 10:09 \/u98\/sqlserver2\/<\/pre>\n
\u2026 with:<\/p>\n