Code Chronicles
SQL AES/RSA Encryption

SQL AES/RSA Encryption

Jeff Tong's photo
Jeff Tong
·

4 min read

In situations where a private key is compromised and a new key is created for future use, the existing encrypted data must be decrypted and re-encrypted.

Starting with SQL 2005, common standard cryptographic algorithms are supported (AES/RSA). Instead of writing a tool to rotate they values, it would be more efficient to write a stored procedure to leverage this feature and operate on the table values directly as long as the database is not being constantly accessed. If database performance is a factor, it is better to write a separate utility where the encryption/decryption CPU and I/O factors can be offloaded from the database server at the cost of multiple connections back to the DB.

Symmetric Encryption

  • One key used to encrypt and decrypt.
  • Better suited if only one party is encrypting/decrypting.
  • Has lower resource cost than asymmetric encryption.

Create Master Key

Create a DB master key and certificate to access the symmetric key store.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong_password'

CREATE CERTIFICATE my_symmetric_cert_name WITH SUBJECT = 'Description of certificate'

Create Symmetric Key

Details 

CREATE SYMMETRIC KEY my_symmetric_key_name 
WITH
IDENTITY_VALUE = 'some_value',
ALGORITHM = AES_256,
KEY_SOURCE = 'passphrase'
ENCRYPTION BY CERTIFICATE my_symmetric_cert_name;

Note a temporary symmetric key can be generated with local temporary table (#my_symmetric_key_name).

Encryption

Open the created key and encrypt with it:

OPEN SYMMETRIC KEY my_symmetric_key_name DECRYPTION BY CERTIFICATE my_symmetric_cert_name 

DECLARE @ciphertext VARBINARY(256) 
SET @ciphertext = EncryptByKey(Key_GUID('my_symmetric_key_name'), 'your_plain_text')

The 256 is in line with the encryption algorithm of AES_256. The 'ciphertext' can be converted to VARCHAR if needed. Pay special attention to the style parameter.

SELECT CONVERT(NVARCHAR(max), @ciphertext , 2);

Decryption

Open the created key and decrypt with it if 'ciphertext' is datatype of VARBINARY:

OPEN SYMMETRIC KEY my_symmetric_key_name DECRYPTION BY CERTIFICATE my_symmetric_cert_name 

DECLARE @plaintext VARCHAR(max)
SET @plaintext = DecryptByKey(@ciphertext)

If @ciphertext is VARCHAR:

DECLARE @ciphertext VARCHAR(MAX) = '0050B63F362F9A1C9222474C0605CD7B020000002D15C1D93616F99303186741391762EFF59206A066298D9CC435088FDA07BAD77C899FAA369E15A91A9A4BE03E739ADC'

OPEN SYMMETRIC KEY PaymentTokenKey DECRYPTION BY CERTIFICATE PaymentTokenCert
DECLARE @ciphertextVarBinary VARBINARY(max) = CONVERT(VARBINARY(MAX), @ciphertext, 2)
DECLARE @plaintext VARCHAR(max) = CONVERT(VARCHAR(max), DecryptByKey(@ciphertextVarBinary), 0)
SELECT @ciphertextVarBinary AS Ciphertext_AS_VARBINARY, @plaintext AS PlainText

Asymmetric Encryption

  • Public key used to encrypt.
  • Private key can be used to decrypt and sign messages.
  • Newer and more secure due to two separate keys.
  • Better suited for multiple parties that require encryption.
  • Has higher resource/performance cost than symmetric encryption.

Create Asymmetric Key

Details 

CREATE ASYMMETRIC KEY my_asymmetric_key_name
WITH
ALGORITHM = RSA_4096,
KEY_SOURCE = 'passphrase'
ENCRYPTION BY PASSWORD = 'your_password';

Encryption

DECLARE @ciphertext VARBINARY(8000) 
SET @ciphertext = EncryptByAsymKey(AsymKey_ID('my_asymmetric_key_name'), 'your_plain_text')

Decryption

DECLARE @decryptedValue = VARBINARY(8000)

@decryptedValue = DecryptByAsymKey( AsymKey_Id('my_asymmetric_key_name'), @ciphertext, N'your_password')

SELECT Convert(NVARCHAR(max)), @decryptedValue)

A login that has read permission will not be able to decrypt the data.

Helpful Queries

List all Keys:

SELECT * FROM sys.symmetric_keys;

Understanding style parameter for CONVERT and VARBINARY:

DECLARE @binaryValue VARBINARY(256) = (select cast('Some_String' as VARBINARY(256)))

SELECT @binaryValue as BinaryValue
    , CONVERT(VARCHAR(256), @binaryValue, 2) as VARBINARY_AS_String
    , CONVERT(VARCHAR(256), @binaryValue, 1) as BinaryValue2
    , CONVERT(VARCHAR(256), @binaryValue, 0) as Literal

Storing Base64 Encoded String as VARBINARY:

DECLARE @base64EncodedString VARCHAR(MAX) = 'SV9BTUFfQkFTRTY0X0RFQ09ERURfU1RSSU5H';
DECLARE @varbinaryBase64DecodedString VARBINARY(MAX)
SELECT @varbinaryBase64DecodedString = CAST(N'' as xml).value('xs:base64Binary(sql:variable("@base64EncodedString"))', 'VARBINARY(MAX)')
DECLARE @varcharBase64DecodedString VARCHAR(MAX)
SELECT @varcharBase64DecodedString = CONVERT(VARCHAR(MAX), @varbinaryBase64DecodedString)
SELECT @varbinaryBase64DecodedString AS VARBINARY_TYPE, @varcharBase64DecodedString AS VARCHAR_TYPE

Learnings

See the documentation on the various options for mechanisms for encryption/decryption via SQL Server.

  • The .NET AES encryption is not the same as the SQL Server AES encryption since there is additional metadata in the VARBINARY type.

  • C# libraries can be run on SQL Server

  • Asymmetric cryptographic algorithms will typically have higher overhead than symmetric ones.

  • ECDSA (Elliptic Curve Digital Signature Algorithm) provides the same level of security with shorter key lengths.

  • Both RSA and ECDSA will be vulnerable to quantum computers .

  • ECDSA is easier to solve, by quantum computers, compared to RSA according to this paper by Microsoft.

References

SQL ServerCryptographyencryption