Recommendations for using Cell Level Encryption in Azure SQL Database
Update: Fixed an error on the sample code.
When we introduced Transparent Data Encryption (TDE) to Azure SQL Database, we also introduced Cell-Level Encryption (CLE, also known as SQL Server key hierarchy).
For more details on TDE on Azure SQL Database, I would recommend visiting the Channel9 show for an excellent introduction: https://channel9.msdn.com/Shows/Data-Exposed/TDE-in-Azure-SQL-Database, and to BOL: https://msdn.microsoft.com/en-US/library/dn948096.aspx.
The main reason to introduce CLE into Azure SQL Database is to increase compatibility between on-premises SQL Server functionality and Azure SQL Database; and while the functionality is basically the same (with a few exceptions), there is also a potential for misuse that we want to describe and give recommendation on how to better use this functionality in the Cloud or in mixed environments.
The first thing to notice is that in Azure, the key hierarchy is no longer based on an instance-specific Service Master Key (SMK), instead, the root is a certificate controlled and managed by the Azure SQL Database service, which means that management for the keys is simplified to the database-scoped key hierarchy.
The Master Key (MK) no longer needs to have a password protection. This will simplify applications that are designed to be in the Cloud only, where disaster recovery strategies do not necessarily need to recover the MK using the password.
Another change is that all the syntax that would make a reference to a file or executable file is not supported on the Azure SQL Database version. This includes creating backup of the MK or certificates, restoring backups or importing certificates or asymmetric keys from files.
Additionally, it is worth emphasizing that because the symmetric key & asymmetric key objects cannot be exported, data encrypted or signed can be lost when copied to a different database using the Import/Export (I/E) functionality in SQL Database which is based on logical data movementusing bacpac files and the DACFx API. This limitation does not affect physical data replication scenarios such as backup files, database copy, or Geo-Replication.
The reason for this potential data loss is that for CLE, there is no actual metadata linking a key to the data being protected by it. Such mapping is typically part if the application’s logic and remains transparent to a service such as Import/Export. When exporting data, services or applications that rely on logical data movement such as I/E will successfully copy all the ciphertext data that will likely be stored in varbinary columns, but without the actual key material, this ciphertext will no longer be useful.
To avoid this situation we would strongly recommend using certificates, which can still be extracted and recreated using a binary representation, and symmetric keys using CREATE SYMMETRIC KEY with the KEY_SOURCE & IDENTITY_VALUE fields in such a way that the exact same key can be re-created elsewhere, avoiding a potential data loss.
Example:
Connecting to a V12 Azure SQL Database:
CREATE MASTER KEY
go
CREATE CERTIFICATE [my_cert] WITH SUBJECT = 'demo'
go
declare @cer varbinary(max)
declare @pvk varbinary(MAX)
declare @cmd nvarchar(MAX)
declare @certName sysname = 'my_cert'
declare @pwd sysname = '<<Use a strong password, keep it safe!>>'
-- Extract the public certificate
select @cer = CERTENCODED(cert_id(@certName))
-- Extract the private key, encrypted by the password certificate
select @pvk = CERTPRIVATEKEY(cert_id(@certName), @pwd)
-- Construct the T-SQL statement to recreate the certificate
SET @cmd = 'CREATE CERTIFICATE ' + quotename(@certName) + ' FROM BINARY = ' + sys.fn_varbintohexstr(@cer) +
' WITH PRIVATE KEY ( BINARY = ' + sys.fn_varbintohexstr(@pvk) + ', DECRYPTION BY PASSWORD = ''' + replace(@pwd, '''', '''''') + ''');'
print @cmd
go
declare @signature varbinary(max)
declare @certName sysname = 'my_cert'
declare @cmd nvarchar(MAX)
declare @data nvarchar(256) = 'Some value to sign'
-- Try signing some data & verify the signature on a database where you recreate the certificate
SELECT @signature = SIGNBYCERT(cert_id(@certName), HASHBYTES('SHA2_256', @data))
-- Construct the statement to verify the signature on a database where the cert was recreated
SET @cmd = 'SELECT VERIFYSIGNEDBYCERT(cert_id(''' + replace(@certName, '''', '''''') + '''), HASHBYTES(''SHA2_256'', N''' +
replace (@data, '''', '''''') +'''), '+ sys.fn_varbintohexstr(@signature) +')';
print @cmd
go
-- Create a symmetric key with a key_source & Identity_value arguments in order to be able to recreate the key in a different database
CREATE SYMMETRIC KEY [my_key] WITH ALGORITHM = AES_256,
KEY_SOURCE = '<<This secret will be used to derive the key, keep it secret, keep it safe>>',
IDENTITY_VALUE = '<<This value will be used to derive the key GUID, does not need to be a secret>>'
ENCRYPTION BY CERTIFICATE [my_cert];
GO
-- Encrypt some data and generate a statement that can be decrypted on a different database where the key was recreated
OPEN SYMMETRIC KEY [my_key] DECRYPTION BY CERTIFICATE [my_cert]
go
declare @ciphertext varbinary(max)
declare @certName sysname = 'my_cert'
declare @symkeyName sysname = 'my_key'
declare @symkeyGuid uniqueidentifier
declare @cmd nvarchar(MAX)
declare @data nvarchar(256) = 'Some value to encrypt'
SELECT @symkeyGuid = key_guid(@symkeyName)
SELECT @ciphertext = encryptbykey(@symkeyGuid, @data)
SET @cmd = 'SELECT CAST( decryptbykeyautocert(cert_id(''' + replace(@certName, '''', '''''') + '''), null, ' +
sys.fn_varbintohexstr(@ciphertext) + ') AS nvarchar(256));'
print @cmd
go
CLOSE SYMMETRIC KEY [my_key]
go
Connecting to a different V12 Azure SQL Database or an on-premises SQL Server:
-- 2nd Database (in premise or another SQL Azure DB)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<<Some password for DR>>'
go
-- recreate the certificate from the code generated on the original DB
-- The sample code here should also work, but feel free to replace the values
CREATE CERTIFICATE [my_cert] FROM BINARY = 0x308202aa30820192a003020102021061fcc48bf3dd0c8e427387e4263f3ab0300d06092a864886f70d01010505003011310f300d060355040313066d7963657274301e170d3135303530373230353435325a170d3136303530373230353435325a3011310f300d060355040313066d796365727430820122300d06092a864886f70d01010105000382010f003082010a028201010095df77a32b92c3acad76aa54998f6d24f8460ef0b071f72dfd56bcea0c5e1060026a794e4c08ad8ba3066142bd4f967ff9ed84456d7ea67eadfcbbe6a4becd60ad9bd8887af87eda064c6678399c3ea58627c2549ba3619e8490d902539109c762cbf47fcf66b85bcf3dc504cb437f4e837ee769d86b9c53fa4464f7b38e4066ab0882631a0bc83f36310a49ebb1ebd39799d0669e09c9697c08d38210501a8ebd64bea06f5dc869d5396a7c8b100c4dd96df6857f79c08abf275da207715a4d2e0b3442a206346ed4b18cd54e7bad8575d9f2dc53499ef5cdb683e97856bea4debecdccf2b7f481fe43a7782bea9bf66c92c6417b6e786aa174355091caeb670203010001300d06092a864886f70d0101050500038201010082668b3866e36c95c7ed6debc28f9fd2c5a0e1f48c934c28c32ee6f2e78be45a59529aeafc3e3b0033aecdc53fd62420fa575e18b6e19251231e20df64ba90c3b62c383c5b892f8ebda74be3b3532e388dad079be6d76c8929df8bf02a023f191215cb18d02aa94a4dbb626c6bbeb0bf965d3d433b4ced8cef2dfd457cf367b32945c7c253685d488dccd3030e0329c4ff268abf131e92ac5c3dd406ab20a6a648378f8cf5c98cb64043b85f5d1312ff9bb999f3a7c48bbdf59a591bbe0de4296ec3b27d04d723d8ea8d0bcbe30d7a297513cf7943b764dd7a1ddd0233522aea03b002495e203a2fee28959238ef58991a6236584ec8c3a54709364bf1629620 WITH PRIVATE KEY ( BINARY = 0x1ef1b5b00000000001000000010000001000000094040000c2e2e0609fb9683e64eeef0faee225cf0702000000a40000fe53960114c71cefa5c841ee24aca3bcedf01d4c1f3a2cd16700bedb83831995354b3b2fd9409ffa394c47bbd095bffa4eb4df0af145b2c53c2caf76c731b6a432e92d884d0645af634d32beafb7033b812a8dc8cbd355fe6e542104a61d2a84545ef1126bca1ab7356431794767b19575be77b8488732c350abb4d962e4ef8b298dbc79d634a0e441ef095835bbfce095a2a7c16b76fa86170f05564b0d0fb13c146c168f16be708dbf8396f1f7fe199edb5cdc134f547ed9cfe791598481664e10fbf687528b2e7864b625a15b12a04682ba6a0c4f1087ece53e52c0ef4568fd5200f3a0b289a367fc407549f15f93505dcf69603381f69a0b175c584470e7db8e301249718ee291c43fa8c94f742090ad06fccf538fd673a84b21e23bdd57edebf45717cec9209660e7d977c66c38a945ad0461cc870a1eb50eb94711f96604d4b76666bd7da7fe2ffeb8da7b814278a6c875a95c39ffe4e402b14d4d2b512d0b2f73f1e01d7e51e98b55a77f3afd33ef5d946a138a93b14bcedf6f86ef854dfe19bb88d7eae0366d9c9b0e57bbae50344a7e40acbcc6aeb9c9c44933efd31ac5c4a4e935b6c0e3ad362279868170959deae06f44a1af0d2e8c1e6ae8e7066c71bbdad05951129f6caf13471613e6ce78c5e20e7a792bdbc578c850eaf22c72d18233234178d1f285bff70960029a913280aa9727c485426fc1126e61570bb30b5d552eadf525b6e11d5d89477441c72d160c51e4a782e1b4dfe36fba1e414b13bf3d4b6b1511921546cda12ab8a3a222551fa2caeba0ea5a29ef2df53dd07624a6185b2eef081a123af21d3c0434960c4048609600623910885e64451bd2caa20e13a58016bdf250401935af7ee2e52d02c2af2c44af6c5ef06dca7e1a612528f7545a7d4e36e20d8591bc522611713fa1c1e623a11cd460cdc5cb663ce674a72b3799fcec76a4a63fe0df4f8d64dfc4b304aa27e251f5b7813e1ffe50c3201354231b6cf8ab2237fc45c1eeae7a57364a615f7ef00720ac7589cef8ea31e53f418883a059421f0945f50109e70963acde5b534c49d38cd13c34b6c1271f84dd4ecae04f1eed6a3855b3ff879d10c907d2946a1e62118d9feec28fcd6d7b00c893f565919d82ba54be3734d08a38f8ed2f1e73b5c6b0393ac015f0edf2dcfc8378d078537f7697be07ac0417b3597c1741629b33503779adc3a35c6152fb83b7c20eb3bea780afa4472d993a9ebc858fcb28a63687fe1dfc52449b01a3a19c6885d390635880c32581dbe2cf591e28a20051901da05ba12daecdc5630e41855e43c5991e337f0c81a07e37f21d44622dc50ff5c9c7231e0f77ad957c769411239cf5f245d469ffeb380572c616fed2606dc6ead65fa7268c614cdeb9cb5e164ce9afa944a9b22d5d6fb2840855aa583c294a504cd0d03fa98ebb05d20b37e4cece534dcc3516908b48894cf4048b7e9334eca24a3df527289df41e16462249005cdc3c9e707207f66d52a5ff1318570c0af9a163bbc791dcf239a8aeec3d994c36d2dbb07c0b6dfc77336ea190f367f2361235e8bdfcff7fd928dbfbe6062df8edd6262447605e496fffef30a47c21ba6a365ed0e1ea78cbb0d02d25dccf1a7f99de7c790d0d8785c197, DECRYPTION BY PASSWORD = '<<Use a strong password, keep it safe!>>');
go
SELECT VERIFYSIGNEDBYCERT(cert_id('my_cert'), HASHBYTES('SHA2_256', N'Some value to sign'), 0x8439b62f9e2e10f6f9a5868034b773a020951661169434029ce1ad51f2fb60f6df7872f73d13510713f4584fef8e4dc73eb29c481cbcbd234eb118c8ba8003ae12b512f6be40af54d8bb9ae201a1d480da99532c0fc5f46d537107fd37f3e47d2124612ef1d9a5b7d8a448f1d847f85672235cd05e1a7e8ec1c23ebb4e5d288bfb53cc8639abbfa8ed3f32f2508bcb5b2417297553c567dc410a3c20ad5d471509aaf55a15306fe436c0dbf44c52cb1b7977bd59c22bceb68094879dd61da97eeed47a8b9be997c0b52f0ef57db638c7910c69ac8922f255444a0ed4088b191ed285c5a9cd507a724664714708d5b30552b74e858f8fa99cb50cabd7348dc191)
go
-- recreate the key using the same values as on the original DB
-- The sample code here should also work, but feel free to replace the values
CREATE SYMMETRIC KEY [my_key] WITH ALGORITHM = AES_256,
KEY_SOURCE = '<<This secret will be used to derive the key, keep it secret, keep it safe>>',
IDENTITY_VALUE = '<<This value will be used to derive the key GUID, does not need to be a secret>>'
ENCRYPTION BY CERTIFICATE [my_cert];
GO
-- Decrypt some data using the code generated on the original DB
SELECT CAST( decryptbykeyautocert(cert_id('my_cert'), null, 0x00ba1df9d003f502a8986a10da279efe0100000056e0ec1e2d6b2537a8258c1c990d750a8451aba962dc830dc5a3801e1681b7459e6abeb084854a75eadf0b5adec4fb9950bedddef14214825c82d9fb2f2fa70d27ed1311c58cbe1c0105164780475d1a) AS nvarchar(256));
go
As you can see on the samples above, using CERTIFICATE objects in CLE will give you an opportunity to export & recreate the certificate on any other system for working on scenarios where logical data migration is needed. The same is true when using the KEY_SOURCE & KEY_IDENTITY fields with CREATE SYMMETRIC KEY. Using these techniques will allow you to recreate the same CLE objects on a different database and move data between them without data loss.
If your scenario does not involve any logical data movement (i.e. all data is and will always be in the same database), you may not need to use the techniques shown above to be able to transport the CLE objects (CERTIFCATE & SYMMETRIC KEY) from one database to another; but if you know logical data movement will be necessary, or you are simply not sure, we strongly recommend using CLE with caution and use the techniques shown here to protect against an accidental data loss.