locked
Corrupted database encryption key (TDE) RRS feed

  • Question

  • I am doing some testing of utilizing our EKM for encrypting our databases with TDE, but I am running into a strange error, where the database encryption key is corrupted when it is created.  We are currently using Safenet on SQL Server 2014 Enterprise.  Here are all of the steps that were performed as recommended by their documentation:

    CREATE CRYPTOGRAPHIC PROVIDER safenetSQLEKM FROM FILE = 'C:\SQLEKM\safenetsqlekm.dll';
    CREATE CREDENTIAL TestEKMCred WITH IDENTITY = 'TestEKM', SECRET = '<password>' FOR CRYPTOGRAPHIC PROVIDER safenetSQLEKM;
    ALTER LOGIN SA ADD CREDENTIAL TestEKMCred;
    CREATE ASYMMETRIC KEY EKMLoginKey FROM PROVIDER safenetSQLEKM WITH PROVIDER_KEY_NAME = 'TestEKMAKey', CREATION_DISPOSITION = OPEN_EXISTING;
    CREATE CREDENTIAL TestEKMTDECred WITH IDENTITY = 'TestEKM', SECRET = '<password>' FOR CRYPTOGRAPHIC PROVIDER safenetSQLEKM;
    CREATE LOGIN SafeNetEKM FROM ASYMMETRIC KEY EKMLoginKey;
    ALTER LOGIN ADD CREDENTIAL TestEKMTDECred;
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
    CREATE DATABASE TestEKM;
    USE TestEKM;
    CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER ASYMMETRIC KEY EKMLoginKey;


    Although I do not receive any error output or see any errors in the event log, in Profiler I see the following error generated:

    Database encryption key is corrupted and cannot be read.

    If I attempt to enable encryption on the database, it will set the state to Encrypting, with a progress of 0% forever.

    Has anyone seen this kind of error, or used Safenet for TDE?  Or any EKM for that matter - I am not finding a lot of online resources for this.

    Thanks!

    Brandon


    Tuesday, August 2, 2016 1:18 PM

All replies

  • I've seen this article before.  It is not relevant to the issues I am experiencing.

    Thanks.

    Tuesday, August 2, 2016 3:48 PM
  • Hi Brandon L,

    According to BOL, the steps you have performed seems fine, and since you could create asymmetric key from provider I would assume your EKM provider is working properly. To confirm that, I would suggest you test EKM provider and TDE individually by using the following steps:

    1. For TDE testing, I would suggest you perform the steps described in BOL see if it’s working without EKM provider.

    2. For EKM provider testing, I would suggest you perform a column encryption test as described in BOL. Instead of using a certificate to encrypt the symmetric key used in the test, you could use the asymmetric key(EKMLoginKey, in your case) to encrypt the symmetric key as database encryption key is also a symmetric key.

    In addition, I would suggest you post your SQL Server error log here so we can have a better understanding about the issue. I also found an article indicates that the issue could related to corrupted database so I would suggest you perform the test again and create the database on another volume if possible.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Wednesday, August 3, 2016 5:30 AM
  • Hi Brandon L,

    I’m writing to follow up with you on this post. Was the issue resolved? If you issue has resolved, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Monday, August 15, 2016 1:58 AM
  • Lin,

    Sorry for not responding sooner, but I've been on vacation for the past few weeks.  I went ahead and tried what you suggested in steps 1 & 2, and had no issues.  The database encrypts using a certificate, and had no issues encrypting columnar data using the asymmetric key.

    I've seen the article before and have already tested for database corruption, and attempted to simply reapply database encryption.  

    Below is the error log after two attempts to encrypt the database using EKM.  As you can see, no error is generated, just a notification that the encryption was aborted.  The error I reported above is only ever seen while profiling the database at the time encryption is enabled.

    2016-08-30 08:08:03.800,spid51,Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64) 
    Oct 10 2015 17:18:26 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    2016-08-30 08:08:03.800,spid51,UTC adjustment: -5:00
    2016-08-30 08:08:03.800,spid51,(c) Microsoft Corporation.
    2016-08-30 08:08:03.800,spid51,All rights reserved.
    2016-08-30 08:08:03.800,spid51,Server process ID is 1264.
    2016-08-30 08:08:03.800,spid51,System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
    2016-08-30 08:08:03.800,spid51,Authentication mode is WINDOWS-ONLY.
    2016-08-30 08:08:03.800,spid51,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2016-08-30 08:08:03.800,spid51,The service account is '[REMOVED]\svc_sql_db'. This is an informational message; no user action is required.
    2016-08-30 08:08:03.800,spid51,Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
    2016-08-30 08:08:03.800,spid51,The error log has been reinitialized. See the previous log for older entries.
    2016-08-30 08:37:53.070,spid59,Setting database option ENCRYPTION to ON for database 'TestEKM'.
    2016-08-30 08:37:53.080,spid32s,Beginning database encryption scan for database 'TestEKM'.
    2016-08-30 08:37:53.100,spid32s,Database encryption scan for database 'TestEKM' was aborted. Reissue ALTER DB to resume the scan.
    2016-08-30 08:38:31.480,spid59,Setting database option ENCRYPTION to ON for database 'TestEKM'.
    2016-08-30 08:38:31.480,spid33s,Beginning database encryption scan for database 'TestEKM'.
    2016-08-30 08:38:31.480,spid33s,Database encryption scan for database 'TestEKM' was aborted. Reissue ALTER DB to resume the scan.

    Tuesday, August 30, 2016 1:52 PM
  • Hey Brandon,

    I have not faced this issue but can you drop all the master keys and certificates and recreate it again and then try encrypting the database.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, August 30, 2016 6:24 PM
  • I drop and re-create the test database and then create a new master key each time I test this.
    Thursday, September 1, 2016 12:54 PM
  • Hi Brandon L,

    Have you tried DBCC CHECKDB WITH NO_INFOMSGS see if it reports any error? Since you re-create your testdb all the time, I wonder if it’s possible that something is wrong with your storage device so your database was corrupted when you create it. I would suggest you create your testdb on another disk/volume see if it helps.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Monday, September 5, 2016 9:40 AM
  • what was you were getting when executed this query-
    
    select * from sys.dm_database_encryption_keys
    
    -- what it shows for your db in encryption_state column.
    
    --Also if its not in progresses then did you tried-
    
    
    
    ALTER DATABASE <namehere> SET ENCRYPTION ON
    GO
    
    
    Also do you see anything blocking (or) holding the locks.



    Regards, S_NO "_"

    Tuesday, September 6, 2016 5:28 AM