SQL AES/RSA Encryption
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
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
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
- https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/functions/cryptographic-functions-transact-sql?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
- https://en.wikipedia.org/wiki/Diffie%E2%80%93Hellman_key_exchange
- https://en.wikipedia.org/wiki/Digital_signature
- https://en.wikipedia.org/wiki/Elliptic_Curve_Digital_Signature_Algorithm
- https://en.wikipedia.org/wiki/Post-quantum_cryptography
- https://www.ssl.com/article/comparing-ecdsa-vs-rsa/
- https://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/
- https://www.mssqltips.com/sqlservertip/1886/sql-server-encryption-symmetric-vs-asymmetric-keys/ -https://stackoverflow.com/questions/2822592/how-to-get-compatibility-between-c-sharp-and-sql2k8-aes-encryption
- https://www.codemahek.com/blog/execute-c-methods-from-sql-server
- http://seesharpdeveloper.blogspot.com/2016/06/calling-c-method-from-sql-server.html
