Microsoft SQL Server

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

To apply a SQL Server license in a Docker container, you primarily use environment variables during the docker run command or within a docker-compose.yml file. Unlike traditional Windows installations, you typically do not enter a 25-character product key in a setup wizard. 

1. Using Environment Variables

When starting a container, you must set two critical environment variables to activate your licensed edition: 

• ACCEPT_EULA=Y: Confirms your acceptance of the End User License Agreement.
• MSSQL_PID: Specifies the edition or product key.
  • Standard/Enterprise/Web: Pass the edition name (e.g., -e 'MSSQL_PID=Standard') if your licensing is handled via Microsoft Volume Licensing or a subscription.
  • Product Key: If you have a specific 25-character product key, pass it directly (e.g., -e 'MSSQL_PID=ABCDE-FGHIJ-KLMNO-PQRST-UVWXY').
  • Free Editions: Use Developer or Express for non-production or lightweight use. 

Example Docker Command:

bash

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrongPassword" \
-e "MSSQL_PID=Standard" -p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2022-latest

Use code with caution.

2. Changing the License of a Running Container

If you need to upgrade an existing container (e.g., from Developer to Standard), you can use the mssql-conf tool inside the container or update your docker-compose file and restart. 

1. Enter the container:
   docker exec -it --user root <container_id> bash
2. Set the new PID:
   /opt/mssql/bin/mssql-conf set-edition (Follow the prompts to enter your key or edition).
3. Restart the container:
   docker restart <container_id>. 

3. Licensing Compliance

• Per-Core Licensing: When licensing per virtual machine/container, you must assign a license for every virtual core (hardware thread) available to that container, with a minimum of four licenses per container.
• Software Assurance: Most container-based licensing for production (Standard/Enterprise) requires Microsoft Software Assurance or a subscription license. 

To deploy a licensed SQL Server instance with persistent storage using Docker Compose, you must define your license edition (or key) and map a persistent volume to the container’s internal data directory. 

1. The docker-compose.yml Template 

This template uses named volumes for data persistence, which is the recommended method for SQL Server on Linux. 

yaml

version: '3.8'

services:
  sql-server:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sql-production
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Password2024
      <em># Apply license here: Use 'Standard', 'Enterprise', 'Web', or a 25-char key</em>
      - MSSQL_PID=Standard 
    ports:
      - "1433:1433"
    volumes:
      - sql_data:/var/opt/mssql <em># Persists all DBs, logs, and secrets</em>
    restart: always

volumes:
  sql_data:
    driver: local

Use code with caution.

2. Key Components Breakdown

• MSSQL_PID: This is where you apply your license. You can use an edition name like Standard, Enterprise, or Web if you have a volume license, or paste your 25-character product key directly.
• Persistent Volume: Mapping to /var/opt/mssql ensures that your system databases (master, model, msdb) and all user databases survive container restarts or removals.
• ACCEPT_EULA=Y: This is a mandatory requirement to start any SQL Server container. 

3. Professional Best Practice: Using an .env File 

To avoid hardcoding sensitive information like your license key or SA_PASSWORD in your YAML file, use a Docker Environment File. 

Create a .env file in the same folder:

text

MSSQL_PID=ABCDE-FGHIJ-KLMNO-PQRST-UVWXY
MSSQL_SA_PASSWORD=YourStrong!Password

Use code with caution.

Update your docker-compose.yml: 

yaml

    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=${MSSQL_SA_PASSWORD}
      - MSSQL_PID=${MSSQL_PID}

Use code with caution.

4. Deploying

Run the following command to start the licensed instance in the background: 

bash

docker-compose up -d

To restore a .bak file into a SQL Server Docker container, you must move the file into the container’s file system and then execute a RESTORE command using the Linux-based file paths. 

1. Copy the .bak File into the Container 

Use the docker cp command to move your backup file from your host machine into the container’s internal storage. 

bash

<em># Example: Copying 'mybackup.bak' to the container's data folder</em>
docker cp mybackup.bak sql-production:/var/opt/mssql/data/

Use code with caution.

2. Identify Logical File Names 

SQL Server backups contain logical names for data and log files that may not match your desired filenames. You must identify these to use the MOVE command correctly. 

