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
    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