T-SQL Tuesday #004: Why Doesn't TDE Encrypt My FILESTREAM Data?

Mike C — Tue, 09 Mar 2010 02:42:00 GMT

This post is my entry for Adam Machanic's T-SQL Tuesday #004, hosted this time by Mike Walsh. I was at the RSA Conference in San Francisco last week discussing database encryption options in SQL Server 2008 and one question seemed to keep coming up. The question concerns FILESTREAM and Transparent Data Encryption (TDE), but first a little background:

FILESTREAM is a new SQL Server 2008 feature. When you apply the FILESTREAM attribute to a varbinary(max) column SQL Server stores your BLOB data in a "FILESTREAM data container" (an NTFS directory structure) instead of directly in the database (the MDF and NDF files that normally hold all your data). The advantages of FILESTREAM are speed (streaming NTFS access for large files) and the ability to store BLOB data larger than 2.1 GB.

One of the downsides of FILESTREAM concerns another new feature, TDE. TDE transparently encrypts your database, adding a layer of protection against physical theft of your database files and storage devices. But TDE does not encrypt FILESTREAM data. The question I kept getting was "why?" The answer is fairly simple -- but a picture's worth a thousand words:

As you can see in the picture, TDE sits midway between your physical storage and SQL Server's IO buffers. This ensures that everything that passes through the IO buffers gets encrypted on its way to persistent storage and decrypted on its way back out of storage. This is also why it's "transparent" to your applications, developers and users. SQL Server reads and writes data through the IO buffers in 8 KB pages. FILESTREAM achieves much of its performance enhancement for BLOB data by simply bypassing the IO buffers. But since TDE acts only on data passing through the IO buffers, it misses FILESTREAM BLOB data completely.

Fortunately there are plenty of other options for encrypting your FILESTREAM data -- you can use Windows Encrypting File System (EFS), BitLocker or third-party file/folder/volume encrypting software, for instance.

Search results matching tags 'SQL Server', 'encryption', 'SQL 2008', and 'filestream'

T-SQL Tuesday #004: Why Doesn't TDE Encrypt My FILESTREAM Data?