SQL Server – Cell Based Encryption – FAQ

Here is a quick FAQ on Cell Based Encryption

  1. Keep all your passwords
    • Master database password
    • You will need it will when
      • Restore your database – Log Shipping, etc
  2. Make sure you fully define your Symmetric Key; that is when creating them:
    • Specify the following arguments
      • KEY_SOURCE
      • Identity Value
    • If not fully defined, you will end up with a different KEY GUID when you invoke the KEY_GUID function
  3. Encrypted Data
    • The return from calling EncryptByKey is 8000 bytes
    • In essence, the maximum size of the original, un-encrypted data is at most about 7550
  4. Session Settings
    • Using the SQL Server encryption functions together with the ANSI_PADDING OFF setting could cause data loss because of implicit conversions
    • So please set ANSI_PADDING ON
    • Read more from Microsoft’s Official documentation
  5. Note the original data-type.  Though encrypted data is stored as binary / varbinary, you will need to convert/cast to the original datatype during data decryption ( DecryptByKey)
  6. Strongly consider adding a Salt ( Authenticator )
    • Please per-use the beautifully written posts in the Salt/Authenticator section from the listed references
  7. Everything will be slower
    • Index Strategy will have to be re-thought
    • With encryption, output data can and will vary for same initial data
  8. Compression
    • As post encrypt data is different for same initial data, pattern mapping that is useful and beneficial for block level compression misses more often than not
    • Row Level Compression, the less efficient of the two, should likely not be impacted as data is not stored in their native format (int, smalldatetime, etc), but as varbinary
  9. Performance
    • Optimization paths that can be achieved through schema design can unfortunately be hidden behind encrypted data



Cloning Symmetric Key

  1. Laurentiu Cristofor’s blog @microsoft.com – SQL Server 2005: How to regenerate the same symmetric key in two different databases
  2. Michael Coles – “Cloning” Symmetric Keys



  1. Michael K. Campbell in Practical SQL Server – SQL Server Management Studio’s Ugly ANSI_PADDING Bug – Aug 5, 2014



  1. Raul Garcia – MS – Prevent Tampering of Encrypting Data Using add_authenticator Argument of EncryptByKey



  1. Database Solutions Engineering – By Kevin Guinn; with Bryant Vo and Leena Kushwaha – Dell Product Group – April 2009

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s