Transparent Data Encryption
Inside & Out
By Bradley Ball MCTS, MCITP
Speaker Introduction Bradley Ball
Over 10 Years IT Experience Previous experience DBA, for the U.S. Army, The Executive Office of the President Currently a Sr. SQL DBA at Publix MCITP SQL 2005 DBA, MCTS SQL 2008 DBA Blog: http://www.SQLBalls.com Twitter: @SQLBalls
SQL Saturday 85 Orlando September 24th 2011 Microsoft’s Buck Woody on Performance and Tuning September 23rd 2011 $99! http://bit.ly/o3Byvn Jorge Serraga, Bradley Schatch, Kyle Walker, Mike Davis Pragmatic Works Full Day BI Pre-Con September 23rd 2011 $99! http://bit.ly/nJoENN All This Plus Lunch, Coffee, & Tea
Agenda What is Transparent Data Encryption (TDE) What does TDE do, how does TDE work
Managing Certificates
Why Choose TDE Additional layer of security Required by some regulatory compliance laws Business Requirements Customer Security
How Does It Work
When Data is Missing or Stolen
Thieves Steal Personal Data of 26.5M Vets
Information on 207,000 Army Reservists Stolen
http://www.usatoday.com/money/industries/retail/2007-11-30-tjx-visa-breachsettlement_N.htm
Idaho Power says Mercer breach affected over 375,000
http://www.informationweek.com/news/security/showArticle.jhtml?articleID=199203277
TJX, Visa reach $40.9M settlement for data breach
http://www.govinfosecurity.com/articles.php?art_id=2527
Estimates Put T.J. Maxx Security Fiasco At $4.5 Billion
http://www.washingtonpost.com/wpdyn/content/article/2006/05/22/AR2006052200839.html
http://www.idahopower.com/pdfs/newscommunity/news/MercerIncidentFAQs081310.pdf
East Moreland Surgical Clinic Burglarized, 800,000 Patents Info on Stolen Media
http://health.einnews.com/pr-news/151784-eastmoreland-surgical-clinic-burglarized
What is Transparent Data Encryption A Physical Security Database level Encryption Solution Transparent Data Encryption is a means to encrypt the contents of a SQL Server Database on the Windows API storage level. It performs real time Encryption and Decryption of the data files , Database Backup Files (Full, Differential, Transactional, and Filegroup), and related Database Snapshots.
SQL Version’s & Compatibility Transparent Data Encryption is available in the following SQL Editions: SQL 2008 Enterprise Edition SQL 2008 Developer Edition SQL 2008 R2 Enterprise Edition SQL 2008 R2 Developer Edition SQL 2008 R2 Datacenter Edition
How Do you implement TDE There are 4 Steps Create a Master Key (Master database) 2. Create a Server Certificate (Master database) 3. Create a Database Encryption Key (User database) 4. Enable Encryption (User database) 1.
Supported Encryption Algorithms
AES 128 bit 196 bit 256 bit
3 Key Triple DES Cipher Block Chaining
Architecture
When Enabling TDE Time to encryption is based on Size Only DDL Database File level commands will be blocked
Altering File Groups No BLOCKING/LOCKING on User activity You cannot Drop Data Files or Add new
Data Files while encrypting or decrypting
What Inherits Encryption Data Files Log Files* Database Snapshots Database Backups (Full, Differential, Filegroup, and Transactional)
*Virtual Log Files written before TDE is enabled will be unencrypted until they are overwritten.
Demo
How is Performance Effected CPU normally takes a 3-5% increase* That means if you are on a system with High CPU usage this could cause up to a 28% over head* Systems that I previously worked with that, utilized TDE with the saw little to no performance increase
*These are numbers that Microsoft put forth but they have not published how they achieved these results
Self Managing Certificates
Don’t Over Complicate it
Think of the current Backup Planning
Certificates on servers with SAN and Recovery level will get replicated Certificates will also get swept to tape
Password Management
Plan to keep a Copy of the Certificate and the Password on hand Adhere to best practices through Automation
Keep Passwords in an replicated SAN location, in a secure tool like Keypass Automate Master & Private Key password changes
Previous Experience
1 Man shop, over 35 prod TDE servers, managed just fine with Keypass, SAN replication, And sweeping Certificates to Tape
References
Jasper Smith SQL Server MVP blog New in SQL 2008 : Transparent Data Encryption Part I & II http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/24/ new-in-sql-2008-transparent-data-encryption-overview.aspx Sung Hsueh Database Encryption in SQL Server 2008 Enterprise Edition http://msdn.microsoft.com/enus/library/cc278098(SQL.100).aspx
Questions
?