SQL Server 2008 Encryption

Here are some notes on “SQL Server 2008 Encryption” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

---

Encryption

• Cryptography: Keeping secrets. For how long?
• Cryptography: plain text – cipher text – plain text
• Historical note – Ceasar’s Cipher, Enigma machine
• Symmetric keys: Same key used to encrypt/decrypt
• Key distribution: How do I send you the key?
• Asymmetric keys: Encryption/Decryption with different, but related keys.
• - Private/Public keys. Only sender has the private key. Receiver uses public key.
• - Algorithm and public key are known, but it’s really hard to decrypt without the private key
• - Validates the sender. Can also be used for signing only.
• Salt: Randomness added to make it harder to reverse.

Hashes

• One-way function. Data fingerprint. Possible (but unlikely) collisions.
• Data changes (in content or position within the content) will likely change the hash.
• Commonly used for data integrity, password storage and verification.
• SQL function: HASHBYTES(‘algorithm’, data)
• See https://msdn.microsoft.com/en-us/library/ms174415.aspx

Encryption

• Encryption algorithms: several different available, complex and many public.
• SQL Server uses Microsoft Crypto Service Provider (CSP). Algorithms vary by OS.
• Certificates: Information about identity, issuer, key, expiration, etc..
• Used in web servers for HTTPS, Signed e-mail with digital signatures.
• Trusted Root Certificates. Looking at an HTTPS site, following the chain.
• Certificate revocation lists (CRL).
• SSL: Authenticates the server. Uses public key encryption to get symmetric session key.

Objects

• First class objects in SQL Server. There’s a hierarchy of keys (keys protect other keys)
• Service Master Key – Protected by DPAPI, associated with the service account
• Database Master Key
• Symmetric Key Object
• Asymmetric Key Object
• Certificate Object
• Functions to support encryption
• DDL for keys/certificates

Keys

• CREATE SYMMETRIC KEY
• See https://msdn.microsoft.com/en-us/library/ms188357.aspx
• They live in the database, view with sys.symmetric_keys
• See https://msdn.microsoft.com/en-us/library/ms189446.aspx
• Protected via another symmetric key, certificate, asymmetric key
• Can be regenerating from a passphrase (KEY_SOURCE, IDENTITY_VALUE, algorithm)
• ALTER SYMMETRIC KEY WITH REGENERATE
• See https://msdn.microsoft.com/en-us/library/ms189440.aspx

Certificates

• CREATE CERTIFICATE
• Used to protect other keys, sign code, sign data
• See https://msdn.microsoft.com/en-us/library/ms187798.aspx
• Query with SELECT * FROM sys.certificates
• See https://msdn.microsoft.com/en-us/library/ms189774.aspx
• Too slow for general encryption
• Private key can be secured via password or database master key
• Private key can also be removed from the database
• Handling private keys is important
• Always have a backup! BACKUP CERTIFICATE…
• See https://msdn.microsoft.com/en-us/library/ms178578.aspx

Database Master Key

• CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘…’
• See https://msdn.microsoft.com/en-us/library/ms174382.aspx
• Secures keys in the database, created by owners
• Support for regeneration (ALTER MASTER KEY)
• Always take a backup! BACKUP MASTER KEY …
• See https://msdn.microsoft.com/en-us/library/ms174387.aspx

Service Master Key

• Used to secure: linked servers, database master keys, proxy credentials
• Created upon first use, secured using DPAPI (service account credentials, machine credentials)
• Support for regeneration (ALTER SERVICE MASTER KEY)
• See https://msdn.microsoft.com/en-us/library/ms187788.aspx
• Always take a backup! BACKUP SERVICE MASTER KEY…
• See https://msdn.microsoft.com/en-us/library/ms190337.aspx

Encryption Algorithms

