Type:
Fixed
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 February 2019: All installer builds nlfsetup.fsql.2018.1202.31132.10.exe and later address this issue (nlemsys.sys v2018.611.32.0 and later). Please check your License Portal for newer versions or contact Support.
For versions earlier than nlemsys.sys v2018.611.32.0, please see below.
——————————————-
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 through v2018.611.31), 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.