Search Knowledge Base
KB #240107: SQL Maintenance Plans do not delete encrypted backup files
SQL Maintenance Plans typically utilize an extended stored procedure, xp_delete_file, which tests backup files prior to deleting to verify that the file is a SQL Backup. Encrypted backup files will fail the test and not be deleted, unless the SQL instance is secured with an AES-CTR encryption key, and the backup is encrypted with one of the AES-CTR encryption keys for the secured server (Encryptionizer version 2012.201.10 and up)
SQL Server Maintenance plans use an undocumented Microsoft extended stored procedure, xp_delete_file. This extended stored procedure verifies that a file is a valid backup or transaction log file prior to deletion. If the file is not verified as a valid backup or transaction file, the file will not be deleted, and without any error notification. This extended stored procedure is generally not recommended for use by Microsoft and you will find a lot of chatter about difficulty with its use on message boards. Nonetheless, it is used in the SQL Maintenance Plans that are created via the point-and-click interface.
Encryptionizer for SQL Server FIPS 140-2 Validated 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 up), you are able to configure such that Encrypted backups pass the xp_delete_file 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 screen
- 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.
For Encryptionizer for SQL Server (nlemsql.sys driver 2008.401.40 and below), Encrypted backups cannot be detected as valid by xp_delete_file. Our recommended solution is to modify your maintenance plan and create a shell script,
e.g. xp_cmdshell ‘del [filename]’.
Last modified: 5/8/2018