• Based on CAPI
• Master keys are 3DES keys, as password protection
• Certificates created are RSA1024-bit (can import 3456-bit)
• Asymmetric keys created are RSA 512, 1024, 2048-bit
• Symmetric keys created are 3DES or AES 128, 192, or 256-bit
• Obviously, larger keys will take longer to process
• Careful – the larger the key, the larger the minimum size of the encrypted bits
• Key (password, key, certificate (public key, private key (password, master key(service key(DPAPI)))))
• SQL Server supported algorithms depends on algorithms available in the OS
• See https://msdn.microsoft.com/en-us/library/ms345262.aspx

Encrypting columns

• Encrypted data is typically stored as varbinary
• How big? Maximum encrypted size is 8000 bytes
• It is salted (same data encrypted multiple times yields different results)
• Can’t index/full-text search encrypted columns
• Consider indexing a hash of the original data or only part of the original data instead
• Careful – these workarounds weaken your protection
• Discussion – Which data to encrypt? What’s the impact on database design?

Functions

• Functions: EncryptByKey, EncryptByCert, EncryptByAsymKey, EncryptByPassphrase
• See https://msdn.microsoft.com/en-us/library/ms174361.aspx
• Encrypted data is prefixed with the key GUID
• When you encrypt, you need to specify the key GUID
• When you decrypt, you don’t need to know the key (since the GUID is there)

Key management

• Symmetric keys (no DDL support, keys can be regenerated from passphrase)
• Asymmetric keys (can import from .SNK strong name files, no option to export)
• Certificates (can import/export to DER-encoded .CER files – X.509 certificates, private keys only in .PVK)
• Moving database – keys are in the database, but the service master key is not
• Data transfer – the important part of the problem is how you move the keys (use SSL)
• Changing keys – ALTER [SERVICE] MASTER KEY REGENERATE
• See https://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx

Extensible Key Management (EKM)

• Key storage/management/encryption via HSM module
• SQL EKM is a proxy to HSM key, provider DLL implements interface to HSM
• Data and keys are physically separate, centralized keys, good performance
• CREATE CRYPTOGRAPHIC PROVIDER … FROM FILE = ‘….DLL’
• See https://msdn.microsoft.com/en-us/library/bb677184.aspx
• CREATE [A]SYMMETRIC KEY … FROM PROVIDER … WITH ALGORITH …

Protecting data from admins

• BuiltinAdministrators rights
• Server Administrator (sysadmin) rights
• Discussion – risks and how to protect

Demo – Symmetric keys

• CREATE DATABASE
• CREATE SYMMETRIC KEY testkey WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD=’…’
• SELECT * FROM sys.symmetric_keys
• OPEN SYMMETRIC KEY testkey DECRYPTION BY PASSWORD=’…’
• DECLARE @cypherdata = varbinary(MAX)
• SET @cypherdata = ENCRYPTBYKEY(KEY_GUID(‘testkey’),’Text’)
• SELECT @cypherdata
• SELECT DATALENGTH(@cypherdata)
• SELECT DECRYPTBYKEY(@cypherdata) - - No need to specify the key
• CLOSE SYMMETRIC KEY testkey
• SELECT DECRYPTBYKEY(@cypherdata) - - Returns NULL
• SELECT COALESCE(DECRYPTBYKEY(@cypherdata),’RESTRICTED’) – Returns RESTRICTED
• Note – Cannot encrypt NULL value – “Argument data type void type is invalid”

See https://sqlblog.com/blogs/lara_rubbelke/archive/2007/09/19/pass-summit-demo-scripts.aspx

Comments

• Anonymous
  January 01, 2003
  Here are some notes on “SQL Server 2008 Auditing” I took while attending an advanced class on SQL Server
• Anonymous
  January 01, 2003
  Here are some notes on “SQL Server 2008 Security” I took while attending an advanced class on SQL Server
• Anonymous
  January 01, 2003
  Here are some notes on “SQL Server 2008 Transparent Data Encryption (TDE)” I took while attending an