David Dye

Introduction

Cryptographic Services in SQL

SMK

DMK

Keys

Certificates

Encryption

David Dye

derekman1@msn.com`

HTTP://WWW.SQLSAFETY.COM

◦First implemented in SQL 2005

Foundation based on Windows operating system

Windows DPAPI

Data Protection Application Programming Interface

Algorithms based on the OS

◦Column level encryption is based on a single column DOES NOT encrypt the rest of the database

◦ Database encryption done using Transparent Database Encryption, TDE More here: http://www.sqlsafety.com/?p=468

◦ BOTH take resources and protect data at rest and NOT over the wire

◦ Every business sector requires obfuscating/hiding data that must be stored Payment Card Industry Security Standard

(PCI) Health Insurance Portability and

Accountability (HIPPA) Sarbanes-Oxley(SOX) Criminal Justice Information System

Standards (CJIS)◦ “Data at rest” Data stored in a database

◦ Resources Storage Encrypted data takes more disk space

than unencrypted

CPU Encrypting and decrypting data takes

additional CPU

◦ Index SQL can not use index seek on encrypted data

◦Does the data have to be stored?

Can it be utilized in a set based solution in temp tables or memory and then dropped when done?

◦Can the data be stored as a salted secure hash

◦Encryption The process if hiding clear text data, obfuscation, in a way that provides the ability to re-present the clear text at a later time Referred to as cyphertext

◦Decryption Transforming data back to its original state Referred to as cleartext

◦Means to encrypt column level data Passphrase

Data is encrypted with a passphrase Less secure and absent SSL passphrase is sent

clear text over the wire Symmetric key

Same key is used for encryption and decryption Asymmetric key

Public key is used to encrypt private key is used to decrypt Considered more secure than symmetric

Certificate Digitally-signed security object that binds the public

key to the principal who holds the private key

ASYMKEY_ID

ASYMKEYPROPERTY

CERTPROPERTY

CERT_ID

CRYPT_GEN_RANDOM

DECRYPTBYASYMKEY

DECRYPTBYCERT

DECRYPTBYKEY

DECRYPTBYKEYAUTOASYMKEY

DECRYPTBYKEYAUTOCERT

DECRYPTBYPASSPHRASE

ENCRYPTBYASYMKEY

ENCRYPTBYCERT

ENCRYPTBYKEY

ENCRYPTBYPASSPHRASE

HASHBYTES

IS_OBJECTSIGNED

Key_GUID

Key_ID

KEY_NAME

SIGNBYASYMKEY

SIGNBYCERT

SYMKEYPROPERTY

VERIFYSIGNEDBYCERT

VERIFYSIGNEDBYASYMKEY

SELECT CRYPT_GEN_RANDOM(5000) ;

GO

SELECT CRYPT_GEN_RANDOM(4, 0x25F18060);

GO

SELECT HASHBYTES('SHA1', 'Pa$$w0rd');

GO

http://msdn.microsoft.com/en-us/library/cc837966(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/cc837966(v=sql.100).aspx

Service Master Key ◦ Protected by the DPAPI

Data Protection Programming Interface OS specific

Based on the operating system will dictate the algorithms available

◦ Generated using the SQL service security account on first start up of the SQL Service

◦ Best Practices Changing the security context of the SQL service should be

done using SQL Server Configuration Manager If the Services Microsoft Management Console is used insure

that a new key is generated using the ALTER SERVICE MASTER KEY T-SQL command

Backup the Service Master Key

BACKUP SMK

RESTORE SMK

Created in the database, user or system

Only one Database Master Key per database◦ Database backups include the Database Master Key◦ Used to protect database-level resources

Keys

Certificates

◦ Two keys are created by default One encrypted by the Service Master Key

One encrypted by the password used during creation

◦ Access is through the Service Master Key or password used during creation This means than anyone with access to either the Service

Master Key, such as sysadmins, or the password also has access to the Database Master Key

Can be loaded from certificate, or asymmetric key created externally

When created in SQL it is self signed◦ Requires that the Database Master Key is created first

Little difference between asymmetric key created in SQL◦ SQL certificates can be exported and SQL asymmetric keys

cannot be exported

Certificate with private keys commonly require passwords to open them, password must be provided separately

Certificates, or asymmetric keys, private key can be protected with a password or with the Database Master Key◦ This choice determines how the key must be opened for

use◦ Only one algorithm can be used at one time

Algorithm can be changed using the ALTER T-SQL command

Securable entity at the database level Encrypted using

◦ Certificate◦ Password◦ Symmetric key◦ Asymmetric key◦ PROVIDER

Encryption◦ DES,◦ TRIPLE_DES,◦ TRIPLE_DES_3KEY,◦ RC2,◦ RC4,◦ RC4_128,◦ DESX,◦ AES_128,◦ AES_192,◦ AES_256

Can have more than one encryption of each type◦ When encrypted with a password instead of the public key of the database master key, the

TRIPLE DES encryption algorithm is used◦ Because of this, keys that are created with a strong encryption algorithm, such as AES, are

themselves secured by a weaker algorithm.

http://technet.microsoft.com/en-us/library/ms188357.aspx

Securable entity at the database level

Contains both a public key and a private key

By default private key is protected by the database master key◦ If no database master key has been created, a

password is required to protect the private key.

Private key can be 512, 1024, or 2048 bits long.

http://msdn.microsoft.com/en-us/library/ms174430.aspx

CREATE DMK

BACKUP DMK

CREATE SYMMETRIC KEY

CREATE CERTIFICATE

BACKUP CERTIFICATE

What to encrypt?◦ Define the scope

Based on governance, oversight, organizational policies

Database

All files?

TDE-Transparent Data Encryption

Over the wire?

SSL

Specific columns

SQL Cryptographic services

Client based encryption

Extended store procedures –NOT!!

Foundation has been built for us!!◦ SQL Cryptographic Services

Built on the DPAPI

SMK

◦ Native to SQL

Can be implemented in T-SQL

No longer requires extended stored procedures or CLR

Easy for developers

NOT SO MUCH for DBA supporting ISV databases

THIS IS NOT A REPLACEMENT TO SSL

Data is still unencrypted cross the wire

Easily implemented◦ Create symmetric key

◦ Open key to encrypt/decrypt data

Encryption Using Symmetric Key

Decryption Using Symmetric Key

Transactions Using Symmetric Key

Symmetric Key Encryption works but what next?◦ More secure requirements

◦ Encryption by certificate

Internal/external certs

Integrating SQL security with column level encryption

THIS IS NOT A REPLACEMENT TO DATABASE ENCRYPTION OR ENCRYPTION OVER THE WIRE!!!!

Provides security by assigning permission(s) to User(s)

Alone requires that a password is used to encrypt/decrypt◦ Without SSL password is easily revealed◦ When used in T-SQL object, stored procedure, view,

function, password is revealed

Provide encryption but has inherent security issues◦ Consider previous demonstration using key with

password

◦CREATE SYMMETRIC

◦ ENCRYPT/DECRYPT DATA USING KEY(S)

Can be internally or externally created◦ SQL allows the import of certificates

Can provide a means of authentication◦ Additional steps required for certificate login

authentication

Provide a securable object at the database and server level

Does not require passing the argument of a password◦ If the certificate specified does not exist in the

database or the current user is not authorized to use it, the EncryptByCert and DecryptByCertfunctions return NULL and no error message is returned

◦ Encryption Using Certificate

◦ Encryption Using Certificate in Proc