Additional Security Tools

1. Additional Security Tools Lesson 15

2. Skills Matrix

3. Guarding Your Data • Maintaining data integrity can also involve data encryption and auditing.

4. Encryption • You can put another layer, encryption, on top of all this security. SQL Server encrypts data with hierarchical encryption and key management infrastructure. • Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys, in scopes that parallel the permissions hierarchy

5. SQL Server Encryption • To encrypt your connections to SQL Server, you first need to get a certificate. • You can get one from one of the major vendors such as VeriSign, or you can install Windows Certificate services and supply your own. • Once you have a certificate, you need to install it on the server. • Finally, you need to configure the clients to request encrypted connections to the server.

6. SQL Server Encryption • The database-level cryptographic features in SQL Server rely on a database master key. • This key does not generate automatically when you create the database. • It must be created by the system administrator. • You need but one master key per database.

7. Transparent Data Encryption • SQL Server 2008 includes a new feature known as Transparent Data Encryption (TDE). • This encryption is transparent to the application code as SQL Server 2008 automatically handles the encryption and decryption of all data going in to and out of the database. • The primary purpose of this TDE feature is to have the entire database encrypted so that any unauthorized person having direct access to copies of the database files and / or transaction log files cannot decrypt and read the data.

8. Transparent Data Encryption • It is critically important to understand that the database master key and the encryption certificate need to be backed up to a secure location. • This location also needs to be separate from regular backups or other copies of the database files. • The encryption security provided by TDE is meaningless if database files and the certificate both fall into the hands of the wrong person.

9. Extensible Key Management • SQL Server 2008 includes a new feature known as Extensible Key Management (EKM). • This is a method of providing for encryption methods using software and possibly hardware such as smart cards or USB devices provided by third-party entities. • With EKM, encryption can be established using physical hardware known as a Hardware Security Module (HSM). • This can be a more secure solution because the encryption keys do not reside with encrypted data in the database. • Instead, the keys are stored on the hardware device.

10. Audits • Audits keep a record of database activities. Set configuration options for the factors of concern and then review the results. • If you suspect someone uses an employee’s login inappropriately, audit for logins and look for suspicious behaviors.

12. C2 Criteria • In 1985, the Department of Defense published DOD Directive 5200.28-STD, Department of Defense Standard, “Department of Defense Trusted Computer System Evaluation Criteria” known familiarly as the “Orange Book” which was part of the “Rainbow Series” of security evaluation criteria. • The Orange Book laid out a matrix where A was the most trusted and D the least trusted. • Numbers were also used where 1 was the most stringent criteria. • The resultant classes, then, are A1, B1, B2, B3, C1, C2 and D.

13. C2 Criteria • C2 requires controlled access protection and is deemed appropriate for “business sensitive” data—less guarded then confidential, secret or top secret classifications. • C2 requires individual accountability through login procedures, audit trails, object reuse and resource isolation. • SQL Server provides these protections

14. Auditing • Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. • SQL Server Utility provides you a means to audit and manage your SQL Server environment as a whole through the concept of application and multiserver management.

15. SQL Server Utility • SQL Server Utility provides you a means to audit and manage your SQL Server environment as a whole through the concept of application and multiserver management. • The SQL Server Utility models your organization’s SQL Server-related entities in a unified view.

16. SQL ServerUtility Architecture

17. SQL Server Utility • Utility Explorer and SQL Server Utility viewpoints (in SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP). • Entities viewable in a SQL Server UCP include instances of SQL Server, data-tier applications, database files and storage volumes. Resource use can be monitored for CPU and storage use.

18. Summary • SQL Server encryption provides an additional security protection from hackers or eavesdroppers. • While someone might access your file stream or table, they will find the information undecipherable. • This comes at the expense of additional overhead.

19. Summary • With auditing you can create a record of what happens on your SQL Server instance. You can record successful connections to learn who uses what resources how often. • A management report might be appropriate to reward diligent employees. • With SQL Utility you can monitor the resource health of your entire enterprise and produce near real-time management and analysis reports.

20. Summary for Certification Examination • Know how to configure encryption on a column. • Understand transparent data encryption and its value. • For a given situation, determine which auditing technique can be of value.