locked
SQL 2014 EE TDE Encryption stuck in encryption_state = 2, percent_complete = 0 RRS feed

  • Question

  • All,

    We're stuck.  We're trying to encrypt databases using SQL Server 2014 Enterprise Edition.  We're migrating databases over from SQL 2012 Standard Edition to SQL 2014 Enterprise Edition in order to take advantage of the TDE offered by Enterprise Edition.  The version of SQL Server 2014 EE we're using is 12.0.4100.1 (X64).

    The encryption consistently gets stuck in encryption_state = 2, percent_complete = 0, even immediately after the encryption starts.  In the SQL logs, we're seeing:

    6/13/2016 3:08:49 PM spid59   Starting up database 'PartnerCache2015'
    6/13/2016 3:08:49 PM spid20s Beginning database encryption scan for database 'PartnerCache2015'
    6/13/2016 3:08:49 PM spid20s Database encryption scan for database 'PartnerCache2015' was aborted.  Reissue ALTER DB to resume the scan.

    Reissuing the ALTER DB command to set encryption on is not effective.  We're getting this message on literally every database we try to encrypt.  Because it's happening on every database, it leads me to believe that the databases are not corrupt, but something else is going on.

    We're using the same master key that we used in our development environment (using SQL Server 2014 Developer Edition), and it works well with one database we've used it on in our development environment, but another database doesn't.  A query of suspect_pages and a DBCC CHECKDB reveals nothing.

    We've tried the solutions outlined and linked to in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e9bcc0fe-f25c-4e07-a625-51fd74e9a74b/transparent-data-encryption-tde-stuck-in-state-2-encryption-in-progress?forum=sqlsecurity short of the profiler (I'm not a DBA, I'm an applications guy, so I'm not familiar with the SQL profiler).

    An EXEC sp_who2 reveals SPID 20 (status BACKGROUND) is running a TASK MANAGER command but is BlkBy 36.  SPID 30 (status BACKGROUND) is running a DB STARTUP but is BlkBy 36.  SPID 36 (status BACKGROUND) is running an ALTER DATABASE E but is BlkBy 59.  59 (status SUSPENDED) is running an ALTER DATABASE.

    Any help would be appreciated in getting these databases to encrypt!

    Thanks,
    Jerome Grimmer

    Monday, June 13, 2016 9:04 PM

