Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P41: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 182 Chapter 5 Managing Data Encryption Configuring Implementing. Effect on Backups Once a SQL database is encrypted via TDE all the backups are also encrypted. That s the easy part. Backups and secure storage of the SMK certificates and DMKs are critical. If these keys are lost the backups are not recoverable. It is recommended best practice to back up the keys and certificates in SQL Server. As part of the database backup process store the certificate and private key backup files in a physically safe location but separate from the database backup files. Backing up these keys to a secure location is critical if the keys fall into the wrong hands they could be used to access your data. If it s worth encrypting it s important to keep the keys safe. The following example illustrates the SQL command to back up the DEK and certificate. BACKUP CERTIFICATE TESTDB_TDE TO FILE SAFE_LOCATION TESTDB_TDE.2009.01.01.cer WITH PRIVATE KEY FILE SAFE_LOCATION TESTDB_TDE.2009.01.01.pvk ENCRYPTION BY PASSWORD TESTDB TD3 P@sswurd Restoring Encrypted Backups to Another Server or Instance If you attempt to restore an encrypted backup to a server that doesn t have the correct server certificate you ll get the following error Msg 33111 Level 16 State 3 Line 1 Cannot find server certificate with thumbprint 0xCA741797B81ED8D1305EAFF5A747BA51E1DAB80D . Msg 3013 Level 16 State 1 Line 1 RESTORE DATABASE is terminating abnormally On the new server you need to create the server certificate from the backup of the server certificate from the server where the database originated from. The syntax for restoring is as follows Managing Data Encryption Chapter 5 183 USE master GO create database master key if it doesn t exist CREATE MASTER KEY ENCRYPTION BY PASSWORD sleepyguyjn adsg12345 GO --create TDE certificate from backup CREATE CERTIFICATE Certl FROM FILE c Cert WITH PRIVATE KEY FILE c MyCertPrivKey DECRYPTION BY PASSWORD this is a ecret GO Once the key has been restored you may restore the .