2

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)

4 Answers 4

3

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.

3
  • 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
    – Julius A
    Aug 26, 2009 at 11:27
  • are you attaching the database as sa? log into SSMS with the service account or as sa & try the attach Aug 26, 2009 at 11:33
  • 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
    – Julius A
    Aug 29, 2009 at 9:26
3

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.

1
  • +1 for agreeing with me ;-) Aug 26, 2009 at 14:03
1

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.

1
  • What about using TDE and then EFS to encrypt just master.mdf/ldf files to keep the master keys safe in case the hardware is stolen?
    – Brain2000
    Aug 12, 2022 at 15:41
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .