(SQL Server) RSA Encrypt/Decrypt AES Key

Demonstrates how to use RSA to protect a key for AES encryption. It can be used in this scenario: You will provide your RSA public key to any number of counterparts. Your counterpart will generate an AES key, encrypt data (or a file) using it, then encrypt the AES key using your RSA public key. Your counterpart sends you both the encrypted data and the encrypted key. Since you are the only one with access to the RSA private key, only you can decrypt the AES key. You decrypt the key, then decrypt the data using the AES key.

This example will show the entire process. (1) Generate an RSA key and save both private and public parts to PEM files. (2) Encrypt a file using a randomly generated AES encryption key. (3) RSA encrypt the AES key. (4) RSA decrypt the AES key. (5) Use it to AES decrypt the file or data.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- ------------------
    -- Step 1.  Generate an RSA key and save to PEM files.

    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Generate a 1024-bit key. 
    DECLARE @success int
    EXEC sp_OAMethod @rsa, 'GenerateKey', @success OUT, 1024
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Keys are exported in XML format:
    DECLARE @pubKeyXml nvarchar(4000)
    EXEC sp_OAMethod @rsa, 'ExportPublicKey', @pubKeyXml OUT

    DECLARE @pubKey int
    -- Use "Chilkat_9_5_0.PublicKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT

    EXEC sp_OAMethod @pubKey, 'LoadFromString', @success OUT, @pubKeyXml
    -- For brevity, we are not checking the return value...
    EXEC sp_OAMethod @pubKey, 'SavePemFile', @success OUT, 0, 'qa_temp/pubKey.pem'

    DECLARE @privKeyXml nvarchar(4000)
    EXEC sp_OAMethod @rsa, 'ExportPrivateKey', @privKeyXml OUT

    DECLARE @privKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadXml', @success OUT, @privKeyXml
    EXEC sp_OAMethod @privKey, 'SavePemFile', @success OUT, 'qa_temp/privKey.pem'

    -- Other methods exist for saving the private key in PKCS8 format,
    -- both encrypted and un-encrypted..

    -- ------------------
    -- Step 2.   This is the code your counterpart will run to 
    -- AES encrypt a file.  It generates a random AES key
    -- to use for the encryption.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256

    -- Generate random bytes and return the random bytes
    -- in a hex string:
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
    DECLARE @randomKey nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'GenRandomBytesENC', @randomKey OUT, 32

    PRINT 'AES key = ' + @randomKey

    -- Set the key.
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @randomKey, 'hex'

    -- Set the IV to a known value that will be used on both sides.
    -- (If desired, you could generate a random IV and protect it in the same
    -- way as the key...)
    -- The length of the IV for AES is always 16 bytes, regardless of the key size.
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'

    -- AES Encrypt the file (the file may be any size because it will
    -- stream the file in/out.
    EXEC sp_OAMethod @crypt, 'CkEncryptFile', @success OUT, 'qa_data/hamlet.xml', 'qa_temp/aesEncrypted.dat'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @pubKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- ------------------
    -- Step 3.
    -- ------------------
    -- RSA encrypt the AES key.
    -- We'll pretend  your counter part has the public-key PEM file and needs
    -- to load it.  Therefore, we'll start with a new RSA object and load
    -- the public key from the PEM..
    DECLARE @rsa2 int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa2 OUT

    DECLARE @pubKey2 int
    -- Use "Chilkat_9_5_0.PublicKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey2 OUT

    -- For brevity, don't check the success..  
    EXEC sp_OAMethod @pubKey2, 'LoadFromFile', @success OUT, 'qa_temp/pubKey.pem'
    EXEC sp_OAMethod @pubKey2, 'GetXml', @pubKeyXml OUT
    EXEC sp_OAMethod @rsa2, 'ImportPublicKey', @success OUT, @pubKeyXml

    -- RSA encrypt the AES key and return it as a base64 encoded string.
    EXEC sp_OASetProperty @rsa2, 'EncodingMode', 'base64'
    DECLARE @bUsePrivateKey int
    SELECT @bUsePrivateKey = 0
    DECLARE @encryptedAesKey nvarchar(4000)
    EXEC sp_OAMethod @rsa2, 'EncryptStringENC', @encryptedAesKey OUT, @randomKey, @bUsePrivateKey

    -- At this point, your counterpart sends you the encryptedAesKey,
    -- and the encrypted file.

    -- ------------------
    -- Step 4 - RSA decrypt the AES key.
    -- ------------------

    -- Start with a new RSA object and load the private key from the PEM.
    DECLARE @rsa3 int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa3 OUT

    DECLARE @privKey2 int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey2 OUT

    EXEC sp_OAMethod @privKey2, 'LoadPemFile', @success OUT, 'qa_temp/privKey.pem'
    EXEC sp_OAMethod @privKey2, 'GetXml', @privKeyXml OUT
    EXEC sp_OAMethod @rsa3, 'ImportPrivateKey', @success OUT, @privKeyXml

    -- The encrypted AES key is encoded using base64, so set
    -- our EncodingMode to "base64".
    EXEC sp_OASetProperty @rsa3, 'EncodingMode', 'base64'

    SELECT @bUsePrivateKey = 1
    DECLARE @decryptedAesKey nvarchar(4000)
    EXEC sp_OAMethod @rsa3, 'DecryptStringENC', @decryptedAesKey OUT, @encryptedAesKey, @bUsePrivateKey

    PRINT 'decryptedAesKey = ' + @decryptedAesKey

    -- ------------------
    -- Step 5 - AES decrypt the file.
    -- ------------------

    DECLARE @crypt2 int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt2 OUT

    EXEC sp_OASetProperty @crypt2, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt2, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt2, 'KeyLength', 256

    -- Set the key.
    EXEC sp_OAMethod @crypt2, 'SetEncodedKey', NULL, @decryptedAesKey, 'hex'

    -- Set the IV 
    EXEC sp_OAMethod @crypt2, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'

    -- AES Decrypt the file (the file may be any size because it will
    -- stream the file in/out.
    EXEC sp_OAMethod @crypt2, 'CkDecryptFile', @success OUT, 'qa_temp/aesEncrypted.dat', 'qa_temp/decrypted.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @pubKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @rsa2
        EXEC @hr = sp_OADestroy @pubKey2
        EXEC @hr = sp_OADestroy @rsa3
        EXEC @hr = sp_OADestroy @privKey2
        EXEC @hr = sp_OADestroy @crypt2
        RETURN
      END

    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @pubKey
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @rsa2
    EXEC @hr = sp_OADestroy @pubKey2
    EXEC @hr = sp_OADestroy @rsa3
    EXEC @hr = sp_OADestroy @privKey2
    EXEC @hr = sp_OADestroy @crypt2


END
GO