Sql Server - Error attaching mdf file encrypted via Encrypted File System (EFS)

I am getting an error trying to attach a database whose files were previously encrypted via EFS. The actuall error message is

Msg 5120, Level 16, State 101, Line 9
Unable to open the physical file "C:\test.mdf". Operating system error 5: "5(Access is denied.)".

If I decrypt the files, then I successfully can attach the database. I am using the same local user account that runs the sqlserver service. Any ideas? (I had previously asked this q in stackoverflow, but I got comments that it belongs here)

From serverfault Jangwenyi

• Are you sure that the service account your using for SQL Server matches the user account your using to encrypt the files? EFS is handled at the OS and is transparent to SQL Server.

  If this is the case, then check for general NTFS permissions, make sure that the service account has full control of the files your trying to attach. May be stating the obvious, but you should always check that there's gas in the tank, don't assume!

  If your using SQL Server 2008 Enterprise, then you should investigate using Transparent Data Encryption (TDE) instead of EFS, there's better performance and its nicer to manage.

  Jangwenyi : Yes I can confirm I am using the same user account. It is worth mentioning that I am using sql express 2005 on Win2000 Professionalsp4

  Nick Kavadias : are you attaching the database as sa? log into SSMS with the service account or as sa & try the attach

  Jangwenyi : For the benefit of the community, I found out the cause of this issue as follows: 1. Detaching/Attaching the database uses the currently logged on user. 2. Encrypting/Decrypting db files uses the sql server service account 3. So if the currently logged on user is a different account to that of the sql server service, clearly there will be an error trying to attach the database because the files were encrypted via account (EFS works like that) 4. To resolve, use the same account to encrypt and attach
  From Nick Kavadias

• Check the NTFS permissions (as Nick said) and ensure that the SQL Server has access to the files using the account that the service is running under, as well as the account you are using to connect to the SQL Server.

  When you detach a database the SQL Server automatically removes the rights to the files from everyone except the person who detached the database.

  Nick Kavadias : +1 for agreeing with me ;-)
  From mrdenny

• Don't use EFS with SQL Server. If you must use an OS level encryption, then use BitLocker. Otherwise, use SQL Server's own TDE. EFS with SQL Server performs horibly.

  From Remus Rusanu

• EFS also doesn't do a real encryption of the file when the file is in use. It will only encrypt the physical data after the file stream is closed which means you have to shut down SQL server to encrypt the file. The file is decrypted when it is access and is stored in both the system cache, and memory unencrypted. Just something to keep in mind.

  It's sounds to me that it's more of a permission problem and not related to EFS. Have you tried moving the file to the SQL Server data directory folder and see if it can be accessed on the server there? We use that alot when we are testing permission issues. If the server can attach it from there we know the original locations has a permission problem.

  From Phillip