SQL Server 2008 Transparent Data Encryption getting started http://www.mssqltips.com/tip.asp?tip=1514 请结帐，谢谢
======================Problem While reviewing the new features in SQL Server 2008, we noticed Transparent Data Encryption. This sounds very interesting. Could you provide us with an explanation and the details to implement it?Solution TDE is a new feature in SQL Server 2008; it provides real time encryption of data and log files. Data is encrypted before it is written to disk; data is decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption. There are a couple of steps to be performed to prepare the database for TDE, then the encryption is turned on at the database level via an ALTER DATBASE command.We've probably all heard of incidents where backup tapes containing sensitive information have been lost or stolen. With TDE the backup files are also encrypted when using just the standard BACKUP command once encryption is turned on for the database. The data in the backup files (or on disk) is completely useless without also having access to the key that was used to encrypt the data.Before we dive in to the steps to implement TDE, let's take a minute to discuss encryption at a very high level. The Wikipedia definition of encryption is "the process of transforming information (referred to as plaintext) using an algorithm (called cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key". To encrypt some data, I choose an available algorithm, supply a key and I now have encrypted data. To decrypt the encrypted data, I choose the same algorithm and supply the key. The security provided by encryption is based on the strength of the algorithm and protection of the key. There are two types of keys - symmetric and asymmetric. With a symmetric key, the same value is used to encrypt and decrypt the data. An asymmetric key has two components - a private key and a public key. I use the private to encrypt data and someone else must use the public key to decrypt the data. To recap, the symmetric key or private key of the asymmetric key pair must be stored securely in order for encryption to be effective.Now let's walk through an example of how to implement TDE. Books on Line lists the following four steps to implement TDE on a particular database: * Create a master key * Create or obtain a certificate protected by the master key * Create a database encryption key and protect it by the certificate * Set the database to use encryptionCreate a Master KeyA master key is a symmetric key that is used to create certificates and asymmetric keys. Execute the following script to create a master key:USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word1'; GONote that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters) and you have to backup (use BACKUP MASTER KEY) and store it in a secure location. For additional details on master keys refer to our earlier tip Managing SQL Server 2005 Master Keys for Encryption.Create a CertificateCertificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate:USE master; CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate' GONote that certificates also need to be backed up (use BACKUP CERTIFICATE) and stored in a secure location. For additional details on certificates, refer to our earlier tip SQL Server 2005 Encryption - Certificates 101.Create a Database Encryption KeyA database encryption key is required for TDE. Execute the following script to create a new database and a database encryption key for it:CREATE DATABASE mssqltips_tde GO USE mssqltips_tde; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert GOIn order to work with TDE the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database. Enable TDEThe final step required to implement TDE is to execute the following script:ALTER DATABASE mssqltips_tde SET ENCRYPTION ON GO SELECT [name], is_encrypted FROM sys.databases GOYou can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.================
http://www.mssqltips.com/tip.asp?tip=1514
请结帐，谢谢
While reviewing the new features in SQL Server 2008, we noticed Transparent Data Encryption. This sounds very interesting. Could you provide us with an explanation and the details to implement it?Solution
TDE is a new feature in SQL Server 2008; it provides real time encryption of data and log files. Data is encrypted before it is written to disk; data is decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption. There are a couple of steps to be performed to prepare the database for TDE, then the encryption is turned on at the database level via an ALTER DATBASE command.We've probably all heard of incidents where backup tapes containing sensitive information have been lost or stolen. With TDE the backup files are also encrypted when using just the standard BACKUP command once encryption is turned on for the database. The data in the backup files (or on disk) is completely useless without also having access to the key that was used to encrypt the data.Before we dive in to the steps to implement TDE, let's take a minute to discuss encryption at a very high level. The Wikipedia definition of encryption is "the process of transforming information (referred to as plaintext) using an algorithm (called cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key". To encrypt some data, I choose an available algorithm, supply a key and I now have encrypted data. To decrypt the encrypted data, I choose the same algorithm and supply the key. The security provided by encryption is based on the strength of the algorithm and protection of the key. There are two types of keys - symmetric and asymmetric. With a symmetric key, the same value is used to encrypt and decrypt the data. An asymmetric key has two components - a private key and a public key. I use the private to encrypt data and someone else must use the public key to decrypt the data. To recap, the symmetric key or private key of the asymmetric key pair must be stored securely in order for encryption to be effective.Now let's walk through an example of how to implement TDE. Books on Line lists the following four steps to implement TDE on a particular database: * Create a master key
* Create or obtain a certificate protected by the master key
* Create a database encryption key and protect it by the certificate
* Set the database to use encryptionCreate a Master KeyA master key is a symmetric key that is used to create certificates and asymmetric keys. Execute the following script to create a master key:USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GONote that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters) and you have to backup (use BACKUP MASTER KEY) and store it in a secure location. For additional details on master keys refer to our earlier tip Managing SQL Server 2005 Master Keys for Encryption.Create a CertificateCertificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate:USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GONote that certificates also need to be backed up (use BACKUP CERTIFICATE) and stored in a secure location. For additional details on certificates, refer to our earlier tip SQL Server 2005 Encryption - Certificates 101.Create a Database Encryption KeyA database encryption key is required for TDE. Execute the following script to create a new database and a database encryption key for it:CREATE DATABASE mssqltips_tde
GO
USE mssqltips_tde;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GOIn order to work with TDE the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database. Enable TDEThe final step required to implement TDE is to execute the following script:ALTER DATABASE mssqltips_tde
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GOYou can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.================
http://technet.microsoft.com/zh-cn/dd793599.aspx