Additional Security ToolsAdditional Security ToolsLesson 15

Skills MatrixSkills Matrix

Guarding Your DataGuarding Your Data• Maintaining data integrity can also

involve data encryption and auditing.

EncryptionEncryption• 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

SQL Server EncryptionSQL 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.

SQL Server EncryptionSQL 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.

Transparent Data EncryptionTransparent 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.

Transparent Data EncryptionTransparent 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.

Extensible Key ManagementExtensible 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.

AuditsAudits• 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.

C2 CriteriaC2 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.

C2 CriteriaC2 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

AuditingAuditing• 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.

SQL Server UtilitySQL 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.

SQL ServerSQL ServerUtility Utility ArchitectureArchitecture

SQL Server UtilitySQL 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.

SummarySummary• 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.

SummarySummary• 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.

Summary for Certification ExaminationSummary 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.