Expert SQL Server 2008 Encryption

Michael Coles, Rodney Landrum1430224649, 978-1-4302-2464-8

Table of contents :
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