sql-serverencryptionfull-text-searchfilestreamfiletable

SQL Server 2012 Full Text Search on FILESTREAMS with Windows Encrypting File System (EFS)


This is basically a yes/no question, but it is appreciated if the answer includes supporting references and a how-to if the answer is "yes". Strangely, I couldn't find a definitive answer in MSDN or TechNet, and my instincts and experiments lead me to a "no" conclusion.

Is it possible to use Windows EFS with SQL Server 2012's FILESTREAMS and FileTables and have Full Text Search work on those FILESTREAMS?

TIA

Additional Detail

I have a Visual Studio SQL Project created that stamps out DBs with FILESTREAMS on FileTables, with Full Text Search working, so I know the baseline is good. From there I tried 2 things.

  1. Browse to the physical FileTable folders and encrypt them with EFS. This is probably the wrong approach, since I'm adding encryption after creating the DB, Log and FILESTREAM. The SQL error is "Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" Disabling encryption on the physical folder puts things to rights.

  2. Encrypt the physical target folders first, then deploy the SQL project (so the .MDF and .LDF are also encrypted on disk). This seems to work better, but when I browse the UNC path (\MyMachine\MSSQLSERVER\NewDb\Files) and copy a file there, I get an error from Windows: "Destination Folder Access Denied. You need permission to perform this action." So search might work, but I can't put files in the folder.


Solution

  • The answer is "yes" you can encrypt FILESTREAMS with Full Text Search. Details are in the comments.