# Encryption by certificate
CREATE CERTIFICATE My_New_Cert FROM FILE = 'D:\Temp\CertTest\certificateDER.cer' GO
Create the certificate
SELECT EncryptByCert(Cert_ID('My_New_Cert'), 'This text will get encrypted') encryption_test
Usually, you would encrypt with a symmetric key, that key would get encrypted by the asymmetric key (public key) from your certificate.
Also, note that encryption is limited to certain lengths depending on key length and returns NULL otherwise. Microsoft writes: "The limits are: a 512 bit RSA key can encrypt up to 53 bytes, a 1024 bit key can encrypt up to 117 bytes, and a 2048 bit key can encrypt up to 245 bytes."
EncryptByAsymKey has the same limits. For UNICODE this would be divided by 2 (16 bits per character), so 58 characters for a 1024 bit key.
# Encryption of database
USE TDE CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE My_New_Cert GO ALTER DATABASE TDE SET ENCRYPTION ON GO
This uses 'Transparent Data Encryption' (TDE)
# Encryption by symmetric key
-- Create the key and protect it with the cert CREATE SYMMETRIC KEY My_Sym_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE My_New_Cert; GO -- open the key OPEN SYMMETRIC KEY My_Sym_Key DECRYPTION BY CERTIFICATE My_New_Cert; -- Encrypt SELECT EncryptByKey(Key_GUID('SSN_Key_01'), 'This text will get encrypted');
# Encryption by passphrase
SELECT EncryptByPassphrase('MyPassPhrase', 'This text will get encrypted')
This will also encrypt but then by passphrase instead of asymmetric(certificate) key or by an explicit symmetric key.
| ||For CREATE CERTIFICATE, a private key can be specified: |
Creation of a DER certificate will work fine. When a Base64 certificate is used however, SQL server will complain with the cryptic message:
Msg 15468, Level 16, State 6, Line 1 An error occurred during the generation of the certificate.
Import your Base64 certificate to your OS's certificate store to be able to re-export it into DER binary format.
Another important thing to do is having an Encryption Hierarchy so that one protects the other, all the way to OS level. See the article on 'Encryption of database/TDE'
For more information for creation of certificates go to: https://msdn.microsoft.com/en-us/library/ms187798.aspx (opens new window)
For more information for encryption of database/TDE go to: https://msdn.microsoft.com/en-us/library/bb934049.aspx (opens new window)
For more information for encryption of data go to: https://msdn.microsoft.com/en-us/library/ms188061.aspx (opens new window)