All replies

  • What does DBCC INPUTBUFFER(59) report?

    Monday, June 13, 2016 9:16 PM
  • Hi Erland,

    DBCC INPUTBUFFER(59) reports "ALTER DATABASE [ProvidersIndex] SET ONLINE

    This was a database that had come up with recovery pending, so I'd opened the master key, then issued the ALTER DB command to bring it online, then closed the master key.  THE SQL for the entire sequence looked like this:

    OPEN MASTER KEY WITH DECRYPTION PASSWORD = 'MyStrongPassword'
    GO
    ALTER DATABASE [ProvidersIndex] SET ONLINE
    GO
    CLOSE MASTER KEY
    GO

    Monday, June 13, 2016 9:42 PM
  • Now this is interesting...  I took a backup of one of the unencrypted databases from our current production SQL server (SQL 2012 SE) and restored it to my local machine, where I had done the successful encryption (SQL 2014 Developer Edition (DE)).  It encrypted just fine.  We have a server here in the office that I also have SQL 2014 DE installed on.  When I tried the encryption there, it acted exactly like my future production server running SQL 2014 EE - that is, the database encryption scan for the database was aborted.  Reissuing the ALTER DB to turn the encryption on did no good - the same thing happened.

    Now, I created the master key and server certificate on my local machine, backed it up to my filesystem, and copied those files over to the other servers and restored them, so at least in theory, this should have worked.


    Here's the backup SQL I used, as near as I can remember (I didn't write all this down or save it, sorry):

    BACKUP MASTER KEY TO FILE = 'C:\wa\SQL2014MasterKey'
        ENCRYPTION BY PASSWORD = 'MyStrongPassword'
    BACKUP CERTIFICATE Sql2014ServerCert TO FILE = 'C:\wa\Sql2014ServerCert'
        WITH PRIVATE KEY (
            FILE = 'C:\wa\Sql2014ServerKey',
            ENCRYPTION BY PASSWORD = 'MyStrongPassword'
        )

    Here's the restore SQL I used (this I did save!):
    USE master
    GO
    RESTORE MASTER KEY FROM FILE = 'C:\wa\SQL2014MasterKey'
        DECRYPTION BY PASSWORD = 'password'
        ENCRYPTION BY PASSWORD = 'password'
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyStrongPassword'
    GO
    CREATE CERTIFICATE Sql2014ServerCert
    FROM FILE = 'C:\wa\SQL2014ServerCert'
    WITH PRIVATE KEY (FILE = 'C:\wa\SQL2014ServerKey',
           DECRYPTION BY PASSWORD = 'MyStrongPassword')
    GO
    CLOSE MASTER KEY
    GO

    Where did I go wrong that encryption works on my local environment but not my test server in the office or my soon-to-be-production environment?

    Tuesday, June 14, 2016 2:23 PM
  • OPEN MASTER KEY WITH DECRYPTION PASSWORD = 'MyStrongPassword'
    GO
    ALTER DATABASE [ProvidersIndex] SET ONLINE
    GO
    CLOSE MASTER KEY
    GO

    And this ALTER DATABASE command has completed by now? Do you still see blocking with sp_who? Is encryption still stuck and this was a false lead?

    Tuesday, June 14, 2016 8:18 PM
  • This may have been a false lead.  We ended up bouncing the service, dropping the databases and grabbing fresh new unencrypted copies from the production server.  Recreated a master key from scratch and a server certificate from scratch on the new server, and encrypting the database on the new server with keys/certificates created on the new server works. 

    I've backed up the master key, server certificate and private key from the NEW server to files using this SQL:
    USE master
    GO
    BACKUP MASTER KEY TO FILE = 'C:\wa\SQL2014MasterKey'
        ENCRYPTION BY PASSWORD = 'MyStrongPassword'
    BACKUP CERTIFICATE Sql2014ServerCert TO FILE = 'C:\wa\Sql2014ServerCert'
        WITH PRIVATE KEY (
            FILE = 'C:\wa\Sql2014ServerKey',
            ENCRYPTION BY PASSWORD = 'MyStrongPassword'
        )

    I then restore them using this SQL, on another (DEV) server (production is using SQL Server 2014 Enterprise Edition, other server is using SQL Server 2014 Developer Edition):
    USE master
    GO
    RESTORE MASTER KEY FROM FILE = 'C:\wa\SQL 2014 (SQL04) TDE Info\SQL2014MasterKey'
        DECRYPTION BY PASSWORD = 'MyStrongPassword'
        ENCRYPTION BY PASSWORD = 'MyStrongPassword'
    GO
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyStrongPassword'
    GO
    CREATE CERTIFICATE Sql2014ServerCert
    FROM FILE = 'C:\wa\SQL 2014 (SQL04) TDE Info\SQL2014ServerCert'
    WITH PRIVATE KEY (FILE = 'c:\wa\SQL 2014 (SQL04) TDE Info\SQL2014ServerKey',
        DECRYPTION BY PASSWORD = 'MyStrongPassword')
    GO
    CLOSE MASTER KEY
    GO

    SQL Server indicates that the backup and restores work correctly, and a restore of an encrypted database works on the DEV server and is accessible, yet I cannot get a database on the DEV server to encrypt an unencrypted database with the master key/certificate created on the NEW server using this SQL:
    USE [DBname]
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE Sql2014ServerCert
    GO
    ALTER DATABASE [DBname]
    SET ENCRYPTION ON
    GO

    I'm running SQL 2014 EE on the NEW server, and SQL 2014 DE on the DEV server.  Here's what shows up in the SQL logs (top-down, descending order by date):
    6/15/2016 10:15:43 AM spid29s Database encryption scan for database 'DBname' was aborted.  Reissue ALTER DB to resume the scan.
    6/15/2016 10:15:43 AM spid29s Beginning database encryption scan for database 'DBname'
    6/15/2016 10:15:43 AM spid65 Setting database option ENCRYPTION to ON for database 'DBname'

    Why does encrypting a database on the NEW server (where I created the key) work, but not on my DEV server (or any other server, for that matter) using the same master key and certificate??  Inquiring minds want to know!  This post contains everything I did, step-by-step.  Sorry so long-winded.


    Wednesday, June 15, 2016 3:41 PM
  • I'm afraid that I am out of ideas (but then again, I have never actually used TDE myself).
    In this new attempt on the DEV server, is there any blocking appearing?

    Wednesday, June 15, 2016 9:28 PM