SQL Server 2005 Encryption Architecture

1
SQL Server 2005 Encryption Architecture

• (Alice and Bob alive and well in Yukon)

PART TWO
2
Agenda

• SQL Server Encryption Technologies
• SQL Server 2000
• SQL Server 2005
• Server Key Management
• Data Encryption Technologies
• Im presuming knowledge of Crypto basics!
• Alice lt-gt Bob Eve

3
SQL Server Encryption

• SQL Server 2000
• SQL Server 2005
• Architecture
• Service Master Key
• Database Master Key
• Certificates
• Symmetric / Asymmetric Keys
• Column Level Encryption
• Code Signing

4
SQL Server 2000
WEAK!!!!!

• SQL login passwords
• Hashing function (salted) Open to attack
• SELECT pwdencrypt(myP_at_ssw0rd) not same
• Network Traffic
• All Network Libraries support SSL
• Needs Certificates
• Multiprotocol
• Encrypting custom Stored Procedures
• Symmetric key derived from several database
  values Open to attack
• CREATE PROC dbo.myProc WITH ENCRYPTION
• Encrypting table data
• EFS
• Microsoft CryptoAPI
• Ref http//www.sqlsecurity.com

5
SQL Server 2005 Encryption Architecture

• The Big Picture
• Service Master Key
• Database Master Keys
• Certificates
• Symmetric Keys
• Asymmetric Keys

6
The Big Picture
7
Service Master Key

• Only ONE Service Master Key
• 128bit 3DES key
• Generated at install time
• Directly (or indirectly) secures all other keys
• Encrypted by DPAPI key
• DPAPI Windows Data Protection API
• Uses SQL Server Service Credentials to encrypt
• Also uses machine credentials to encrypt
• If one fails, other used to recreate
• No CREATE or DROP statement
• BACK IT UP!!

8
Service Master Key management 1

• BACKUP SERVICE MASTER KEY TO FILE
  'path_to_file'
• ENCRYPTION BY PASSWORD 'password
• RESTORE SERVICE MASTER KEY FROM FILE
  'path_to_file'
• DECRYPTION BY PASSWORD 'password' FORCE
• Any errors encountered and whole process fails
• Force option to ignore decryption errors
• WILL (PROBABLY) LOSE DATA!!!

9
Service Key Management 2

• When changing SQL Server service account use
• SQL Server Configuration Manager
• (Automatically decrypts encrypts SMK)
• ALTER SERVICE MASTER KEY statement
• Enabling decryption by the new account while SQL
  Server is running as the old account
• ALTER SERVICE MASTER KEY
• WITH NEW_ACCOUNT 'AdvWorks\Sandeep',
• NEW_PASSWORD 'dslkd9700mMdlgf8'
• GO
• Enabling decryption by the new account while SQL
  Server is running as the new account
• ALTER SERVICE MASTER KEY
• WITH OLD_ACCOUNT 'AdvWorks\MiraG',
• OLD_PASSWORD '0i0035MDFh6gfk576t'
• GO

10
Service Key Management 3

• ALTER SERVICE MASTER KEY FORCE REGENERATE
• GO
• When SMK Regenerated option ALL dependent keys
  decrypted and re-encrypted
• Resource Intensive

11
Database Master Key

• Can only have One DMK per database
• 128bit 3DES key
• USE AdventureWorks
• CREATE MASTER KEY ENCRYPTION BY PASSWORD
  '23987hxJKL95234nl0zBe'
• GO
• DROP MASTER KEY
• GO
• Automatic Key Management Copy of DMK encrypted
  by SMK
• No need for OPEN MASTER KEY statement
• BUT sysadmin can decrypt DMK
• THUS
• ALTER MASTER KEY DROP ENCRYPTION BY SERVICE
  MASTER KEY

12
DMK management

• BACKUP MASTER KEY TO FILE 'path_to_file'
• ENCRYPTION BY PASSWORD 'password'
• RESTORE MASTER KEY FROM FILE 'path_to_file'
• DECRYPTION BY PASSWORD 'password'
• ENCRYPTION BY PASSWORD 'password'
• FORCE
• ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY
  PASSWORD 'dsjdkflJ435907NnmMsX003'

13
Certificates

• Once SMK DMK are configured
• UNLESS ENCRYPTED BY PASSWORD!
• Self-signed X.509 certificates
• CREATE CERTIFICATE statement used for both new
  and restored certificates
• CREATE CERTIFICATE myNewCert
• ENCRYPTION BY PASSWORD 'pGFD4bb925DGvbd243958
  7y'