bash

docker exec -it sql-production /opt/mssql-tools18/bin/sqlcmd \
   -S localhost -U sa -P 'YourStrongPassword' \
   -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/data/mybackup.bak'"

Use code with caution.

Note the LogicalName column in the output for both the Data and Log files. 

3. Execute the Restore Command 

Run the RESTORE DATABASE command. You must explicitly MOVE the logical files to the Linux container’s internal paths. 

bash

docker exec -it sql-production /opt/mssql-tools18/bin/sqlcmd \
   -S localhost -U sa -P 'YourStrongPassword' \
   -Q "RESTORE DATABASE [NewDBName] \
   FROM DISK = '/var/opt/mssql/data/mybackup.bak' \
   WITH MOVE 'LogicalDataName' TO '/var/opt/mssql/data/NewDBName.mdf', \
   MOVE 'LogicalLogName' TO '/var/opt/mssql/data/NewDBName.ldf'"

Use code with caution.

Summary of Best Practices

• Path Mapping: Since you are using persistent storage (as set up in the previous step), copying files to /var/opt/mssql/data/ ensures they are accessible by the SQL Server engine.
• Permissions: If you encounter an “Access Denied” error, ensure the file within the container is owned by the mssql user.
• Tools: Most modern SQL Server 2022 containers use mssql-tools18. If your command fails, try /opt/mssql-tools/bin/sqlcmd instead. 

To automate a database restore in Docker Compose, you can use a “sidecar” initialization script. This script waits for the SQL Server engine to be fully ready before executing the RESTORE command. 

1. Create a restore.sql Script 

Save this file in your project folder. It tells SQL Server where to find the .bak and where to put the new database files. 

sql

<em>-- restore.sql</em>
RESTORE DATABASE [MyDatabase]
FROM DISK = '/var/opt/mssql/backup/mybackup.bak'
WITH MOVE 'LogicalDataName' TO '/var/opt/mssql/data/MyDatabase.mdf',
     MOVE 'LogicalLogName' TO '/var/opt/mssql/data/MyDatabase.ldf',
     REPLACE; <em>-- Overwrites if it already exists</em>
GO

Use code with caution.

2. Create an import-data.sh Shell Script 

This script loops until SQL Server is up and then runs your SQL. 

bash

<em># import-data.sh</em>
<em># Wait for SQL Server to start (max 60 seconds)</em>
for i in {1..60}; do
    /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -Q "SELECT 1" > /dev/null 2>&1
    if [ $? -eq 0 ]; then
        echo "SQL Server is ready, starting restore..."
        /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -i /var/opt/mssql/backup/restore.sql
        break
    else
        echo "SQL Server still starting..."
        sleep 1
    fi
done

Use code with caution.

3. Update your docker-compose.yml 

You can use a custom entrypoint to run both the SQL Server engine and your restore script simultaneously. 

yaml

services:
  sql-server:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sql-production
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Password
      - MSSQL_PID=Standard
    volumes:
      - sql_data:/var/opt/mssql
      - ./mybackup.bak:/var/opt/mssql/backup/mybackup.bak
      - ./restore.sql:/var/opt/mssql/backup/restore.sql
      - ./import-data.sh:/var/opt/mssql/backup/import-data.sh
    <em># This command starts the restore script in the background, then starts SQL Server</em>
    command: >
      bash -c "chmod +x /var/opt/mssql/backup/import-data.sh && 
               /var/opt/mssql/backup/import-data.sh & 
               /opt/mssql/bin/sqlservr"
volumes:
  sql_data:

Use code with caution.

Why this works

• Background Execution: The & in the command starts import-data.sh as a background process so it doesn’t block the main SQL Server process (sqlservr) from starting.
• Health Check Loop: The script won’t fail if the engine takes 10 or 30 seconds to boot; it waits patiently for a successful connection before attempting the restore.
• Idempotency: Using REPLACE in the SQL script ensures that if the container restarts, it simply ensures the latest version of your backup is active. 

To integrate a self-hosted HashiCorp Vault with your SQL Server Docker container, the most secure method is to have the container retrieve its own credentials at runtime. This avoids leaving sensitive passwords in docker-compose.yml or static .env files. 

