Sql server 2017 TDE with external EKM

Goh Chee Hong 1 Reputation point
2020-12-22T09:15:25.067+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,601 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,601 Reputation points
    2020-12-23T02:38:38.047+00:00

    Hi @Goh Chee Hong ,

    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

    0 comments
  2. Goh Chee Hong 1 Reputation point
    2020-12-23T09:24:59.417+00:00

    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