SQL Server

AES GCM Encrypt and Decrypt a File

See more Encryption Examples

Demonstrates how to AES GCM encrypt and decrypt a file. SQL Server

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Set the encryption algorithm to "AES"	
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'

    -- Indicate that the Galois/Counter Mode (GCM) should be used:
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'gcm'

    -- KeyLength may be 128, 192, 256
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256

    -- This is the 256-bit AES secret key (in hex format)
    DECLARE @K nvarchar(4000)
    SELECT @K = '000102030405060708090A0B0C0D0E0F000102030405060708090A0B0C0D0E0F'

    -- This is the 16-byte initialization vector (in hex format)
    DECLARE @IV nvarchar(4000)
    SELECT @IV = '000102030405060708090A0B0C0D0E0F'

    -- This is the OPTIONAL additional data (in hex format) to be used as input to the GCM AEAD algorithm,
    -- but is not included in the output.  It plays a role in the computation of the
    -- resulting authenticated tag.
    DECLARE @AAD nvarchar(4000)
    SELECT @AAD = 'feedfacedeadbeeffeedfacedeadbeefabaddad2'

    -- Set the secret key and IV
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @IV, 'hex'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @K, 'hex'

    -- Set the additional authenticated data (AAD)
    EXEC sp_OAMethod @crypt, 'SetEncodedAad', @success OUT, @AAD, 'hex'

    -- Encrypt a file.
    DECLARE @inFile nvarchar(4000)
    SELECT @inFile = 'qa_data/hamlet.xml'
    DECLARE @outFile nvarchar(4000)
    SELECT @outFile = 'c:/temp/qa_output/hamlet_aes_gcm.enc'
    EXEC sp_OAMethod @crypt, 'CkEncryptFile', @success OUT, @inFile, @outFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- Get the authentication tag in hex format
    DECLARE @authTag nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'GetEncodedAuthTag', @authTag OUT, 'hex'

    PRINT 'authentication tag = ' + @authTag

    -- Decrypt..

    -- Before decrypting, you must provide the expected authentication tag.
    -- The decrypt will fail if the resulting authentication tag computed while decrypting is not equal to the
    -- expected authentication tag.
    EXEC sp_OAMethod @crypt, 'SetEncodedAuthTag', @success OUT, @authTag, 'hex'

    SELECT @inFile = @outFile
    SELECT @outFile = 'c:/temp/qa_output/hamlet_restored.xml'
    EXEC sp_OAMethod @crypt, 'CkDecryptFile', @success OUT, @inFile, @outFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END


    PRINT 'Success.'

    -- --------------------------------------------------------------------------------------------
    -- About AES-GCM:

    -- AES-GCM (Advanced Encryption Standard - Galois/Counter Mode) is a widely-used
    -- encryption mode that provides both confidentiality (encryption) and
    -- integrity/authentication (data integrity verification) in one operation. It is
    -- commonly used in secure communications due to its efficiency and strong security
    -- properties.
    -- 
    -- Key Concepts:
    -- 
    --     AES (Advanced Encryption Standard):
    -- 
    --         AES is a symmetric encryption algorithm, meaning the same key is used
    --         for both encryption and decryption.
    -- 
    --         It operates on fixed-size blocks of data (128 bits) using key sizes of
    --         128, 192, or 256 bits.
    -- 
    --         In AES-GCM, AES is used to perform the actual data encryption.
    -- 
    --     GCM (Galois/Counter Mode):
    -- 
    --         Counter Mode (CTR): GCM uses counter mode for encryption. In this mode,
    --         a nonce (or initialization vector, IV) and a counter are combined and encrypted
    --         with AES. The result is XORed with the plaintext to produce the ciphertext. 
    -- 
    --         Galois Mode (GMAC): GCM also includes an authentication mechanism based
    --         on a Galois field. It generates an authentication tag, which ensures the
    --         integrity of both the ciphertext and any additional data (called AAD -
    --         Additional Authenticated Data). This tag is verified during decryption to ensure
    --         that the data hasn't been tampered with.
    -- 
    -- Key Features:
    -- 
    --     Confidentiality (Encryption):
    -- 
    --         The plaintext is encrypted using AES in counter mode. Each block of
    --         plaintext is XORed with the output of AES applied to a combination of the IV and
    --         an incremented counter.
    -- 
    --     Integrity (Authentication):
    -- 
    --         In addition to encryption, GCM provides authentication for both the
    --         encrypted data (ciphertext) and any Additional Authenticated Data (AAD), such as
    --         headers or metadata that need to be protected but not encrypted.
    -- 
    --         The authentication tag is generated using a Galois field multiplication
    --         of the ciphertext and AAD. This ensures that any changes to the encrypted
    --         message or the AAD will be detected during decryption.
    -- 
    -- Key Components:
    -- 
    --     - Plaintext: The data you want to encrypt.
    --     - Ciphertext: The encrypted data.
    --     - Key: A symmetric key used for both encryption and decryption.
    --     - Nonce/IV: A unique value used for each encryption to ensure security. It is not secret but should never be reused with the same key.
    --     - AAD (Additional Authenticated Data): Optional data that is not encrypted but needs to be authenticated (e.g., headers).
    --     - Authentication Tag: A tag generated to verify the integrity and authenticity of the ciphertext and AAD

    EXEC @hr = sp_OADestroy @crypt


END
GO