Running kubernetes jobs against Microsoft SQL Server (MSSQL)

A TL;DR for Linux DevOps peeps.

I’m not an MS SQL DBA. My background is Linux Sysadmin, which is about as far away from any Microsoft product as one could be. But in this brave new world, MSSQL can run on Linux, and can be run in a Linux container on Kubernetes.

I found myself involved in a project that needed to automate the loading of some SQL statements against an MS SQL database.

So I saw sqlcmd and tried to automate the task with a k8s job that would use mcr.microsoft.com/mssql-tools:latestto run something like this:

sqlcmd -S sql-${INSTANCE_NAME} -U user1 -P ${DB_PASS} -i /some/file.sql

But it kept throwing authentication errors. Even though when I launched a debug copy of the container via oc debug and copy-pasted the startup command, it worked.

So I went to the SQLcmd documentation (I know, right?)

Turns outsqlcmd will utilize any of several environment variables if they exist:
— SQLCMDSERVER
— SQLCMDLOGINTIMEOUT
— SQLCMDDBNAME
— SQLCMDUSER
— SQLCMDPASSWORD

So, by using a k8s job definition with the expected environment variables configured, my working command ended up being effectively: sqlcmd -i /files/file.sql

I saved the database password as a k8s Secret, and configured the job to retrieve that and set it as the value of SQLCMDPASSWORD environmental variable.

The SQL file was loaded into a k8s ConfigMap, so that it could be consumed by the kubernetes job.

In my use case, the Secret, ConfigMap and Job are all processed or created by one or more pipelines.

Below is an example kubernetes job that:
— Gets the SQL file to be processed from a ConfigMap
— Obtains SQLCMDPASSWORD via a k8s secret

apiVersion: batch/v1
kind: Job
metadata:
 labels:
 job-name: sql-script-${JOB_NUMBER}
 job-type: sql-script
 name: sql-script-${JOB_NUMBER}
 namespace: ${my_ns}
spec:
 activeDeadlineSeconds: 120
 backoffLimit: 0
 completions: 1
 parallelism: 1
 selector:
 matchLabels: {}
 template:
 metadata:
 creationTimestamp: null
 labels:
 job-name: sql-script-${JOB_NUMBER}
 job-type: sql-script
 name: sql-script-${JOB_NUMBER}
 spec:
 containers:
 — args:
 — -i
 — /files/${SQL_FILE}
 command:
 — /opt/mssql-tools/bin/sqlcmd
 env:
 — name: NAME
 value: sql
 — name: SQLCMDDBNAME
 value: thisDB
 — name: DBUSER
 value: sa
 — name: SQLCMDPASSWORD
 valueFrom:
 secretKeyRef:
 key: db-pass
 name: sql-${INSTANCE_NAME}
 image: ${MS-SQLTOOLS_IMAGE}
 imagePullPolicy: IfNotPresent
 name: sqltools
 terminationMessagePath: /dev/termination-log
 terminationMessagePolicy: File
 volumeMounts:
 — mountPath: /files
 name: files
 readOnly: true
 dnsPolicy: ClusterFirst
 restartPolicy: Never
 schedulerName: default-scheduler
 terminationGracePeriodSeconds: 30
 volumes:
 — configMap:
 defaultMode: 420
 name: files-${JOB_NUMBER}
 name: files