1. Store the Secret in Vault 

Assuming your Vault is at https://vault.yourdomain.com, store your SQL SA password in the Key-Value (KV) engine: 

bash

<em># Authenticate and store the secret</em>
export VAULT_ADDR='https://vault.yourdomain.com:8200'
vault kv put secret/mssql sa_password="YourVaultSecurePassword123!"

Use code with caution.

2. The “Sidecar” Pull Method (Automation)

Since the official SQL Server image doesn’t natively talk to Vault, you can use a custom entrypoint script that fetches the password using curl and the Vault API before starting the database. 

vault-entrypoint.sh:

bash

<em>#!/bin/bash</em>
<em># Fetch the secret using your Vault Token (or AppRole)</em>
VAULT_RESPONSE=$(curl -s -H "X-Vault-Token: $VAULT_TOKEN" \
  $VAULT_ADDR/v1/secret/data/mssql)

<em># Parse the password using jq</em>
export MSSQL_SA_PASSWORD=$(echo $VAULT_RESPONSE | jq -r '.data.data.sa_password')

<em># Now run the original entrypoint/startup commands</em>
/opt/mssql/bin/mssql-conf set-edition <em># If needed</em>
/opt/mssql/bin/sqlservr

Use code with caution.

3. Updated docker-compose.yml

Pass the Vault connection details as environment variables. For better security, use AppRole instead of a raw root token. 

yaml

services:
  sql-server:
    image: ://mcr.microsoft.com
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Standard
      - VAULT_ADDR=https://vault.yourdomain.com:8200
      - VAULT_TOKEN=${VAULT_TOKEN} <em># Pass this from your host environment</em>
    volumes:
      - ./vault-entrypoint.sh:/usr/local/bin/vault-entrypoint.sh
    entrypoint: ["/bin/bash", "/usr/local/bin/vault-entrypoint.sh"]
    <em># Ensure jq is installed in the container or use a custom Dockerfile</em>

Use code with caution.

Security Best Practices

• AppRole Authentication: Instead of a long-lived VAULT_TOKEN, use AppRole to provide the container with a role_id and secret_id that it exchanges for a short-lived token at startup.
• Response Wrapping: For high-security environments, use Response Wrapping so the container only has a one-time-use token to fetch its “real” credentials.
• Vault Agent: For complex setups, run a separate Vault Agent container as a sidecar that manages token renewal and writes secrets to a shared memory volume for the SQL container to read. 

To build a production-ready SQL Server image that integrates with your self-hosted HashiCorp Vault, you can create a custom Dockerfile. This pre-installs jq for parsing Vault’s JSON responses and packages your automated entrypoint script. 

1. Create the vault-entrypoint.sh 

This script fetches the secret from Vault before launching SQL Server. It uses mssql-tools18 for connectivity. 

bash

<em>#!/bin/bash</em>
<em># vault-entrypoint.sh</em>

<em># 1. Fetch secret from Vault (using KV-V2 engine)</em>
<em># Assuming VAULT_ADDR and VAULT_TOKEN are provided via Docker Compose</em>
VAULT_RESPONSE=$(curl -s -H "X-Vault-Token: $VAULT_TOKEN" "$VAULT_ADDR/v1/secret/data/mssql")

<em># 2. Extract the password using jq</em>
export MSSQL_SA_PASSWORD=$(echo "$VAULT_RESPONSE" | jq -r '.data.data.sa_password')

<em># 3. Validation: Stop if password is empty</em>
if [ -z "$MSSQL_SA_PASSWORD" ] || [ "$MSSQL_SA_PASSWORD" == "null" ]; then
    echo "ERROR: Failed to retrieve SA password from Vault."
    exit 1
fi

<em># 4. Hand off to the original SQL Server binary</em>
exec /opt/mssql/bin/sqlservr

Use code with caution.

2. Create the Dockerfile 

Modern SQL Server 2022 images are based on Ubuntu 22.04. We will use apt-get to install dependencies. 

dockerfile

<em># Use the official SQL Server 2022 image</em>
FROM mcr.microsoft.com/mssql/server:2022-latest

