Expert SQL Server 2008 Encryption (Expert's Voice in SQL Server)

Apress - Expert SQL Server 2008 Encryption (October 2009) (ATTiCA)......Page 1 Contents......Page 8 Contents at a Glance......Page 7 Books for Professionals......Page 2 Foreword......Page 16 About the Authors......Page 18 About the Technical Reviewer......Page 19 Acknowledgments......Page 20 Who This Book Is For......Page 22 How This Book Is Structured......Page 23 Do I Need Encryption?......Page 28 Are You Under Contractual or Professional Obligation to Protect Your Data?......Page 29 Why Encrypt the Database?......Page 30 Threat Modeling......Page 31 The First Ciphers......Page 34 The Rise of Cryptanalysis......Page 36 Bellaso Strikes Back......Page 37 War and Security......Page 39 How to Share a Secret......Page 41 Official Ciphers of the US Government......Page 43 Encryption in SQL Server 2005......Page 45 Summary......Page 46 SQL Encryption Key Hierarchy......Page 48 Key Distribution......Page 52 Backing Up the SMK......Page 53 Altering the SMK......Page 54 Altering a DMK......Page 56 Restoring a DMK......Page 58 Opening a DMK......Page 59 Permissions......Page 61 Catalog Views......Page 63 Dynamic Management Views and Functions......Page 67 Summary......Page 72 Symmetric Keys......Page 74 Creating and Protecting Symmetric Keys......Page 75 Encrypting Data......Page 76 Decrypting Data......Page 80 Adding an Authenticator......Page 81 Automatic Key Management......Page 83 Temporary Keys......Page 87 Layering Symmetric Keys......Page 88 Encryption with Passphrases......Page 93 Encryption Algorithms......Page 94 AES Family......Page 95 DES Family......Page 97 RC2 and RC4......Page 98 Summary......Page 99 Asymmetric Keys......Page 100 Generating and Installing Asymmetric Keys......Page 101 Encrypting Data......Page 105 Protecting Asymmetric Keys with Passwords......Page 109 Encrypting Data Directly with Asymmetric Keys......Page 110 Creating Certificates......Page 112 Creating SQL Server Self-Signed Certificates......Page 115 Encrypting Data......Page 116 Encrypting Data Directly with Certificates......Page 118 Backing Up Certificates......Page 120 Digital Signatures......Page 121 Database-Level Permissions......Page 124 Server-Level Permissions......Page 131 Signing Modules......Page 123 Summary......Page 136 What Is EKM?......Page 138 Configuring EKM......Page 139 Creating Asymmetric Keys......Page 142 Protecting Symmetric Keys......Page 143 Encrypting Data Directly......Page 144 Creating Symmetric Keys......Page 148 EKM Limitations......Page 150 Summary......Page 152 What Is TDE?......Page 154 What Isn’t Encrypted......Page 156 Advantages of TDE......Page 157 Enabling TDE......Page 158 Using TDE with EKM......Page 160 Checking Status......Page 161 Backups and Compression......Page 162 Windows-Based Encryption Options......Page 164 Summary......Page 177 Cryptographic Hash Functions......Page 178 SHA Hash Function......Page 179 Using HashBytes SHA-1......Page 180 HashBytes SHA-1 Limitations......Page 182 Hash Function Extension......Page 183 MD5 Hash Function......Page 185 MD5 Security......Page 188 CHECKSUM Functions......Page 189 Summary......Page 193 Encrypting By Passphrase......Page 194 EncryptAesByPassPhrase Function......Page 195 Deriving Encryption Keys......Page 198 DecryptAesByPassPhrase......Page 200 Testing the Functions......Page 203 GetHash......Page 204 SaltedHash......Page 208 Summary......Page 211 The Problem of Searching Encrypted Data......Page 212 Storing Partial Plaintext Values......Page 217 Storing Hashed Values......Page 220 Storing Salted Hashed Values......Page 223 Storing Hash-Based Message Authentication Codes......Page 225 Range Queries......Page 228 Summary......Page 229 Encrypting Connections toSQLServer 2008......Page 230 Network Monitor......Page 231 SQL Server Encryption Out of the Box......Page 233 Applying a Self-Signed Certificate......Page 236 Requesting a Certificate from a Valid CA......Page 241 Enforcing Encryption Between Server and Client......Page 245 Forcing Encryption at the Server......Page 246 Forcing Encryption from Clients with SQL Native Client......Page 247 Creating and Testing Secure Connections......Page 248 SQL Server Management Studio Example......Page 249 SQL Server Reporting Services Example......Page 253 Performance......Page 255 Summary......Page 256 Regulations......Page 258 What to Encrypt......Page 261 Example: From $15 Billion to Bankruptcy......Page 265 Summary......Page 268 Threat Modeling......Page 286 A......Page 270 C......Page 271 D......Page 274 E......Page 276 F......Page 277 H......Page 278 L......Page 279 O......Page 280 P......Page 281 R......Page 282 S......Page 283 Z......Page 284 Security Review......Page 290 Encryption Planning Checklist......Page 296 Prerequisites......Page 298 Configuring the HSM......Page 299 Configuring Client Access......Page 305 Setting Up SQL Server EKM......Page 311 Index......Page 316 Annotation Every day, organizations large and small fall victim to attacks on their data. Encryption provides a shield to help defend against intruders. Because of increasing pressure from government regulators, consumers, and the business community at large, the job descriptions of SQL DBAs and developers are expanding to include encryption. Expert SQL Server 2008 Encryption will show you how to efficiently implement SQL Server 2008 encryption functionality and features to secure your organizational data. Introduces encryption, guiding readers through its implementation in SQL Server Demonstrates advanced techniques such as the use of hardware security modules Covers all that a SQL Server database administrator needs to know about encryption What youll learn Take advantage of hardware security modules via extensible key management Implement targeted encryption of individual columns Secure an entire database at once with Transparent Data Encryption Encrypt disk volumes using BitLocker encryption Effectively design and manage encryption as part of your total security solution Digitally sign documents stored in your database Who this book is for The audience for this book includes SQL Server DBAs, SQL developers, and .NET developers who want to take advantage of the powerful encryption functionality available in SQL Server 2008. The features of SQL Server 2008 provide a powerful set of tools to secure your most sensitive data, helping protect it from theft. Table of Contents Introduction to Encryption Encryption Key Management Symmetric Encryption Asymmetric Encryption Extensible Key Management Transparent Data Encryption Hashing SQL CLR Cryptography Indexing Encrypted Data Encrypting Connections to SQL Server 2008 Regulatory Requirements