Managing SQL Server sa credentials with Docker secrets on Swarm

A couple of weeks ago, I was working on a MSSQL Server docker image in a context of Hidora, a swiss cloud provider based on jelastic and for Docker-based applications.

When writing my jps manifest file I was agreeably surprised about the section concerning the MSSQL Server credentials information. We may able to define global variables for SQL Server sa password with ${fn.password} as function as shown below:

globals:
  sa_password: ${fn.password}
  user_password: ${fn.password}

nodes:
  - nodeGroup: cp
    count: 1
    cloudlets: 30
    displayName: mssqlserver-linux-2017-cu4 dbi services
    env:
      MSSQL_SA_PASSWORD: ${globals.sa_password}
      MSSQL_PASSWORD: ${globals.user_password}
      MSSQL_USER: ${globals.user_login} 
      TZ: Europe/Berlin
    image: dbi_linux_sql2017_cu4
    registryUrl: node21755-env-6328816:5000

In fact, jelastic provides this interesting function that generates a random password during the creation of the image container preventing to put the security information in clear text into the manifest. A very good idea!

But let’s going back to a traditional docker infrastructure. Usually as developers, we put sensible information in the docker-compose file deployment but that’s not a big deal in this case (at least for almost cases). If we start containers based on our image in a Docker infrastructure, we still able to get the SQL Server sa password by running the docker inspect command.

$docker inspect 23107bf057ef | jq .[].Config.Env
[
  "ACCEPT_EULA=Y",
  "SA_PASSWORD=SuperSecretPassword1234",
  "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
]

Definitely, one thing we want to avoid in Production. Sometimes ago, I wrote about Docker Swarm feature that enables addressing production-oriented workload including scalability, high-availability and others. In production, the game may change a lot because we have to manage sensible data as login credentials and fortunately we may rely on a Docker Swarm feature called Docker secrets.

As stated to the Docker documentation, when we create and add a secret to a swarm, Docker sends the secret to the swarm manager over a mutual TLS connection. The secret is stored in the Raft log, which is encrypted. The entire Raft log is replicated across the other managers, ensuring the same high availability guarantees for secrets as for the rest of the swarm management data.

This feature may address the security concern with MSSQL Server containers and sa credentials in production workloads. Indeed, as database administrator, we don’t want to provide the sa password to the application users and we will go further by providing a SQL Server login with a set of scope-limited permissions and without providing any password in clear text in the docker deployment file.

Thus, we managed to modify our initial MSSQL Docker image to support Docker secrets on Docker Swarm. In this blog, let’s focus on SQL Server sa password. Firstly we have to create a docker secret concerning the SQL Server sa password

$echo "Passw0rd1" | docker secret create mssql_sa_password –
$ docker secret ls
ID                          NAME                  DRIVER              CREATED             UPDATED
fpqykdgr4ytcher1j3sb5tgfv   mssql_sa_password                         35 minutes ago      35 minutes ago

The mssql_sa_password secret is then replicated to the other nodes by the Docker Swarm using TLS as explained above.

The second step consisted in modifying the docker file as well as the docker-compose file for deployment. The former contains two important sections where we had to put the additional code to extract the docker secret information as entrypoint.sh and healthcheck.sh

The Docker file (I put only the interesting sample here):

# Entry point # 
ENTRYPOINT ["./entrypoint.sh"]

# Tail the setup logs to trap the process
CMD ["tail -f /dev/null"]

# Healthcheck routine for mssql instance
HEALTHCHECK --interval=15s CMD [ "./healthcheck.sh" ]

entrypoint.sh bash script includes starting up the sqlservr process and healhcheck.sh a custom health check routine based on sqlcmd command line tool (meaning this approach requires mssql-tools package is already installed in your image).

/opt/mssql-tools/bin/sqlcmd -S localhost,$MSSQL_TCP_PORT -U sa -P $SA_PASSWORD -Q "select 1" && grep -q "MSSQL CONFIG COMPLETED" ./config.log

The code to leverage Docker secrets was as follows:

if [  ! -z ${MSSQL_SA_PASSWORD_FILE} ];
then 
    SA_PASSWORD=$(cat $MSSQL_SA_PASSWORD_FILE)
else
    SA_PASSWORD=${MSSQL_SA_PASSWORD}
fi

We added a new $MSSQL_SA_PASSWORD_FILE variable that takes priority over the $MSSQL_SA_PASSWORD if exists. The $MSSQL_SA_PASSWORD_FILE points to the path where the secret is available inside the Docker container by design: /run/secrets/<secret file>. We tried to follow a standard rule that consists in adding the _FILE prefix to the existing SQL Server sa variable (MSSQL_SA_PASSWORD) for convenience.

Finally, we modified the docker-compose file for deployment that contains all information to connect to the secret password so we may switch easily between using the traditional approach with the password in clear text in the deployment file and the securest way to manage sensible data on Docker Swarm.

After applying the code update, using docker inspect command doesn’t reveal the password anymore.

$docker inspect 62c42040174a | jq .[].Config.Env
[
  "ACCEPT_EULA=Y",
  "MSSQL_PID=Developer",
  "MSSQL_SA_PASSWORD_FILE=/run/secrets/mssql_sa_password",
…
]

As you probably know, Docker EE 2.0 is now able to manage container applications both on Docker Swarm and Kubernetes. I’m looking forward to write about for both environments in the context of MSSQL Server databases and managing sensible data 🙂

See you!

By David Barbarin