Transcript Under Lock And Key - Australian SQL Server User Group

Jim McLeod
MyDBA
[email protected]



SQL Server Performance Tuning
Consultant with MyDBA
Microsoft Certified Trainer with SQLskills
Australia
MCITP:Database Administration





Examine the basics of encryption
Determine when to use encryption
Explore how SQL Server handles
encryption technology
Examine how to structure your tables in
order to encrypt information efficiently
Consider how to protect your data from
the DBA

Plaintext


Codes




“This is a plaintext message.”
“Fred likes to eat cake.”
Fred = This, likes = is, to = a, eat = plaintext,
cake = message.
Not terribly useful in computing
Ciphertext


“Guvf vf n cynvagrkg zrffntr.”
Operates on a group of letters, individual
letters or bits



Key based encryption uses mathematical
algorithms to encrypt data by use of a key
Typically non-deterministic
Symmetric Keys


Asymmetric Keys



The same key will encrypt and decrypt the message
A public key is used to encrypt the message
Only the private key can be used to decrypt the
message
For a given key length:



Symmetric algorithms are faster to encrypt/decrypt
Asymmetric algorithms are more secure
Compromise: encrypt data with a symmetric key, and
encrypt the symmetric key with an asymmetric key.

Business Requirements

Hide sensitive data
 Passwords, Credit Card numbers, etc
Hide sensitive data from the DBA
 Protect against unauthorised changes


On disk encryption
Encrypted file system
 Transparent Data Encryption – SQL Server
2008
 Backup encryption – 3rd Party





Client Connections (SSL)
Network Traffic
Individual Columns
Stored Procedures, Triggers, Functions,
Views, etc

Data can be encrypted at one of two points



Application encryption:




Before it gets to the database engine (Application)
After it gets to the database engine (Database)
Key must be stored in the application/another tier
Prevents Profiler from sniffing unencrypted data passed
into stored procedures
Database cannot search efficiently
Database Encryption



Unencrypted data accessible inside the database
Unencrypted data passed to the database engine
Key may be held in an Hardware Security Module
 via Extensible Key Management (2008)
 via Extended Stored Procedures/SQLCLR (2005)

A deterministic, one-way method of
obfuscating data






Original source data cannot be retrieved
Easy to compute
Extremely difficult to construct a string with a
given hash
A single bit changed in the source string will
radically alter the result
Extremely unlikely that two source strings will
produce the same hash
Deterministic means dictionary attacks are
possible

SUM() is a hashing function


SELECT SUM(1 + 50 + 20 + 25) = 96
HashBytes() function


128 bit - MD2, MD4, MD5
160 bit - SHA, SHA1

Passwords and authentication


Data integrity



Protect your data from changes via
Management Studio
Detecting changes in a table
Creating indexes for types that cannot be
indexed


Salting
Images, varchar(max)
Demonstration – enforcing row integrity



EncryptByPassphrase()
DecryptByPassphrase()
Uses a Triple-DES symmetric key




AES128, AES192, AES256 are stronger
No Database Master key, or certificate
management required
If the passphrase is lost, the data is lost
Demonstration


Profiler can expose data that should be
encrypted
Stored Procedures



Passphrase or unencrypted data can be
passed through as a parameter in plaintext
Passphrase can be hardcoded into the stored
procedure
Ad Hoc Queries



Passphrase may be visible over the network
Profiler will hide the event details
Application must supply the passphrase

Service Master Key
Instance scope
 Is created the first time it is required to
encrypt something
 Can be regenerated.


Database Master Key


Database scope
Created by the DBA when the database needs
to use encryption

Asymmetric Keys
If VIEW DEFINITION is granted, the user can
encrypt data
 If CONTROL is granted, the user can encrypt and
decrypt data


Public Key Certificates





Expiry Dates
Self Signed X.509 Standard (1024 bit)
Signed by a Certification Authority (384-3456 bit)
Used for encryption and authentication
Demonstrations


Asymmetric Key Encryption
Certificate Encryption

A symmetric key is similar to Passphrase



Not restricted to Triple-DES
Fast to encrypt, but if you know how to
encrypt, you can also decrypt
Symmetric Key with Certificate
Demonstration


Encrypted data cannot be searched on without
decrypting the data, resulting in a table scan
Need some way of narrowing the search




AdventureWorks comes with an index on Email,
indicating that they search for customers based
on an Email Address
Non-deterministic encryption means we can’t
index the encrypted value
Create a hash of the first part of the email



Reduce the number of rows requiring decryption from 1
million to 100.
Doesn’t give away any information about the Contact
Can be indexed
Demonstration




DBA has control over all certificates in all
databases
Database Master Keys encrypted via the
Service Master Key will be wide open to
the DBA
Certificates/keys protected by passwords
will be hidden from the DBA via Profiler
Demonstration







Feature of SQL Server 2008 Enterprise Edition
Encrypts data on disk, and is unencrypted when
loaded into memory
Uses a symmetric key called the Database
Encryption Key
Replication or memory dumps may expose
unencrypted data
No application architecture changes required.
Small performance hit on every disk I/O
Recovery of an encrypted database MUST be tested
on a separate server before a disaster


Backup your keys/certificates
Backups of encrypted database will not compress
well, but Page Compression will still work


Roll your own encryption functions
Try the SQL 2000 DBA Toolkit




Uses extended stored procedures
http://www.sqlservercentral.com/articles/Sec
urity/sql2000dbatoolkitpart1/2361/
Encrypt data at the application layer
Put database files on an encrypted
partition


Examine the basics of encryption
Determine when to use encryption


Explore how SQL Server handles encryption
technology


Hashing, Certificates, Symmetric Keys,
Asymmetric Keys, Transparent Data Encryption,
Extensible Key Management
Examine how to structure your tables in
order to encrypt information efficiently


Disk, Connection, Column, Network, Backups
Indexes of hashes
Consider how to protect your data from the
DBA