SQL Server 2008 Encryption

  • Article

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

Certificates

Database Master Key

Service Master Key

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