854
Part VI: Securing Your SQL Server
Transparent Data Encryption (TDE) is a special kind of encryption using a symmetric key.
This type encrypts the entire database using a symmetric key, which is called a database
encryption key. Other keys protect the database encryption key or certifi cates, which are
a protected database master key or an asymmetric key stored in an extensive key manage-
ment module. TDE protects the data at rest, meaning the data and log fi les. It also enables
the ability to comply with many guidelines and regulations established by many different
industries.You should immediately back up the certifi cates and the private key associated with the certifi cates when using TDE.
If you don’t have them backed up you cannot restore or attach the database to another server.A certifi cate is a digitally signed security object that is used for containers for keys because
they can hold information like an expiration date and issuers. Asymmetric keys are used
to secure the symmetric keys and are used for limited data encryption and digitally signed
database objects. Certifi cates are similar to asymmetric keys but are generally issued by
an organization, such as VeriSign to certify that the organization associated with the cer-
tifi cate is legitimate. It’s possible, and recommended, to generate local certifi cates within
SQL Server for your database encryption and decryption requirements. SQL Server 2012 also
enables external key management and hardware security modules to be used by SQL Server
internal key and certifi cate generation algorithms.The SQL Server Crypto Hierarchy
SQL Server encryption is based on a hierarchy of keys. At the top of the hierarchy is
a unique service master key generated by SQL Server for encryption the fi rst time it’s
needed.At the next level is the database master key, which is a symmetric key SQL Server uses to
encrypt private certifi cates and asymmetric keys. You create a database master key using the
create master key DDL command. SQL Server then encrypts the database master using
the service master key and stores it in both the user database and the master database:CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@$rw0rD';The password must meet Windows’ strong password requirements.To view information about the master keys, use the sys.symmetric_keys catalog view.Within the database, and below the database master key in SQL Server’s cryptographic hier-
archy, are certifi cates and private keys.c34.indd 854c34.indd 854 7/31/2012 10:01:46 AM7/31/2012 10:01:46 AM
http://www.it-ebooks.info