SQL Server

AES Encryption ECB Mode with PKCS7 Padding

See more Encryption Examples

Duplicates the following C# code:

public static byte[] DecryptBySymmetricKey(string encryptedText, byte[] key)
  {
     string keyAsBase64 = Convert.ToBase64String(key);

     byte[] dataToDecrypt = Convert.FromBase64String(encryptedText);
     var keyBytes = key;
     AesManaged tdes = new AesManaged();
     tdes.KeySize = 256;
     tdes.BlockSize = 128;
     tdes.Key = keyBytes;
     tdes.Mode = CipherMode.ECB;
     tdes.Padding = PaddingMode.PKCS7;
     ICryptoTransform decrypt__1 = tdes.CreateDecryptor();
     byte[] deCipher = decrypt__1.TransformFinalBlock(dataToDecrypt, 0, dataToDecrypt.Length);
     tdes.Clear();
     string EK_result = Convert.ToBase64String(deCipher);
     return EK_result;
}

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
    -- This example requires 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

    -- In the C# code above that is to be duplicated here, use the base64 encoded key.
    DECLARE @keyAsBase64 nvarchar(4000)
    SELECT @keyAsBase64 = '...'
    DECLARE @encryptedBytesAsBase64 nvarchar(4000)
    SELECT @encryptedBytesAsBase64 = '....'

    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'AES'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'ecb'
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @keyAsBase64, 'base64'

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    -- Pass the base64 representation of the encrypted data.
    -- (The EncodingMode indicates you are passing base64.)
    DECLARE @EK_result nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @EK_result OUT, @encryptedBytesAsBase64


    PRINT @EK_result

    EXEC @hr = sp_OADestroy @crypt


END
GO