3

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.

Todd Sprang
  • 2,899
  • 2
  • 23
  • 40
  • Todd, could you say how it does not work exactly? Is there any error or exception? To be sure that there is no mistakes in creating or operation on a filestream. – cassandrad Jan 22 '16 at 15:30
  • Also, I have not found any evidences that Sql Server will not work with EFS or that filestream in particular case. And, according to [this](https://technet.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx#_Toc189384681) it should work. And, maybe I already provided answer to you: EFS will not encrypt filestreams itself. – cassandrad Jan 22 '16 at 15:32
  • Expanded in the question. Pls see additional detail. – Todd Sprang Jan 22 '16 at 15:59
  • For the second issue, by which user have you created EFS and by which user you are trying to add files? I mean, are you sure that they are same? – cassandrad Jan 22 '16 at 16:02
  • The only user accounts would be local SQL and my domain account, and all this is on my Windows 10 dev machine. I enabled EFS using my account. When I browse via UNC path, I'm also using my account. I will test: if I do a SQL INSERT instead of copying by UNC path, does it work? – Todd Sprang Jan 22 '16 at 16:13
  • As far as I know (maybe it's wrong), EFS would work only with account that created it. Maybe there is a way to grant permissions to other accounts. Maybe if you could place a file in which a database placed to the EFS and then perform import in that database successfully, and if you can upload a file via SQL statement, then everything is fine with permissions. – cassandrad Jan 22 '16 at 16:29
  • What you say about EFS is correct I believe. BitLocker is supposed to be an improvement, working at the volume level, not user. I ran SQL to INSERT INTO my filetable, and that worked fine with EFS! I can even browse the files via UNC path, but I can't write to or copy from (freaky!). So it sounds like it is permissions-related with the virtual shares. Maybe BitLocker is the answer.. – Todd Sprang Jan 22 '16 at 16:39

1 Answers1

0

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

Todd Sprang
  • 2,899
  • 2
  • 23
  • 40