Here are some notes on “SQL Server 2008 Encryption” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://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.
- 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.
- 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 http://msdn.microsoft.com/en-us/library/ms174415.aspx
- 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.
- 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
- CREATE SYMMETRIC KEY
- See http://msdn.microsoft.com/en-us/library/ms188357.aspx
- They live in the database, view with sys.symmetric_keys
- See http://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 http://msdn.microsoft.com/en-us/library/ms189440.aspx
- CREATE CERTIFICATE
- Used to protect other keys, sign code, sign data
- See http://msdn.microsoft.com/en-us/library/ms187798.aspx
- Query with SELECT * FROM sys.certificates
- See http://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 http://msdn.microsoft.com/en-us/library/ms178578.aspx
Database Master Key
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘…’
- See http://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 http://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 http://msdn.microsoft.com/en-us/library/ms187788.aspx
- Always take a backup! BACKUP SERVICE MASTER KEY…
- See http://msdn.microsoft.com/en-us/library/ms190337.aspx
- 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 http://msdn.microsoft.com/en-us/library/ms345262.aspx
- 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: EncryptByKey, EncryptByCert, EncryptByAsymKey, EncryptByPassphrase
- See http://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)
- 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 http://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 http://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”