Prepare machine key for contained availability groups on Linux and containers

Applies to: SQL Server - Linux

This article provides an example of how to prepare a machine key for SQL Server running on Linux in a contained availability group (AG).

A contained AG is an availability group that supports:

• Managing metadata objects (users, logins, permissions, SQL Server Agent jobs, and so on) at the AG level in addition to the instance level.

• Specialized contained system databases within the AG.

The examples in this article target SQL Server in Linux containers, but you can use the same steps for SQL Server on Linux, running on physical machines, virtual machines, and in a Kubernetes-based deployment.

Caution

These instructions should only be used for contained availability groups. When you configure a contained AG with a common machine key across all replicas, first ensure there's no existing encryption hierarchy (for example, transparent data encryption, column-level encryption, or any other security-related feature that requires key management). Changing the machine key could break the encryption and cause data loss. After configuration, avoid creating or modifying the encryption hierarchy for security reasons.

Overview of the machine key

In SQL Server on Linux, the machine key plays a vital role in securing communication and data. The following table describes its primary functions.

Function	Description
Encryption and decryption	The machine key is used to encrypt and decrypt data exchanged between nodes in an AG
Authentication	It helps in authenticating communication between the primary and secondary replicas in an AG
Security	The machine key and associated certificates must be protected to prevent unauthorized access

When you work with SQL Server 2022 (16.x) on Linux and contained AGs, you must synchronize the machine keys between SQL Server replicas. This process allows the service master key (SMK) in the contained master database to be used for decrypt and encrypt operations.

Prepare SQL Server container on standalone host to run custom machine key

The following instructions show an example of generating a new machine key in base64 format.

1. Generate a machine key file using OpenSSL (into a file called machine-key.bin) using the following script.

   openssl rand -out machine-key.bin 44
   printf '\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | dd of=machine-key.bin bs=1 seek=0 count=12 conv=notrunc
   cat machine-key.bin | base64
   

   The previous script performs these steps:

   1. Create a 44-byte binary file called machine-key.bin, containing random data.
   2. Overwrite the first 12 bytes with a fixed header. This might change in the future.
   3. Encode machine-key.bin in base64 format and print it to the console.

2. Run the container by mounting the file to the machine key of the SQL Server container.

   docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourpassword" -p 14331:1433 --name sqlcontainer --hostname sqlcontainer -v ~/machine-key.bin:/var/opt/mssql/secrets/machine-key -d mcr.microsoft.com/mssql/server:2022-latest
   

3. Verify that the machine-key.bin on the host machine is the same as the machine-key.bin used by the SQL Server container deployed in the previous step.

   cat machine-key.bin | base64
   

   Here's the sample output:

   AQAAAAAAAAAAAAAA//////////////////////////////////////////8=
   

4. Inside the container, verify the key. First, connect to an interactive terminal in the container.

   docker exec -it sqlcontainer "bash"
   

   Then, verify the key.

   cat machine-key | base64
   

   Here's the sample output:

   AQAAAAAAAAAAAAAA//////////////////////////////////////////8=
   

You can use the same steps across all the SQL Server container deployments that you intend to use as replicas in your contained AG. You must use the same machine key, and don't generate different machine keys for each of the SQL Server container deployments.

Related content

• Deploy and connect to SQL Server Linux containers
• Quickstart: Deploy a SQL Server Linux container to Kubernetes using Helm charts