SQL Server

openssl smime -encrypt -des3 -in <file> <pem file>

See more OpenSSL Examples

OpenSSL SMIME encrypt file using PEM containing a certificate. 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 @iTmp0 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Load the cert from a PEM file.

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

    -- Our particular PEM was not encrypted, so we pass an empty password.
    -- Also, a private key is not needed for encryption.  The PEM used to test this example
    -- happens to have a private key, but it's not actually used.
    EXEC sp_OAMethod @pem, 'LoadPemFile', @success OUT, 'qa_data/openssl/rsaCertAndKey.pem', ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pem
        RETURN
      END

    EXEC sp_OAGetProperty @pem, 'NumCerts', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'PEM does not contain any certificates.'
        EXEC @hr = sp_OADestroy @pem
        RETURN
      END

    DECLARE @cert int
    EXEC sp_OAMethod @pem, 'GetCert', @cert OUT, 0
    EXEC sp_OAGetProperty @pem, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @pem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pem
        RETURN
      END

    -- -------------------------------------------------------------------------------------
    -- Duplicate this OpenSSL command:   openssl smime -encrypt -des3 -in <file> <pem file>
    -- -------------------------------------------------------------------------------------
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OAMethod @crypt, 'SetEncryptCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    EXEC @hr = sp_OADestroy @cert

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'PKI'
    EXEC sp_OASetProperty @crypt, 'Pkcs7CryptAlg', '3des'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 168

    -- Load the file to be encrypted.
    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/openssl/hello.txt'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load the input file.'
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    -- Encrypt.
    EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @bd
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    -- The openssl smime -encrypt command produces encrypte MIME such as this:

    -- MIME-Version: 1.0
    -- Content-Disposition: attachment; filename="smime.p7m"
    -- Content-Type: application/x-pkcs7-mime; smime-type=enveloped-data; name="smime.p7m"
    -- Content-Transfer-Encoding: base64
    -- 
    -- MIICzwYJKoZIhvcNAQcDoIICwDCCArwCAQAxggF8MIIBeAIBADBgMEoxCzAJBgNVBAYTAlVTMRYw
    -- FAYDVQQKEw1MZXQncyBFbmNyeXB0MSMwIQYDVQQDExpMZXQncyBFbmNyeXB0IEF1dGhvcml0eSBY
    -- MwISA9Nqgb1dH/XTDNeKzN1nR85iMA0GCSqGSIb3DQEBAQUABIIBAIQqPexjxWovgxwKV/r3HL/U
    -- EP9Yozvz5hBeX5VvRZjKSi4FRw5wapElPK+4FB82hiAR9Mi3c16PvPSVkJv3l78Mv5uaaOs/OmUz
    -- mIHFB6Z+l2E52BDmUVWJZTQ09vdWy6+NIRlg2R9Z1NkmZ4BZCJk6mHB/Yx03IaOxK8LnwieDMthM
    -- SvxbhJnIOISN7k7ofs+/0vTXUpdQ+tlmwyVySMGQ6VMk+z4sqZJ2stacqCPtt/aiSwJ9p0OKmihf
    -- 3KDJceXJtavIQeA97yz1LqPvle35mmd5sBhV9qQYdTV/KJ+YM5uEZ9BHYbvMJbADFHwKzhcEY3qA
    -- 7T9acmEsb7NycOIwggE1BgkqhkiG9w0BBwEwFAYIKoZIhvcNAwcECERX/ZoHweSkgIIBEMmCMx49
    -- zjVAnGqRaBbvzQT1hg0uQSxIJjxMxC+HSuM+eY9oSOsbrw4uIijHKH9NdOpeDsdRzg2z5EBM7AlP
    -- Ht9DyPW5C2deV6RPX4F8gyExz+JUXrd+3Yb3AKTdpDkTWDmNCeO0r/YSqp518+mfU5hG8e336u51
    -- HAM44FeknA8oThWsD/wUB1e8vzsatK4UXW/KSu/166V7z+VT86kd+IHa7t60U9Yp0ZXgcM5Pb5Ni
    -- 69Qc5MKPzom2801H5UR/WjCgsxOIjOj49sKisjRy79skrJzxY5ZG05T0dKn6KC3TjRpIEEeOyhCd
    -- Nm2Y7dcW8GLMepdhWay5vePmQxmvmhbAtBprIem14NcrYeG6D5wP

    -- We have the body in bd
    -- Construct the header and base64 body...
    DECLARE @sbEncryptedMime int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbEncryptedMime OUT

    EXEC sp_OAMethod @sbEncryptedMime, 'AppendLine', @success OUT, 'MIME-Version: 1.0', 1
    EXEC sp_OAMethod @sbEncryptedMime, 'AppendLine', @success OUT, 'Content-Disposition: attachment; filename="smime.p7m"', 1
    EXEC sp_OAMethod @sbEncryptedMime, 'AppendLine', @success OUT, 'Content-Type: application/x-pkcs7-mime; smime-type=enveloped-data; name="smime.p7m"', 1
    EXEC sp_OAMethod @sbEncryptedMime, 'AppendLine', @success OUT, 'Content-Transfer-Encoding: base64', 1
    EXEC sp_OAMethod @sbEncryptedMime, 'AppendLine', @success OUT, '', 1
    EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    EXEC sp_OAMethod @sbEncryptedMime, 'AppendLine', @success OUT, @sTmp0, 1

    -- Show the result.
    EXEC sp_OAMethod @sbEncryptedMime, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- or save to a file..
    EXEC sp_OAMethod @sbEncryptedMime, 'WriteFile', @success OUT, 'qa_output/encryptedMime.txt', 'utf-8', 0

    EXEC @hr = sp_OADestroy @pem
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @sbEncryptedMime


END
GO