Knowledge Base

KB #240118: Encrypting FileStreams (Whole Database Encryption)

Type:

Information

Summary:

Instructions on how to configure Encryptionizer to work with SQL Server FileStreams. (Encryptionizer Driver versions 2010.201.10 and later)

Additional Information:

FileStreams are essentially varbinary(max) fields that are stored external to the database in special linked files. The filenames are internal to SQL and do not in any way represent the name or type of the original file.

FileStream data must be encrypted with the AES-CTR algorithm, even if the database they are linked to uses a different algorithm.

Procedure:Locate the FileStream Data Directory. It usually will have a file FILESTREAMS.HDR. The actual data is stored in typeless files in one or more subdirectories. For example, assume the FileStream Data for database NorthWind is in C:\FileStream\NorthWind.

If desired, but not required, initially encrypt the FileStream data for the first time. You can use the Encrypt/Decrypt Wizard GUI. However, you might find it easier/faster to use the SECTOOL.EXE CLI (Command Line Interface). The CLI has the advantage of recursing subdirectories. Encrypt all typeless files (*), e.g.,

REM Encrypt all typeless files in directory and subdirectories
SECTOOL.EXE /e /. /1:9:32:filestreampassword /s "c:\FileStream\NorthWind\*" "<"

Secure the instance of SQL Server using the Administration Wizard User Interface:

• Add your database key(s) on the Enable Encryptionizer screen. It is recommended that you use AES-CBC or AES-ECB for your databases and backups. Add one additional key using the AES-CTR algorithm and note the Key number assigned in the list. This last key (Key N) will be assigned later for use with the Filestream files.
• Set the option to “Encrypt New Databases and Backups” on the Additional Options screen
• Choose the “Specify File Names to include/exclude” feature ad click the “Specify..” button.
• Enter the FileStream directory and * mask into the File Mask field (e.g. *\FileStream\Northwind\*). Choose Key N (from the step above) to be used with the FileStream directory extension, and click Include. This option will ensure that all newly created Filestream files created in the specified Filestream directory will be encrypted with Key N.
• Exclude FileStream HDR file
  Enter *.HDR into the File Mask field, and select “Exclude”
• If you need to also encrypt newly created databases and backups, see the Whole Database User Guide for more detailed instructions.
• Complete securing the SQL instance.

Alternatively, you can choose to use the BLDCMD.EXE CLI (command line interface) to secure the SQL instance. For example, if you have used AES-CBC or AES-ECB algorithm for the databases (recommended) and AES-CTR for the filestreams, you need to specify both keys and specify the FileStreams directory in the File Mask Rules String, but exclude the FileStream HDR file. For example,

BLDCMD.EXE /1:8:32:databasepassword /2:9:32:filestreamspassword /FPC /v"+*\FileStream\Northwind\*#2;-*.hdr;" /k /S MSSQLSERVER

Note that in the Rules String the drive letter is replaced with “*” in the FileMask.

Finally restart SQL Server. Any new or updated FileStream data is automatically encrypted.

Last modified: 3/5/2024