Ingeniero

Ing. Eduardo Castro, Phd ecastro@mswindowscr.org

http://comunidadwindows.org http://ecastrom.blogspot.com

Transparent Data Encryption External Key Management Data Auditing Pluggable CPU Transparent Client Redirect for Database Mirroring Database Mirroring Enhancements DBM: Auto Page Repair Declarative Management Framework Server Group Management Streamlined Installation Enterprise SystemManagement Performance Data Collection System Analysis Data Compression Query Optimization Modes Resource Governor Entity Data Model LINQ

Visual Entity Designer Entity Aware Adapters SQL Server Change Tracking Synchronized Programming Model Visual Studio Support SQL Server Conflict Detection FILESTREAM data type Integrated Full -Text Search Sparse Columns Large User-Defined Types Date / TimeData Types LOCATION data type SPATIAL data type Virtual Earth Integration Partitioned Table Parallelism Query Optimizations Persistent Lookups Change Data Capture

Backup Compression MERGE SQL Statement Data Profiling Star Join Enterprise Reporting Engine Internet Report Deployment Block Computations Scale-out Analysis BI Platform Management Export to Word and Excel Author reports in Word, ExcelReport Builder Enhancements TABLIX Rich Formatted Data Personalized Perspectives … and many more

Transparent data encryption – encrypt an entire database Backup encryption – compresses and secures the backup file Auditing – now monitors data access and modifications Policy-based Framework from Windows Server 2008 automates administrative tasks

Enterprise Data Platform

Protect yourinformation
Transparent Data Encryption External Key Management Encrypt your data without requiring an application re-write Consolidate security keys within the data center Integrated auditing support

Data Auditing
Pluggable CPU Enhanced Database Mirroring

Increase the reliability of your applications
Add system resources without affecting your users Leverage database mirroring to increasereliability

In SQL Server 2000, 3rd party support required Since SQL Server 2005
Built-in support for data encryption Support for key management

Encryption additions in SQL Server 2008
Transparent Data Encryption Extensible Key Management

Support for full SSL Encryption since SQL Server 2000
Clients: MDAC 2.6 or later Force encryption from client or server

Login packet encryption
Usedregardless of encryption settings Supported since 2000 Self-generated certificates avail since 2005

SQL Server 2005
− − − − Built-in encryption functions Key management in SQL Server Encrypted File System (EFS) Bit-Locker

SQL Server 2008
− Extensible Key Management (EKM) − Transparent Data Encryption (TDE)

Follow principal of least privilege! Avoid using sysadmin/sa and db_owner/dbo− Grant required perms to normal login

Never use the dbo schema
− User-schema separation

Applications should have own schema
− Consider multiple schemas

Leverage Flexible Database Roles
− Facilitates role separation

Consider Auditing user activity

HSM

SQL EKM Provider DLL

SQL EKM Key (HSM key proxy)

Data SQL Server

Key storage, management and encryption done by HSMmodule SQL EKM key is a proxy to HSM key SQL EKM Provider DLL implements SQLEKM interface, calls into HSM module

Security
Data and keys are physically separated (keys are stored in HSM modules) Centralized key management and storage for enterprise Additional authentication layer Separation of duties between db_owner and data owner

Performance
Pluggable hardware encryption boards

HSMSymmetric key

Asymmetric key

EKM Symmetric key SQL Server

EKM Asymmetric key

Data

Data
Native TDE DEK key Symmetric key

SQL Server 2008

DEK

Encryption/decryption at database level DEK is encrypted with:
− Certificate − Key residing in a Hardware Security Module (HSM)

Encrypted data page
Client Application

Certificate required to attach database files or...