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

Question

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 domain user who did the encryption. Any ideas?

Answer 1

I am using the same domain user who did the encryption.

The SQL Server Service account will need to be able to access and decrypt the file as well (all file operations by the service are done under the account it runs at, whatever account is being used by clients).

Answer 2

Hi Jangwenyi check again ,make sure that the startup account of sql server is the same as the account that encrypted the db. Bye.

Answer 3

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