question

shin-8484 avatar image
0 Votes"
shin-8484 asked Sathdk-2905 published

Sql server 2017 TDE with external EKM

Hi,

I have implemented SQL Server TDE 2017 with an external hardware security module

We faced a intermittent issue. We kept encountered an error has occurred during decryption. we have to restart sql server services and it solved the issue

Does SQL Server access the HSM to retrieve the asymmetric key to decrypt the database encryption key when it first start up SQL Server service (one time retrieval) and store the decrypted database encryption key into a secure area in memory? or SQL Server service has to keep decrypt the database encryption key frequently?

thank you

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.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

Hi @shin-8484,

When you start a SQL Server instance, the SQL Server database calls the EKM Provider software to decrypt the database symmetric key so that it can be used for encryption and decryption operations. The decrypted database key is stored in protected memory space and used by the database. The encrypted version of the database key remains on disk. In the event the system terminates abnormally, the only version of the database key is the encrypted version on disk.
More details refer to following posts.
https://www.sqlservercentral.com/articles/transparent-data-encryption-and-extensible-key-management-better-together
or
https://dba.stackexchange.com/questions/218137/tde-using-ekm-device

Here is a official document about Enable TDE on SQL Server Using EKM,please refer to this doc to check if there are any omissions in your TDE creation process.

In addition it is recommended to apply the latest SP and CU updates to your SQL Server 2017 instance to avoid any potential issues that have been fixed in the update.


If the answer is helpful, please click "Accept Answer" and upvote it.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November

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.

shin-8484 avatar image
0 Votes"
shin-8484 answered Sathdk-2905 published

thank you for your reply.

We followed every steps in Enable TDE on SQL Server Using EKM,

we did a test. The moment we disable the network to HSM, we encountered the following error

A sample of the errors we encountered in SQL Server log
" 2019-10-29 1 An error occurred during decryption.
2019-10-29 1 Error: 9001, Severity: 21, State: 4.
2019-10-29 1 The log for database 'XXXXX' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database."

some of our databases became recovery pending

why it still needs asymmetric key to decrypt the database encryption key since a decrypted copy is keep inside protected memory


· 3
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.

Hi,
Not sure if this depends on the configuration of encryption and key management options of different EKM Providers.

0 Votes 0 ·

Did you get any further information or clues on this?
I think we just had a similar situation - but our key management is Azure (off-premises). I believe that SQL Server contacts the KMS when extending a logfile, and (at an unfortunate moment) we did not have connectivity to Azure.

0 Votes 0 ·

DId you get any further update under this? Had same issue and looking for assistance.

0 Votes 0 ·