• WITH SUBJECT This is a certificate',
• EXPIRY_DATE '10/31/2009'
• GO
• CREATE CERTIFICATE myRestoredCert
• FROM FILE 'c\CertStore\mySavedCert.cer'
• WITH PRIVATE KEY (FILE 'c\CertStore\mySaved
  PriKey.pvk',
• DECRYPTION BY PASSWORD 'sldkflk34et6gs53v0
  0')

14
Certificate management 1

• Exporting a certificate to a file
• BACKUP CERTIFICATE myCert02 TO FILE
  'c\storedcerts\myCert02'
• GO
• Exporting a certificate and a private key
• BACKUP CERTIFICATE myCert02 TO FILE
  'c\storedcerts\myCert02'
• WITH PRIVATE KEY ( FILE 'c\storedkeys\myCer
  t02key' ,
• ENCRYPTION BY PASSWORD '997jkhUbhkw4ez0876h
  KHJH5gh' )
• GO
• Exporting a certificate that has an encrypted
  private key
• BACKUP CERTIFICATE myCert02 TO FILE
  'c\storedcerts\myCert02'
• WITH PRIVATE KEY ( DECRYPTION BY PASSWORD
  '9875t66rfid7vble7r' ,
• FILE 'c\storedkeys\myCert02' ,
• ENCRYPTION BY PASSWORD '9n34khUbhkw4ecJH5gh
  ' )
• GO

15
Certificate management 2

• Changing the password of a certificate
• ALTER CERTIFICATE myCert03
• WITH PRIVATE KEY (DECRYPTION BY PASSWORD
  'pGF5DGvbd2439587y',
• ENCRYPTION BY PASSWORD '4-329578thlkajdshglX
  CSgf')
• GO
• Ability to Drop the Private key
• Signature Verification
• DO NOT do if certificate protects Symmetric Key
• ALTER CERTIFICATE myCert03
• REMOVE PRIVATE KEY

16
Symmetric Keys

• Must be itself encrypted by
• certificate, password, symmetric key, asymmetric
  key
• Can use multiple methods, multiple times
• CREATE SYMMETRIC KEY MarkKey01 WITH ALGORITHM
  AES_256
• ENCRYPTION BY CERTIFICATE myCert02
• GO
• CREATE SYMMETRIC KEY tempMarkKey
• WITH ALGORITHM AES_128,
• KEY_SOURCE
• These are the times to try mens souls',
• IDENTITY_VALUE ThomasPaine'
• ENCRYPTION BY CERTIFICATE myCert02
• GO

17
Symmetric Key management 1

• Changing Encryption methods
• CREATE SYMMETRIC KEY MarkKey99 WITH ALGORITHM
  AES_256
• ENCRYPTION BY CERTIFICATE myCert02
• Open the key.
• OPEN SYMMETRIC KEY MarkKey99 DECRYPTION BY
  CERTIFICATE myCert02
• WITH PASSWORD 'pGFD4bb925DGvbd2439587y'
• First, encrypt the key with a password.
• ALTER SYMMETRIC KEY MarkKey99
• ADD ENCRYPTION BY PASSWORD
  '435098fdlxk4Bj9oFD9h4'
• Now remove encryption by the certificate.
• ALTER SYMMETRIC KEY MarkKey99
• DROP ENCRYPTION BY CERTIFICATE myCert02
• CLOSE SYMMETRIC KEY MarkKey99

18
Asymmetric Keys

• What is difference between a Certificate and an
  Asymmetric Key?
• Both are encapsulating RSA keys
• Can NOT export Asymmetric Keys to .CER files
• Functionally no significant difference
• CREATE ASYMMETRIC KEY MarkAsymmKey08
• WITH ALGORITHM RSA_2048
• ENCRYPTION BY PASSWORD 'bmsAdk7i82bv55foajs
  d9764'
• GO

19
Asymmetric Key management 1

• Change password
• ALTER ASYMMETRIC KEY MarkAsymmKey08
• WITH PRIVATE KEY (
• DECRYPTION BY PASSWORD 'bmsAdk7i82bv55foajs
  d9764',
• ENCRYPTION BY PASSWORD '85423sdvcx79872ooo
  ')
• GO
• Removing the private key
• ALTER ASYMMETRIC KEY PacificSales19 REMOVE
  PRIVATE KEY
• GO

20
Encrypting Data

• EncryptByCert
• EncryptByAsymKey
• EncryptByKey
• EncryptByPassPhrase
• HashBytes

21
EncryptByCert DecryptByCert

• Encrypt data with the public key of a certificate
• EncryptByCert(Cert_ID(MyCert02'), _at_cleartext)
  )
• Decrypt data with the private key of a
  certificate.
• DecryptByCert(Cert_Id(MyCert02'), _at_ciphertext,
  _at_Cert_Password)

22
EncryptByAsymKey DecryptByAsymKey

• Encrypt data with an asymmetric key
• EncryptByAsymKey(AsymKey_ID(myCert'),
  _at_cleartext)
• Decrypt data with an asymmetric key
• DecryptByAsymKey( AsymKey_Id(myCert'),
• _at_ciphertext, _at_cert_password )

23
EncryptByKey DecryptByKey

• Key MUST be opened first
• OPEN SYMMETRIC KEY mySymmKey01
• DECRYPTION BY CERTIFICATE MarkCert02
• Encrypt data by using a symmetric key
• EncryptByKey(Key_GUID(' mySymmKey01 '),
  _at_Cleartext)
• Decrypt data by using a symmetric key
• DecryptByKey(_at_ciphertext))
• Close those keys!!
• CLOSE SYMMETRIC KEY ShippingSymKey04
• or CLOSE ALL SYMMETRIC KEYS

24
DecryptByKeyAutoAsymKey / Cert

• Decrypt using a symmetric key that is
  automatically decrypted using an asymmetric key
• DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey')
  , NULL ,_at_ciphertext)
• Decrypt by using a symmetric key that is
  automatically decrypted with a certificate
• DecryptByKeyAutoCert(cert_ID(myCert') , NULL
  ,_at_ciphertext)

25
Code Signing

• Yes you can!
• CREATE PROCEDURE GetOrdersSignedProc
• AS
• SELECT FROM Sales.SalesOrderHeader
• GO
• ADD SIGNATURE TO GetOrdersSignedProc BY
  CERTIFICATE DemoCert WITH PASSWORD 'Paw0rd'

26
HashBytes function

• Returns the MD2, MD4, MD5, SHA, or SHA1 hash of
  its input.
• HashBytes ( 'ltalgorithmgt', _at_input 'input' )
• ltalgorithmgt MD2 MD4 MD5 SHA SHA1

27
DEMO time permitting. (Ha!)
Email mark.morton_at_didata.com.au Major thanks
to Laurentiu Cristofor http//blogs.msdn.com/lcr
is/default.aspx
28
Thank you