K8S secret usage in SQLCMD password is always empty

Question

Im trying to connect to an SQL server using K8S secret for password but no matter what syntax or method i want to use the password always empty. If i hard code the password everything works fine.

I can also print the secret in the POD using this command and its also returns the password stored in secret so the POD can actually access to the secret.

kubectl exec -it podname -- printenv MSSQL_SA_PASSWORD

Im trying to run this little healthcheck.

 start-sql.sh: |
    #!/bin/bash

    # Start SQL Server in the background
    /opt/mssql/bin/sqlservr &

    echo "The password being used is: $MSSQL_SA_PASSWORD"
    echo "Waiting for SQL Server to start..."
    for i in {1..120}; do
      /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -Q "SELECT 1"
      if [ $? -eq 0 ]; then
        echo "SQL Server is up and running."
        break
      else
        echo -n "."
        sleep 1
      fi
    done

here is my init container

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: ${LOWERASSET}
  labels:
    app: ${ASSET}
spec:
  replicas: 1
  serviceName: ${LOWERASSET}
  selector:
    matchLabels:
      app: ${ASSET}
  template:
    metadata:
      labels:
        app: ${ASSET}
    spec:
      securityContext:
        fsGroup: 10001
      initContainers:
      - name: init-sql
        image: ${IMAGE}
        resources:
          requests:
            cpu: "200m"
            memory: "2Gi"
          limits:
            cpu: "500m"
            memory: "4Gi"
        command: ["/bin/bash", "/mnt/init/start-sql.sh"]
        env:
        - name: MSSQL_PID
          value: Developer
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_ENABLE_HADR
          value: "1"
        - name: MSSQL_AGENT_ENABLED
          value: "1"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: sql-server
              key: pwd
        volumeMounts:
        - name: init-script
          mountPath: /mnt/init
        - name: ${LOWERASSET}
          mountPath: /var/opt/mssql
      containers:
      - name: sqlserver
        image: ${IMAGE}
        resources:
          requests:
            cpu: "200m"
            memory: "2Gi"
          limits:
            cpu: "500m"
            memory: "4Gi"
        ports:
        - containerPort: 1433
          name: tcpsql
        env:
        - name: MSSQL_PID
          value: Developer
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_ENABLE_HADR
          value: "1"
        - name: MSSQL_AGENT_ENABLED
          value: "1"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: sql-server
              key: pwd
        volumeMounts:
        - name: ${LOWERASSET}
          mountPath: /var/opt/mssql
      volumes:
      - name: init-script
        configMap:
          name: sql-init-script
  volumeClaimTemplates:
  - metadata:
      name: ${LOWERASSET}
      labels:
        app: ${ASSET}
        backup: "${BACKUP}"
    spec:
      storageClassName: encrypted-standard
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 8Gi

---
kind: Service
apiVersion: v1
metadata:
  name: ${LOWERASSET}
  labels:
    app: ${ASSET}
spec:
  type: ClusterIP
  selector:
    app: ${ASSET}
  ports:
  - name: tcpsql
    protocol: TCP
    port: 1433

What should be correct way to use secret in sqlcmd ??

Answer 1

So based on Kubernetes docs i can pass the env variable as an "args" and this way i can use it in my bash command

command: ["/bin/bash", "/mnt/init/start-sql.sh"]
          args:
            - "$(MSSQL_SA_PASSWORD)"
          env:
            - name: MSSQL_SA_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: sql-server
                  key: pwd

I can now retrive the env variable and see it in the logs.

 echo "The password being used is: $1"