SQLSERVER2012 Transparent Encryption (TDE) issues

Recently need to use the encryption function, found the TDE, generate a certificate only to find that the SQL version installed is not Enterprise Edition, it seems that only SQL2008 more than the Enterprise version is supported, can upgrade it, try the next automatic upgrade, OK prompt successful, and then install still prompt version not select @ @version Only to discover that the version has not changed.

Reinstall it, find the Control Panel uninstall 2012, Strange unexpectedly unloaded. I have the SQL set up to unload the database can still be used, strange, is the reason for the upgrade?

Redo the system.

Waiting .....

The system is ready to install SQL2012 Enterprise Edition first

OK This can generate a certificate, encryption also successful, the following is the encryption process

 UseMaster;GO--To see if the master database is encrypted--SELECT name,is_master_key_encrypted_by_server from sys.databases;--1 means encryption--Each database can have only one database master key--Delete the old key certificateDropCERTIFICATE MyservercertDropMasterKey GO--Create a master key in the master databaseCREATEMASTERKEYEncryption byPASSWORD= '[email protected]';--to view key information under the master database--SELECT * from Sys.symmetric_keys--Create a certificate to protect the database encryption key (DEK)CREATECERTIFICATE Myservercert withSUBJECT= 'My DEK Certificate';--IF db_id (' Tdedemo ') is not NULL--DROP DATABASE TdedemoGo UseDemo1GO --Create a database encryption key and use Myservercert to encrypt the certificate--Drop DATABASE Encryption KEYCREATE DATABASEEncryptionKEY withAlgorithm=aes_256encryption bySERVER CERTIFICATE MyservercertGo--after executing the above statement appears:/*warning:the Certificate used for encrypting the database encryption key have not been backed up. You should immediately back up the certificate and the private key associated with the certificate.  If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must has Backups of both the certificate and the private key or you are not being able to open the database.*//*Warning: The certificate used to encrypt the database encryption key has not been backed up. The certificate and the private key associated with the certificate should be backed up immediately. If the certificate is not available, or you must restore or attach the database on another server, you must back up the certificate and private key, or you will not be able to open the database. */--you are prompted to back up the certificate at once; The backup certificate here is no more than a symmetric key for encrypting the private key. Because his key is encrypted through the master database key of the master database.--Accordingly, we back up the database master key (master) UseMaster;GO--If you do not have the automatic decryption feature enabled for the master key--OPEN MASTER KEY decryption by PASSWORD = ' [email protected] ';BACKUPMASTERKEY  to FILE='D:\Cers\master_key'Encryption byPASSWORD=N'[email protected]'--back up the primary database key DMKBACKUPCERTIFICATE Myservercert to FILE = 'D:\Cers\master_cer' --backing up a database certificate     withPRIVATEKEY (     FILE = 'D:\Cers\master_cert.pvk',--automatically generate PVKEncryption byPASSWORD= '[email protected]' );GO --in a production environment, set up a single user to run the encryption--ALTER DATABASE dbname SET single_user with ROLLBACK IMMEDIATE; UseMasterGOBACKUP DATABASEDemo1 to DISK=N'D:\demo1.bak' /*test OFF on sometimes prompts for failure message 33122, Level 16, State 1, line 1th This command requires database encryption scanning on database ' Tdedemo '. However, the database has changed since the encryption scan of the last pending log backup. Please extract a log backup and retry the command. Message 5069, Level 16, State 1, Row 1th, alter DATABASE statement failed. */--See if the database is encrypted Encryption_state:3 TDE encryptedSELECT db_name(database_id) asDbname,encryption_state fromSys.dm_database_encryption_keys;--after the backup is successful, TDE encryption is turned onALTER DATABASEDemo1SETEncryption on--Turn off TDE encryptionALTER DATABASEDemo1SETEncryptionOFF;--set up multi-user access--ALTER DATABASE dbname SET multi_user with ROLLBACK IMMEDIATE;GO

The strange thing is that after I open tde on OFF a few times, I get the following error, I can't turn tde on or off after the error occurs, and I don't know why.

33122  - 2 1 5069  - 1 1 Row alter DATABASE statement failed.

After encryption is the problem of restore, if the server has a problem to restore to other machines are often used, tested to restore to non-Enterprise Edition is not normal use, that is, Enterprise Edition to support the database is protected by TDE (do not know the right), the following is the restore code

 UseMaster;Go--To see if the master database is encrypted--SELECT name,is_master_key_encrypted_by_server from sys.databases;--1 means encryption--GO--If there is a primary database key removed under the master database--DROP MASTER KEY;/**--Online has said that the method to recover the master key but tested the method does not properly recover the master key does not know if my version issue test version SQL2012 Enterprise Edition restore master key from FILE = ' D:\Cers\master_key ' DE Cryption by PASSWORD = ' [e-mail protected] ' encryption by PASSWORD = ' [email protected] '; **/CREATEMASTERKEYEncryption byPASSWORD= '[email protected]';--creating a master key can be different from what you want to recoverGO--Create a certificate--Drop CERTIFICATE MyservercertCREATECERTIFICATE Myservercert from FILE = 'D:\Cers\master_cer'      withPRIVATEKEY(FILE = 'D:\Cers\master_cert.pvk', decryption byPASSWORD= '[email protected]');--same as the key to recover pwd other files to keep path consistentGO ---Database restore Operations--If the database logical file name cannot be determined, you can use the following statement to view the--restore filelistonly from disk=n ' D:\Cers\Demo1.bak '--if the physical path is inconsistent, you can use the Move statement to modifyRESTORE DATABASEDemo from DISK=N'D:\Cers\Demo1.bak' withMove'Demo1'      to 'D:\data\demo.mdf', Move'Demo1_log'  to 'D:\data\demo.ldf'GO

Revert to Business Edition tips below

Have the solution, welcome message Thank you

SQLSERVER2012 Transparent Encryption (TDE) issues