<em># Switch to root to install packages</em>
USER root

<em># Install jq and curl for Vault communication</em>
RUN apt-get update && \
    apt-get install -y jq curl && \
    rm -rf /var/lib/apt/lists/*

<em># Copy the entrypoint script and make it executable</em>
COPY vault-entrypoint.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/vault-entrypoint.sh

<em># Switch back to the mssql user for security</em>
USER mssql

<em># Set the entrypoint to our custom script</em>
ENTRYPOINT ["/usr/local/bin/vault-entrypoint.sh"]

Use code with caution.

3. Update docker-compose.yml

Build the custom image and provide the Vault connection details.

yaml

services:
  sql-server:
    build: .
    container_name: sql-production
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Standard
      - VAULT_ADDR=https://vault.yourdomain.com:8200
      - VAULT_TOKEN=${VAULT_TOKEN} <em># Pass from your local environment or CI/CD</em>
    ports:
      - "1433:1433"
    volumes:
      - sql_data:/var/opt/mssql
    restart: always

volumes:
  sql_data:

Use code with caution.

Security Considerations

• AppRole: For higher security, replace the VAULT_TOKEN with a VAULT_ROLE_ID and VAULT_SECRET_ID. Modify your script to first trade these for a temporary token.
• Persistent Data: The /var/opt/mssql volume ensures that even if you rebuild the image, your databases remain intact.
• Executable Permissions: Ensure vault-entrypoint.sh has execution permissions on your host machine before building the image (chmod +x vault-entrypoint.sh). 

To transition from a static token to AppRole, your container will use a ROLE_ID and a SECRET_ID to perform a “login” and receive a short-lived token. This is the recommended “pull” model for automated systems. 

1. Setup in Vault

First, configure the AppRole and a policy that only allows reading the SQL password. 

bash

<em># Enable AppRole auth</em>
vault auth enable approle

<em># Create a policy for the SQL container</em>
vault policy write mssql-policy - <<EOF
path "secret/data/mssql" {
  capabilities = ["read"]
}
EOF

<em># Create the role</em>
vault write auth/approle/role/sql-server \
    token_policies="mssql-policy" \
    token_ttl=1h \
    token_max_ttl=4h

<em># Retrieve the IDs (Role ID is static; Secret ID is a one-time credential)</em>
vault read auth/approle/role/sql-server/role-id
vault write -f auth/approle/role/sql-server/secret-id

Use code with caution.

2. Updated vault-entrypoint.sh

Modify your entrypoint script to exchange the AppRole IDs for a session token before fetching the secret. 

bash

<em>#!/bin/bash</em>
<em># vault-entrypoint.sh</em>

<em># 1. Exchange Role ID and Secret ID for a Vault Token</em>
VAULT_LOGIN_RESPONSE=$(curl -s --request POST \
    --data "{\"role_id\":\"$VAULT_ROLE_ID\",\"secret_id\":\"$VAULT_SECRET_ID\"}" \
    "$VAULT_ADDR/v1/auth/approle/login")

<em># 2. Extract the client token</em>
TEMP_TOKEN=$(echo "$VAULT_LOGIN_RESPONSE" | jq -r '.auth.client_token')

<em># 3. Use the temp token to fetch the actual SA password</em>
VAULT_RESPONSE=$(curl -s -H "X-Vault-Token: $TEMP_TOKEN" \
    "$VAULT_ADDR/v1/secret/data/mssql")

export MSSQL_SA_PASSWORD=$(echo "$VAULT_RESPONSE" | jq -r '.data.data.sa_password')

<em># 4. Start SQL Server</em>
exec /opt/mssql/bin/sqlservr

Use code with caution.

3. Docker Compose with AppRole

In production, deliver the VAULT_SECRET_ID as a Docker Secret or through your CI/CD pipeline to keep it out of the YAML file. 

yaml

services:
  sql-server:
    build: .
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Standard
      - VAULT_ADDR=https://vault.yourdomain.com:8200
      - VAULT_ROLE_ID=your-static-role-id-here
      - VAULT_SECRET_ID=${VAULT_SECRET_ID} <em># Delivered via CI/CD or .env</em>
    <em># ... volumes and ports as before</em>