Knowledge Base

Search Knowledge Base

KB #240107: SQL Maintenance Plans do not delete encrypted database backup or transaction log backup files

Type:

Fix/Workaround

Summary:

You have Configured/Secured your SQL Server instance with these options:

  • Secured with an encryption key using the AES-CBC or AES-ECB algorithm.
  • Secured specifying that all newly created SQL Backups or TRN files are automatically encrypted.
  • Included a Maintenance Plan that creates TRN files on a regular basis.
  • Included a Maintenance Plan that deletes TRN files older than a certain threshold time.

Observations:

  • TRN files are not being deleted according to the specified Maintenance Plan.
  • Windows System Event Log contains numerous Errors with Event ID 1181, Source=NLEMSYS

Additional Information:

Updated December 2018: A patch is now available for this issue. If you have an active Support/Maintenace plan please contact Support for the patch.

Encryptionizer for SQL Server supports SQL Maintenance plans but does require a certain configuration of Encryptionizer.

For Encryptionizer for SQL Server (nlemsys.sys driver v2012.201.10.0 and later, you are able to configure such that Encrypted backups pass the Delete test and the backups are deleted via SQL Maintenance plans.

In general we recommend that databases are encrypted using the AES-CBC or AES-ECB algorithms as those are optimized for SQL database I/O. However, if you configure your backups to be encrypted with the AES-CTR algorithm, the SQL Maintenance Plans will be able to recognize the backup as a valid back-up file and will delete them.

To do so, you must use the following configuration:

  • Open the Encryptionizer Administration Wizard
  • Select the SQL instance to secure
  • Add encryption keys that you will use for your databases – it is recommended to use AES-CBC or AES-ECB.
  • Add one more encryption key using AES-CTR. Note the key number after you have added it. Let’s call this Key N.

  • On the Additional Options screen, check in this order “Encrypt New Databases”, “Encrypt New Backups”, “Custom”
  • On the line below, choose “Specify Rules manually”. This will make the text box below it editable.
  • Modify the key number for either *.bak or &sb and &sc to reference the key number for the AES-CTR key.
    e.g. original rules says: +*.mdf#1;+*.ldf#1;+*.ndf#1;+&sb#1;+&sc#1;
    Modify to be: +*.mdf#1;+*.ldf#1;+*.ndf#1;+&sb#2;+&sc#2;
    where 2 = Key number N for the AES-CTR key created in the previous screenIf you are using a SQL Maintenance Plan to delete old transaction log backups (*.trn), you will need to add a little more to the rules string:
    Modify to be: +*.mdf#1;+*.ldf#1;+*.ndf#1;+&sb#2;+&sc#2;+*.trn#2;

  • finish setting any other options on this screen.
  • Complete securing your SQL instance.

For more information on the use the the “Specify Files to include/exclude..” feature, please see the Whole Database User Guide installed with the software.

Last modified: 12/15/2018

Top