An Encryption Primer Steve Jones Editor in Chief

An Encryption Primer Steve Jones Editor in Chief SQLServer. Central

Agenda • • What is encryption? Encryption in SQL Server Transparent Data Encryption Hashing Symmetric Keys Asymmetric Keys Communications

What is Encryption?

encryption is the process of transforming information (referred to as plaintext) using an algorithm (called a cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key. The result of the process is encrypted information (in cryptography, referred to as ciphertext). - Wikipedia

Simple Ciphers ABCDEFGHIJKLMNOPQRSTUVWXYZABC WKLV LV HQFUBSWHG

Simple Ciphers ABCDEFGHIJKLMNOPQRSTUVWXYZABC WKLV LV HQFUBSWHG THIS IS ENCRYPTED

Complex Encryption Results: --------------------------------0 x 00 E 2 A 26 D 824 E 22468392458 DE 6 F 450 DA 0100000025 DE 09 EF 3 AD 8 D 7 C 989 E 393 BF 9 FE 1368 D 04 C 1 B 9 BEE 086 EFFDF 6 F 77 AF 9 E 3 A 3 B 8142 F 23723 D 536 C 72 C 216 D 6 F 9 B 104 A 5 E 44 A

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption in SQL Server Instance Communication Link (the wire) Client SQL Server memory Client file system Backup files SQL Server data files

Encryption Hierarchy

Transparent Data Encryption • TDE introduced in SQL Server 2008 • Protects the data at rest by encrypting the data on disk. – The transaction log is encrypted – Backups are encrypted (this can eliminate compression advantages) – Tempdb is encrypted for all operations. – Replication data is not encrypted – Filestream data is not encrypted

Transparent Data Encryption • Implemented with a simple ALTER DATABASE command ALTER DATABASE Adventure. Works 2008 R 2 SET ENCRYPTION ON; GO • Encryption is handled by the Database Encryption Key (DEK) • Requires a Database Master Key (DMK) and a Certificate to protect the DEK • Backups of the DEK are necessary to restore a backup of a TDE encrypted database (and the certificate protecting the key).

Transparent Data Encryption

Transparent Data Encryption For more information, see session SQL 228: Transparent Data Encryption Inside and Out In SQL Server 2012

Hashing • “A hash function is any algorithm or subroutine that maps large data sets, called keys, to smaller data sets. ” - Wikipedia

Hashing • SQL Server uses the HASHBYTES functions • there are other implementations using. NET/CLR that you can include. (Expert SQL Server Encryption, Michael Coles) • CHECKSUM() or BINARY_CHECKSUM() can also be used.

Hashing • In security applications, hashing is used to mask the actual data, but provide a way to still use the data. • DEMO

Hashing or Encryption • Hashing is not really encryption – Decryption is not supported (usually) • Hashing is deterministic, encryption is not • Hashing is quicker • In general, a hash of searchable data can be used to allow indexing of encrypted data. – Caveat – Only hash the portion of the encrypted data needed for searching, e. g. last four digits of a credit card number. • Choose the strongest algorithm available in your version. – SQL Server 2008 – SHA 1 – SQL Server 2012 - SHA 2_512

Keys • Multiple Keys in SQL Server – Service Master Key – Database Encryption Key – Symmetric Keys – Asymmetric Keys – Certificates

The Encryption Hierarchy

Service Master Key • Service Master Key = SMK • The Service Master Key is created when it is first needed. No CREATE DDL • Secured by Windows DPAPI (default) • Accessed by Service Account for database engine, or a principal with access to the service account name and password

Service Master Key • Must be manually backed up. BACKUP SERVICE MASTER KEY • Must be restored in a DR situation to open other keys secured by this key (Database Master Keys) • Can be regenerated if necessary. – This can cause data loss

Service Master Key • A restore or regenerate requires a decryption and re-encryption of all keys protected by this key – VERY RESOURCE INTENSIVE • The FORCE option in restores bypasses errors.

Database Master Key • Database Master Key = DMK • The Database Master Key is created by an administrator (CREATE/ALTER DDL) • This is secured by the SMK and a password (Triple. DES encryption) • This can be secured by password only (DROP ENCRYPTION BY SERVICE MASTER KEY option)

Database Master Key • Backup and restore using DDL commands BACKUP MASTER KEY RESTORE MASTER KEY • OPEN/CLOSE manually if not protected by the SMK • Attach/restore of an encrypted database requires the password for the DMK • You can alter the DMK to add SMK encryption after attach/restore

Symmetric Encryption • Like a normal key lock • The key that encrypts the data also decrypts the data

Symmetric Keys • Symmetric Keys are created in a database and are always in that database (cannot be backed up/restored) • Symmetric Keys are deterministic, and can be duplicated with the same creation parameters. • Symmetric keys require less resources than asymmetric keys, but there is still an additional CPU load from their use.

Symmetric Keys • DEMO

Symmetric Keys • The identity value always generates the same GUID for the key. These must be unique in a session. • The KEY_SOURCE and IDENTITY can be used to recreate a key. If you choose the same ones, and the same algorithm, you’ll get the same key • You can, and should, secure these keys with asymmetric keys

Symmetric Keys • The algorithm used is stored in the header of the encrypted data. • You can generate temporary keys for encryption/decryption • CREATE SYMMETRIC KEY #My. Temp. Key • Encryption with passphrases uses symmetric keys (Triple. DES)

Asymmetric Encryption • Asymmetric keys are unlike keys and locks in the real world. • Based on factoring very large prime numbers. • More secure than symmetric keys • Require more resources for encryption/decryption than symmetric keys

Asymmetric Encryption Key 1 Now is the time for all good men to come to the aid of their country 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Asymmetric Algorithm Key 1 Asymmetric Algorithm 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 0 x. E 7 A 518047 A 8 D 38 36 B 76006 D 9 CE 04 DA 2 F 803607 A 57 CD 7 F 9 EE 855 FC 3451 EB 02 A 076 F 28 DD 614 BA 841 AC 756 E 52 CFEC 4006 746480 C 8204 D 57908 3 C 4 AD 0 D 627 CAD 24

Asymmetric Encryption Key 1 Now is the time for all good men to come to the aid of their country 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Asymmetric Algorithm 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Key 2 Asymmetric Algorithm Now is the time for all good men to come to the aid of their country

Asymmetric Encryption Key 1 – Private Key 2 – Public Keys 1 and 2 are paired and generated together. One is referred to as a private key and the other a public key. Only the user has the private key, but the public key is distributed to everyone

Asymmetric Encryption Anyone encrypts with Steve’s Public Key Now is the time for all good men to come to the aid of their country 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Asymmetric Algorithm 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Only Steve can decrypt with his private key Now is the time for all good men to come to the aid of their country Asymmetric Algorithm

Asymmetric Encryption Steve can encrypt with his private key Now is the time for all good men to come to the aid of their country 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Asymmetric Algorithm 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 E 02238 EEAE 588 E 0 6 D 00 F 8 D 0 C 6 FAB 5 C 48 F 68639 ABB 400356 4 CFB 48 A 41 BA 373 C FA 411 E 99 D 3 AB 31 A 1 B 7 CE 40 CB 35 Anyone can decrypt with Steve’s public key Now is the time for all good men to come to the aid of their country Asymmetric Algorithm

Asymmetric Encryption Steve can encrypt with his private key 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 Now is the time Steve encrypts again with Andy’s Public Key 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 0 x 48385 D 8 A 87 BD 329 FF 328 E 476 BC 234

Asymmetric Encryption 0 x 48385 D 8 A 87 BD 329 FF 328 E 476 BC 234 Andy decrypts the outer message with his private key 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 Andy then decrypts with Steve’s Public key to verify the message is from Steve 0 x 26 CD 66 B 61 E 50369 CBBDB 42 F 48423737 Now is the time

Asymmetric Encryption • Use DDL to create asymmetric keys (CREATE/DROP/ALTER) • Can be created outside the server (FROM FILE option) – SN. exe (Visual Studio SDK) – Makecert (Windows SDK)

Asymmetric Encryption Create parent key Create child key protected by parent key Encrypt data with child key Open parent key Open child key decryption by parent key Decrypt data with child key

Asymmetric Encryption Create parent key CREATE SYMMETRIC KEY CREATE ASYMMETRIC KEY CREATE CERTIFICATE Create child key protected by parent key CREATE SYMMETRIC KEY Open parent key OPENSYMMETRIC KEY OPEN ASYMMETRIC KEY OPEN CERTIFICATE Open child key decryption by parent key OPEN SYMMETRIC KEY DECRYPTION BY XXX Encrypt data with child key ENCRYPTBYKEY ENCRYPTBYASYMKEY Decrypt data with child key DECRYPTBYKEY DECRYPTBYASYMKEY

Asymmetric Encryption • Demo

Asymmetric Encryption • You can encrypt an asymmetric key with a password. – This will be required for decryption – Not required for encryption (strange) • Asymmetric keys are usually used to encrypt symmetric keys, which encrypt the data. This balances security with resources • You can remove the private key (prevents decryption in that db).

Certificates • Certificates have additional metadata with the public/private keys. • Expiration dates are not enforced by SQL Server for encryption purposes. – Administrators must decrypt/re-encrypt the data and remove the old certificates – Useful for marking the key rotation dates (query sys. certificates) • To restore certificates, use CREATE CERTIFICATE.

Communications • Encrypt the connection to/from SQL Server • Two options – SSL encryption from SQL Server – IPSec encryption at the Windows host network layer.

Communications • SSL encryption across the wire • Install certificate on SQL Server, set the FORCE ENCRYPTION options – Yes = required – No = client option • Certificate must be valid based on the system time • All rules in BOL – Encrypting Connections to SQL Server – How to: Enable Encrypted Connections to the Database Engine • DO NOT USE SELF SIGNED CERTIFICATES

The End • • • Questions? Don’t forget to fill out your evaluations Resources at the end of the PPT www. sqlservercentral. com/forums Enjoy Dev. Connections

References • Encryption - http: //en. wikipedia. org/wiki/Encryption • Understanding TDE - http: //msdn. microsoft. com/enus/library/bb 934049. aspx • Hash Function - http: //en. wikipedia. org/wiki/Hash_function • Rainbow Tables - http: //en. wikipedia. org/wiki/Rainbow_table • TDE and Backup Compression http: //sqlcat. com/sqlcat/b/technicalnotes/archive/2009/02/16/tuningbackup-compression-part-2. aspx • Encrypting Connections to SQL Server - http: //msdn. microsoft. com/enus/library/ms 189067. aspx

References • • BACKUP SERVICE MASTER KEY - http: //technet. microsoft. com/enus/library/ms 190337. aspx RESTORE SERVICE MASTER KEY - http: //technet. microsoft. com/enus/library/ms 187972. aspx ALTER SERVICE MASTER KEY - http: //technet. microsoft. com/enus/library/ms 187788. aspx BACKUP MASTER KEY - http: //technet. microsoft. com/enus/library/ms 174387. aspx RESTORE MASTER KEY - http: //technet. microsoft. com/enus/library/ms 186336. aspx ALTER MASTER KEY - http: //technet. microsoft. com/enus/library/ms 186937. aspx OPEN MASTER KEY - http: //technet. microsoft. com/en-us/library/ms 174433. aspx CLOSE MASTER KEY - http: //technet. microsoft. com/enus/library/ms 188387. aspx

References • HASHBYTES - http: //msdn. microsoft. com/enus/library/ms 174415. aspx • CHECKSUM() - http: //msdn. microsoft. com/enus/library/ms 189788. aspx • BINARY_CHECKSUM() - http: //msdn. microsoft. com/enus/library/ms 173784. aspx • Expert SQL Server Encryption http: //www. amazon. com/gp/product/1430224649? ie=UTF 8& ta g=redgatsof 20& link. Code=as 2& camp=1789& creative=9325&am p; creative. ASIN=1430224649 • Data Hashing in SQL Server http: //blogs. msdn. com/b/sqlsecurity/archive/2011/08/26/datahashing. aspx

References • • • CREATE ASYMMETRIC KEY - http: //technet. microsoft. com/enus/library/ms 174430. aspx ALTER ASYMMETRIC KEY - http: //technet. microsoft. com/enus/library/ms 187311. aspx CREATE CERTIFICATE - http: //technet. microsoft. com/enus/library/ms 187798. aspx ALTER CERTIFICATE - http: //technet. microsoft. com/enus/library/ms 189511. aspx BACKUP CERTIFICATE - http: //technet. microsoft. com/enus/library/ms 178578. aspx sys. certificates - http: //technet. microsoft. com/en-us/library/ms 189774. aspx ENCRYPTBYPASSPHRASE - http: //technet. microsoft. com/enus/library/ms 188910. aspx ENCRYPTBYKEY - http: //technet. microsoft. com/en-us/library/ms 174361. aspx ENCRYPTBYASYMKEY - http: //technet. microsoft. com/enus/library/ms 186950. aspx

References • • • ENCRYPTBYCERT - http: //technet. microsoft. com/en-us/library/ms 188061. aspx DECRYPTBYKEY - http: //technet. microsoft. com/en-us/library/ms 181860. aspx DECRYPTBYASYMKEY - http: //technet. microsoft. com/enus/library/ms 189507. aspx DECRYPTBYCERT - http: //technet. microsoft. com/en-us/library/ms 178601. aspx DECRYPTBYKEYAUTOASYMKEY - http: //technet. microsoft. com/enus/library/ms 365420. aspx DECRYPTBYKEYAUTOCERT - http: //technet. microsoft. com/enus/library/ms 182559. aspx

References • • http: //blogs. msdn. com/b/raulga/archive/2006/03/11/549754. aspx Windows SDK (Makecert) - http: //msdn. microsoft. com/enus/windowsserver/bb 980924. aspx SN. EXE - http: //msdn. microsoft. com/en-us/library/k 5 b 5 tt 23. aspx Subway Hacked - http: //arstechnica. com/business/news/2011/12/how-hackers-gavesubway-a-30 -million-lesson-in-point-of-sale-security. ars Install SSL Certificate http: //blogs. msdn. com/b/jorgepc/archive/2008/02/19/enabling-certificates-for-sslconnection-on-sql-server-2005 -clustered-installation. aspx Encrypting Connections to SQL Server - http: //msdn. microsoft. com/enus/library/ms 189067. aspx SQL Server 2005: A look at the master keys - part 2 http: //blogs. msdn. com/b/lcris/archive/2005/09/30/475822. aspx

Images • • Enigma Machine - http: //www. flickr. com/photos/badwsky/34164244/ The Encryption Hierarchy from BOL - http: //msdn. microsoft. com/en. US/library/ms 189586%28 v=SQL. 90%29. aspx Hashing Image http: //upload. wikimedia. org/wikipedia/commons/thumb/5/58/Hash_table_4_1_ 1_0_0_1_0_LL. svg/240 px-Hash_table_4_1_1_0_0_1_0_LL. svg. png TDE Structure - http: //msdn. microsoft. com/en-us/library/bb 934049. aspx