Using LDT with SQL FILESTREAM

The following section is for guarding an SQL Server FileStream database with an LDT policy. If the database contains FileTables directories instead, then follow the instructions in Using CTE with SQL FileTables.

Creating a policy for LDT

To guard an SQL Server FileStream Database with an LDT policy, create an appropriate policy on CipherTrust Manager.

1. In a web browser, navigate to the URL of the CipherTrust Manager you want to use and log in with CipherTrust Manager Administrator credentials.

2. If the client you want to protect is registered to the default domain (root), proceed to the next step. If you need to change to a different domain, do the following:

   1. In the top menu bar, click the domain/user name (default: root/admin) on the right-hand side.

   2. Select Switch Domains, then select the domain in which the client is registered.

   3. The logged in user now shows the new domain name/user name.

      

3. In the CipherTrust Manager Applications Page, open the CTE application.

4. In the left-hand menu bar on the Clients page, click Policies.

5. Click Create Policy. CipherTrust Manager displays the Create Policy Wizard.

6. On the General Info page, set the following options:

   Field	Description
   Name	A unique name for the policy. Make sure you use a name that is descriptive and easy to remember so that you can find it quickly when you want to associate it with a GuardPoint. For example: LDT-Policy-West
   Policy Type	Select Live Data Transformation.
   Description	A user-defined description to help you identify the policy later. For example: LDT policy for the West Coast Datacenter.
   Learn Mode	Learn Mode provides a temporary method for disabling the blocking behavior of CTE/LDT policies. While useful for quality assurance, troubleshooting, and mitigating deployment risk, Learn Mode is not intended to be enabled permanently for a policy in production. This prevents the policy Deny rules from functioning as designed in the policy rule set. Ensure that the policy is properly configured for use in Learn Mode. Any Security Rule that contains a Deny effect must have Apply Key applied as well. This is to prevent data from being written in mixed states, resulting in the loss of access or data corruption. Apply Key will have no effect when combined with a Deny rule unless the policy is in Learn Mode.

7. Click Next.

8. On the Security Rules page, define the security rules you want to use.

   CipherTrust Manager automatically adds a default security access rule with an action of key_op and the effects Permit and Apply Key. This rule permits key operations on all resources, without denying user or application access to resources. This allows it to perform a rekey operation whenever the encryption key rotates to a new version. This rule is required by LDT, so you cannot edit it, move it, or delete it.

   1. If no process other than SQL Server’s process needs access to clear data, then after the default security rule, create a security rule with:

      Policy Element	Value
      Process set	path-to-sqlservr.exe
      Action	all_ops
      Effect	permit,apply key

      

      

   2. The SQL Server process set for the second security rule should specify the path to sqlservr.exe, and optionally, select to sign that process when adding that process to the process set.

   

   1. Create a third security rule:

      Rule	Value	Note
      Action	All_ops
      Effect	Permit, audit	Needed if each operation occurring on the database outside of sqlservr.exe will be audited.

9. On the Create Key Rule page, click Create Key Rule and enter the following information:

   Field	Description
   Resource Set	If you want to select a resource set for this key rule, click Select and either choose an existing resource set or create a new one. Resource sets let you specify which directories or files will either be encrypted with the key or will be excluded from encryption with this key.
   Current Key Name	Click Select to choose an existing key or create a new one. If the data has not yet been encrypted, select clear_key. Otherwise select the name of the non-versioned key that is currently being used to encrypt the data.
   Transformation Key Name	Click Select to choose an existing versioned key or to create a new one. CTE uses the versioned key specified in this field to encrypt the data in the GuardPoint. If the data is currently encrypted, CTE decrypts it using the key specified in the Current Key Name field and re-encrypts it using the key specified in this field.

   Note

   LDT now supports dynamic resource sets. This means that you can alter the resource set, and the policy will pick up the changed resource set and apply it. However, make sure that you do not mix encryption keys in your edited resource set. This will cause corruption.

   When you are done specifying key rules, click Next.

Create a GuardPoint

1. Navigate to and select the directory containing *.mdf, *.ndf and *.ldf database files to guard.

   

2. On the SQL Server, take the database offline so that CTE Agent can guard the database.

   

   When the CTE Etray on the agent turns green, that indicates that the directory is now guarded. You can now bring the database back online.

3. On the Confirmation page, review the information you entered and click Save when you are ready to save the policy.

Within a minute after the directory is guarded, either before or after the database is brought back online, LDT begins encrypting the files in the directory, including the database files (*.mdf, *.ndf and *.ldf), and any other files in that directory or subdirectory. This LDT encryption activity includes all of the files in the the FileStream subdirectories in that guarded parent directory.

As the LDT activity is transparent, the database can be brought back online before or after this activity occurs. CipherTrust Manager will periodically update with the LDT percentage progress:

Verify Successful Encryption

To verify that the FileStream files are encrypted on a disk, open a file outside of SQL Server, using notepad or any desired application. Issue an SQL query to verify if sqlservr.exe is reading the decrypted data:

Once LDT completes, the status on CipherTrust Manager for that GuardPoint changes to rekeyed.

Backup an SQL FileStream Database

To backup a FileStream database using SQL, use SQL normally, however, if the backup file target location is outside of the guarded database directory, then that backup target location should be guarded as well. When utilizing SQL to backup a database, since sqlservr.exe is the process that creates the backup file and performs all of the database backups, the backup is encrypted inline and on disk for those writes when writing to that guarded backup directory. The backup includes the FileStream files in that backup.

Restore an SQL FileStream Database

To restore a FileStream database, restore the database as usual from a backup that was created either before, or after, the database was guarded by a LDT policy. (This is possible because LDT stores file metadata on each file, indicating if it is encrypted or not.) This is not supported though when guarding with a standard policy since no file metadata is stored on files for a standard policy. For a standard policy, any backup being restored, from within a guarded directory, needs to have been created after that directory was guarded with that standard policy. Otherwise, any older, unencrypted clear backup file needs to be outside of the guarded directory.

Conclusion

As sqlservr.exe is the only process that reads the decrypted data, avoid mixing accesses to SQL Server database backups across guarded directories and non-guarded directories. For example: If you use SQL to backup a database to a guarded directory, then outside of SQL Server, use explorer to copy that backup from the guarded directory to a non-guarded directory. Then using SQL to restore that backup results in undecipherable data being returned to sqlservr.exe. No corruption would occur, as the sqlservr.exe process would simply recognize that the file is not in SQL Server backup format and thus immediately fail to restore it. Copying the file back into a GuardPoint and then restoring it it would resolve the issue.