Running kubernetes jobs against Microsoft SQL Server (MSSQL)

Andrew McIver
2 min readSep 15, 2021

--

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

--

--

Andrew McIver

I’m a Mech Warrior, the Bearded Wolverine at LSDopen.io where we use Open Source solutions to solve real business challenges.