create_and_back_up_cert.j2

-- Enabling NOCOUNT to suppress (1 rows affected) messages from DECLARE
-- keywordss on the output
SET NOCOUNT ON;

DECLARE @cerExists INT;
exec master.dbo.xp_fileexist
  '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer', @cerExists OUTPUT;
DECLARE @pvkExists INT;
exec master.dbo.xp_fileexist
  '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk', @pvkExists OUTPUT;
IF NOT EXISTS(
  SELECT *
  FROM sys.certificates
  WHERE name = '{{ mssql_ha_dbm_cert_name }}'
)
BEGIN
  PRINT 'Certificate {{ mssql_ha_dbm_cert_name }} does not exist, creating';
  IF (@cerExists = 1 AND @pvkExists = 1) OR (@cerExists != @pvkExists)
  BEGIN
    THROW 51000, 'Certificate {{ mssql_ha_dbm_cert_name }} does not exist in \
SQL Server, however, /var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer \
and/or /var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk files do exist. \
You must either remove the files, or run the role with \
`mssql_ha_reset_cert: true` to regenerate certificates.', 1;
  END
  ELSE
  BEGIN
    CREATE CERTIFICATE {{ mssql_ha_dbm_cert_name }} WITH SUBJECT = 'dbm';
    PRINT 'Certificate {{ mssql_ha_dbm_cert_name }} created successfully';
  END
END
ELSE IF @cerExists = 0 AND @pvkExists = 0
BEGIN
  PRINT 'Certificate {{ mssql_ha_dbm_cert_name }} already exists, skipping';
END

IF @cerExists = 1 AND @pvkExists = 1
BEGIN
  PRINT '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer and \
/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk already exist, skipping';
END
ELSE IF @cerExists = 0 AND @pvkExists = 0
BEGIN
  PRINT 'Exporting a certificate and private key to \
/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer and \
/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk';
  BACKUP CERTIFICATE {{ mssql_ha_dbm_cert_name }}
   TO FILE = '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer'
   WITH PRIVATE KEY (
     FILE = '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk',
     ENCRYPTION BY PASSWORD = '{{ mssql_ha_dbm_private_key_password }}'
   );
  PRINT 'Certificate and private key files \
/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer and \
/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk exported successfully';
END
ELSE IF @cerExists = 1 AND @pvkExists = 0
BEGIN
  PRINT '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk does not exist \
while /var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer exists. You must \
either remove the files, or run the role with `mssql_ha_reset_cert: true` to \
regenerate certificates.';
END
ELSE IF @cerExists = 0 AND @pvkExists = 1
BEGIN
  PRINT '/var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.cer does not exist \
while /var/opt/mssql/data/{{ mssql_ha_dbm_cert_name }}.pvk exists. You must \
either remove the files, or run the role with `mssql_ha_reset_cert: true` to \
regenerate certificates.';
END