question

Echo123DBA-6166 avatar image
0 Votes"
Echo123DBA-6166 asked Echo123DBA-6166 commented

EFS encrypt/decrypt SQL Server database files online

I have some machines which have OS build 16299 and have SQL Server installed and mdf/ldf are encrypted with EFS. I found the mdf/ldf files could not be decrypted when the database is online. While for the machine which have OS build above16299, the mdf/ldf files could be decrypted directly when database is online.

Which means for the OS build 16299, I have to set the database offline to decrypt the database files, if the database size is huge, it will take a very long down time.
I though it's due to the OS build issue, so I upgraded the OS build to same version with the OS build which could decrypt the database files online. While even the OS has been upgraded it didn't change the EFS decrypt behavior, I still could not decrypt the files when the database is online.

It seems for the old OS build, the EFS will create a file named EFS0.TMP which is same with the database file when do the encryption/decryption. While for the new OS build in my case, the EFS will not create any file named EFS*.TMP. I could not understand what caused the different behavior of the EFS. If anyone could help to answer this question?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Echo123DBA-6166 commented

Hi @Echo123DBA-6166,

OS 16299 is an old version and it is out of support now. What is your SQL Server version? SQL Server 2008 introduced Transparent Data Encryption (TDE); a mechanism allowing the DBA to easily encrypt databases without affecting their operation as it allows full transparency to the database users and applications. Suggest you using TDE for encrypt SQL Server. It can encrypt and decrypt the database that is online.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We are using SQL Server 2017. I understand TDE is introduced since SQL Server 2008, while it's only supported in SQL Server enterprise edition and we are using standard SQL Server license.

We have multiple SQL instances are using EFS to encrypted the database files in previous time. And even we have plan to move to use TDE , we still need to decrypt it from file level.

Currently, I found these servers which were in OS 16299 could not decrypt the files online even we upgrade the OS build to new version.

0 Votes 0 ·