Knowledge Base

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)

Additional Information:

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 the ability to use xp_delete_file in 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 xp_delete_file.

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 xp_delete_file will be able to recognize the backup as a valid back-up file. 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 “Encrypt New Databases and Backups”
  • On this same screen, check “Specify Files to include/exclude..” feature and then click the “Specify” button.
  • This screen allows you to specify the rules for which files are automatically encrypted on created, and with which encryption key (based on Key number).
  • If you want all new databases to be encrypted on creation, you can leave *.* in the list in the middle of the screen.
  • In the file mask field, enter *.bak, select Key N (which is the AES-CTR key you set up above) and click “Include” (if you only want certain backups encrypted you can have [databasename]*.bak)
  • when you have completed defining your rules, you can click the “Continue” button.
  • 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]’.