SQL scripts

Windocks provides a flexible mechanism to run scripts on databases at image build time, container create time, system level scripts that are run before attaching databases, database level scripts that are run after databases are attached, different user accounts under which to run the scripts, etc. These scripts are used for masking data, synthetic test data generation, extensible key managers (EKMs), release upgrade and rollback, and more.

In all script files, you must use ; (semi-colon) to separate SQL lines. Also note that GO is not supported.

There are four types of script extensions:
  • filename.sql - Runs the script after databases are attached. Use files with the extension .sql to run masking, synthetica data generation, release upgrade and rollback scripts. These scripts run on databases that are cloned by Windocks
  • filename.sqlsys - Runs the script before anything is done in the image or container. Use the .sqlsys extension to run scripts at the system level - managing user accounts, system level certificates or keys, third party encryption systems, tweaking database server level settings.
  • filename.sqlrunas - Same as .sql extension but runs by authenticating against the image or container database system using the credentials specified.
  • filename.sqlsysrunas - Same as .sqlsys extension but runs by authenticating against the image or container database server using the credentials specified. Use this for third party key management systems which require the use of a SQL account to work.

.SQL scripts

masking.sql
USE yourdbname;
ALTER TABLE employees
DROP COLUMN socialsecuritynumber;


Put the masking.sql script in the same directory as dockerfile

dockerfile
FROM mssql-2017
# Or mysql-5.6 or postgre-x.x etc

# Remove this line of you want to update the image with incremental transaction log backups ENV FULL_BACKUPS_ONLY=1

SETUPCLONING FULL yourdbname path\to\backup

COPY masking.sql .
# Remove this line of you want to update the image with incremental transaction log backups RUN masking.sql

# To run the masking script at container create time, which you MUST do for incremental transaction log backups, uncomment the 2 lines below
# ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
# RUN masking.sql


.SQLSYS scripts

adduniqueuserforcontainer.sqlsys
CREATE LOGIN WITH PASSWORD = '';



Put the adduniqueuserforcontainer.sqlsys script in the same directory as dockerfile

dockerfile
FROM mssql-2017
# Or mysql-5.6 or postgre-x.x etc

SETUPCLONING FULL yourdbname path\to\backup

COPY adduniqueuserforcontainer.sqlsys .

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# This script is run before the database yourdbname is attached to the container database server
RUN adduniqueuserforcontainer.sqlsys


.SQLRUNAS scripts

restrictedscript.sqlrunas
-- Put your script that runs against a DB with a specific user name here



This script needs to be run with a specific account name and password. First make sure that there is a SQL account in the installed SQL Server used by Windocks with the specified user name and password. Since Windocks uses the installed SQL Server, any containers will also contain that user account.

Encrypt the password with windock\bin\encrypt.exe and put the encrypted password in the file windocks\config\node.conf as follows:
SQLRUNAS_PASSWORD1="put encrypted password here"

Restart the Windocks service

Put the restrictedscript.SQLRUNAS script in the same directory as dockerfile

dockerfile
FROM mssql-2017
# Or mysql-5.6 or postgre-x.x etc

SETUPCLONING FULL yourdbname path\to\backup

COPY restrictedscript.sqlrunas .

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# This script is run after the database yourdbname is attached to the container database server
RUN restrictedscript.sqlrunas username SQLRUNAS_PASSWORD1


.SQLSYSRUNAS scripts

ekmscript.sqlsysrunas
-- Put your script for EKM system here



This script needs to be run with a specific account name and password. Encrypt the password with windock\bin\encrypt.exe and put the encrypted password in the file windocks\config\node.conf as follows:
SQLRUNAS_PASSWORD1="put encrypted password here"

Restart the Windocks service

Put the ekmscript.SQLSYSRUNAS script in the same directory as dockerfile

dockerfile
FROM mssql-2017
# Or mysql-5.6 or postgre-x.x etc

SETUPCLONING FULL yourdbname path\to\backup

COPY ekmscript.sqlsysrunas .

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# This script is run before the database yourdbname is attached to the container database server
RUN ekmscript.sqlsysrunas username SQLRUNAS_PASSWORD1