ManagingSecurity•Chapter4 167 Self Test Quick Answer Key 1. C 2. D 3. B 4. C 5. A 6. B 7. C 8. B 9. D 10. B 11. A 12. C 13. D 14. B 15. D 16. D 17. B 18. C 19. B 20. D This page intentionally left blank 169 Exam objectives review: ˛ Summary of Exam Objectives ˛ Exam Objectives Fast Track ˛ Exam Objectives Frequently Asked Questions ˛ Self Test ˛ Self Test Quick Answer Key Exam objectives in this chapter: Understanding Transparent Data Encryption  Encryption Keys  Cell- and Column-Level Encryption  EFS Encryption (  Through the OS) Third-Party Tools  Managing Data Encryption Chapter 5 MCTS SQL Server 2008 Exam 432 170 Chapter5•ManagingDataEncryption Introduction Many enhancements have been made to SQL Server 2008 in area data encryption. These enhancements are covered in detail in this chapter. We’ll explain encryption keys along with encryption and encryption key management. It’s important to understand when and why to use different forms of encryption as well as how to implement. In SQL Server 2008 there are a number of different options available. In this chapter, we will touch on some of the benefits of using encryption. Finally, we’ll talk about some other types of encryption that are available, such as Windows built-in Encrypting File System (EFS). Understanding Transparent Data Encryption With overall concern for data privacy as well as the increase in regulatory compliance in the area of data security, encryption is used as a method for protecting data. Encryption is a way of keeping data confidential and unreadable by unauthorized users. SQL Server 2008 introduced transparent data encryption (TDE) to provide the ability to encrypt entire databases, data, and log files without the requirement of application changes and with minimal performance impact. TDE protects data files at rest by encrypting the data and log files for a specific database on disk. When a transaction requires data from data or log files, the specific data pages are decrypted in memory. Once a TDE is enabled on a database, all the database’s backups are encrypted. Also, tempdb will be encrypted. It’s interesting to note that filestream data (the new filestream data types) will be encrypted as well. In the past, it was necessary to either use a third-party tool, or if you were using cell-based encryption, you would need to change the data type of the column to varbinary. This would on occasion require changes to foreign keys and make searching more complex. The application needed to make an additional call to encrypt and decrypt the data. Transparent data encryption will allow you to apply encryption to a database without having to change the application that accesses the database. All data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk when using transparent data encryption. Managing Data Encryption • Chapter 5 171 Te s T Da y Ti p Be sure to understand which features are available in which editions, especially when it comes to encryption! Head of the Class… Encryption and Security But why use encryption if my database server and facilities are physically secure? While this may be true for your organization, the reality is the backups of the data and log files are susceptible to loss or theft. Often the backups are stored off site. Ideally they should be stored in a physically secure location. More importantly, without TDE or another post-backup encryption method, the backup data and log files can be restored on another server and information can be stolen. More often than not, the use of backup files and tapes used for disaster recovery at remote sites as focused more for availability and the security concerns of the media are not as rigid. The economic realities of outsourcing and downsizing have increased the real threat of losing data to theft. Regulations to maintain data privacy and integrity have created a key operational requirement in the data management business. While database encryption is not required to protect information as such as credit card, Social Security numbers, and other personally identifiable information (PII) to meet the government regulations per Sarbanes-Oxley and the Health Insurance Portability and Accountability Act of 1996 (HIPAA), it is an easier and more cost-effective solution for a growing international business problem. . availability and the security concerns of the media are not as rigid. The economic realities of outsourcing and downsizing have increased the real threat of losing data to theft. Regulations. implement. In SQL Server 2008 there are a number of different options available. In this chapter, we will touch on some of the benefits of using encryption. Finally, we’ll talk about some other types. (  Through the OS) Third-Party Tools  Managing Data Encryption Chapter 5 MCTS SQL Server 2008 Exam 432 170 Chapter5•ManagingDataEncryption Introduction Many enhancements have been made to SQL