SQL Server

Duplicate OpenSSL smime Command

See more OpenSSL Examples

Demonstrates how to duplicate an OpenSSL smime command to sign a text file using cert + key. 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example duplicates the following OpenSSL command:
    -- 
    -- smime -sign -in helloWorld.txt -signer cert.pem -inkey my.key -passin pass:test1234 -outform PEM -out helloWorldSigned.pem

    DECLARE @certPath nvarchar(4000)
    SELECT @certPath = 'qa_data/cert.pem'
    DECLARE @privateKeyPath nvarchar(4000)
    SELECT @privateKeyPath = 'qa_data/my.key'
    DECLARE @password nvarchar(4000)
    SELECT @password = 'test1234'
    DECLARE @dataFile nvarchar(4000)
    SELECT @dataFile = 'qa_data/helloWorld.txt'

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

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, @certPath
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, @privateKeyPath, @password
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, @dataFile
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load ' + @dataFile
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OAMethod @crypt, 'SetSigningCert2', @success OUT, @cert, @privKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @bd
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    EXEC sp_OAMethod @crypt, 'OpaqueSignBd', @success OUT, @bd
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @bd
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    DECLARE @sbPem int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPem OUT

    EXEC sp_OAMethod @sbPem, 'AppendLine', @success OUT, '-----BEGIN PKCS7-----', 1
    EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    EXEC sp_OAMethod @sbPem, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbPem, 'AppendLine', @success OUT, '-----END PKCS7-----', 1
    EXEC sp_OAMethod @sbPem, 'WriteFile', @success OUT, 'helloWorldSigned.pem', 'utf-8', 0

    EXEC sp_OAMethod @sbPem, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output:

    -- -----BEGIN PKCS7-----
    -- MIINxAYJKoZIhvcNAQcCoIINtTCCDbECAQExCzAJBgUrDgMCGgUAMBoGCSqGSIb3DQEHAaANBAtI
    -- ZWxsbyBXb3JsZKCCC0owggYQMIID+KADAgECAhBNlCwQ1DvglAnFgS06KwZPMA0GCSqGSIb3DQEB
    -- DAUAMIGIMQswCQYDVQQGEwJVUzETMBEGA1UECBMKTmV3IEplcnNleTEUMBIGA1UEBxMLSmVyc2V5
    -- IENpdHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29yazEuMCwGA1UEAxMlVVNFUlRydXN0
    -- IFJTQSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTAeFw0xODExMDIwMDAwMDBaFw0zMDEyMzEyMzU5
    -- NTlaMIGWMQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQH
    -- EwdTYWxmb3JkMRgwFgYDVQQKEw9TZWN0aWdvIExpbWl0ZWQxPjA8BgNVBAMTNVNlY3RpZ28gUlNB
    -- IENsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBMIIBIjANBgkqhkiG9w0B
    -- AQEFAAOCAQ8AMIIBCgKCAQEAyjztlApB/975Rrno1jvm2pK/KxBOqhq8gr2+JhwpKirSzZxQgT9t
    -- lC7zl6hn1fXjSo5MqXUfItMltrMaXqcESJuK8dtK56NCSrq4iDKaKq9NxOXFmqXX2zN8HHGjQ2b2
    -- Xv0v1L5Nk1MQPKA19xeWQcpGEGFUUd0kN+oHox+L9aV1rjfNiCj3bJk6kJaOPabPi2503nn/ITX5
    -- e8WfPnGw4VuZ79Khj1YBrf24k5Ee1sLTHsLtpiK9OjG4iQRBdq6Z/TlVx/hGAez5h36bBJMxqdHL
    -- pdwIUkTqT8se3ed0PewDch/8kHPo5fZl5u1B0ecpq/sDN/5sCG52Ds+QU5O5EwIDAQABo4IBZDCC
    -- AWAwHwYDVR0jBBgwFoAUU3m/WqorSs9UgOHYm8Cd8rIDZsswHQYDVR0OBBYEFAnA8vwL2pTbX/4r
    -- 36iZQs/J4K0AMA4GA1UdDwEB/wQEAwIBhjASBgNVHRMBAf8ECDAGAQH/AgEAMB0GA1UdJQQWMBQG
    -- CCsGAQUFBwMCBggrBgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwUAYDVR0fBEkwRzBFoEOgQYY/
    -- aHR0cDovL2NybC51c2VydHJ1c3QuY29tL1VTRVJUcnVzdFJTQUNlcnRpZmljYXRpb25BdXRob3Jp
    -- dHkuY3JsMHYGCCsGAQUFBwEBBGowaDA/BggrBgEFBQcwAoYzaHR0cDovL2NydC51c2VydHJ1c3Qu
    -- Y29tL1VTRVJUcnVzdFJTQUFkZFRydXN0Q0EuY3J0MCUGCCsGAQUFBzABhhlodHRwOi8vb2NzcC51
    -- c2VydHJ1c3QuY29tMA0GCSqGSIb3DQEBDAUAA4ICAQBBRHUAqznCFfXejpVtMnFojADdF9d6HBA4
    -- kMjjsb0XMZHztuOCtKF+xswhh2GqkW5JQrM8zVlU+A2VP72Ky2nlRA1GwmIPgou74TZ/XTarHG8z
    -- dMSgaDrkVYzz1g3nIVO9IHk96VwsacIvBF8JfqIs+8aWH2PfSUrNxP6Ys7U0sZYx4rXD6+cqFq/Z
    -- W5BUfClN/rhk2ddQXyn7kkmka2RQb9d90nmNHdgKrwfQ49mQ2hWQNDkJJIXwKjYA6VUR/fZUFeCU
    -- isdDe/0ABLTI+jheXUV1eoYV7lNwNBKpeHdNuO6Aacb533JlfeUHxvBz9OfYWUiXu09sMAviM11Q
    -- 0DuMZ5760CdO2VnpsXP4KxaYIhvqPqUMWqRdWyn7crItNkZeroXaecG03i3mM7dkiPaCkgocBg0E
    -- BYsbZDZ8bsG3a08LwEsL1Ygz3SBsyECa0waq4hOf/Z85F2w2ZpXfP+w8q4ifwO90SGZZV+HR/Jh6
    -- rEaVPDRF/CEGVqR1hiuQOZ1YL5ezMTX0ZSLwrymUE0pwi/KDaiYB15uswgeIAcA6JzPFf9pLkAFF
    -- Ws1QNyN++niFhsM47qodx/PL+5jR87myx5uYdBEQkkDc+lKB1Wct6ucXqm2EmsaQ0M95QjTmy+rD
    -- WjkDYdw3Ms6mSWE3Bn7i5ZgtwCLXgAIe5W8mybM2JzCCBTIwggQaoAMCAQICEQDwUrMy4rq9PIFH
    -- eb0WvDbOMA0GCSqGSIb3DQEBCwUAMIGWMQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBN
    -- YW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRgwFgYDVQQKEw9TZWN0aWdvIExpbWl0ZWQxPjA8
    -- BgNVBAMTNVNlY3RpZ28gUlNBIENsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWls
    -- IENBMB4XDTE5MDMyMjAwMDAwMFoXDTIwMDMyMTIzNTk1OVowKDEmMCQGCSqGSIb3DQEJARYXc3Vw
    -- cG9ydEBjaGlsa2F0c29mdC5jb20wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQC3Y+NL
    -- mfKmb5tkrlU8z0B4Nfbe09jivGC9+OLz9Hhvcn1xmHkEtunHFC2xZOOuTIbucKrJdlGmlSsztHdo
    -- Uq6btS/7e3j0gk3OJ8P+VhL638rbgE7BFjwuDKkYhSsItwNJdoi3omA4kfnfITqp/GDU3618miIU
    -- YFGdkZ0yhIofQoyzFEqdszG35d5rxNuSaw6pQG/NX2vtvLFuJ3zebvvZqs9JAIfDaVJ2OPfsyiCs
    -- OWsvvC6Mv7ZuLnKi5ZbBJQEhxGI1t9fgOjResLbQXegKJdsh5z9BuocVmOHyYlj16aQHI3PpNY46
    -- 5WB0rrxTiFrjrj8KScNM/Yf0UISmb86ZAgMBAAGjggHmMIIB4jAfBgNVHSMEGDAWgBQJwPL8C9qU
    -- 21/+K9+omULPyeCtADAdBgNVHQ4EFgQUp6kiYgCk7i8Fy9TkPf9yudOdRW4wDgYDVR0PAQH/BAQD
    -- AgWgMAwGA1UdEwEB/wQCMAAwIAYDVR0lBBkwFwYIKwYBBQUHAwQGCysGAQQBsjEBAwUCMBEGCWCG
    -- SAGG+EIBAQQEAwIFIDBABgNVHSAEOTA3MDUGDCsGAQQBsjEBAgEBATAlMCMGCCsGAQUFBwIBFhdo
    -- dHRwczovL3NlY3RpZ28uY29tL0NQUzBaBgNVHR8EUzBRME+gTaBLhklodHRwOi8vY3JsLnNlY3Rp
    -- Z28uY29tL1NlY3RpZ29SU0FDbGllbnRBdXRoZW50aWNhdGlvbmFuZFNlY3VyZUVtYWlsQ0EuY3Js
    -- MIGKBggrBgEFBQcBAQR+MHwwVQYIKwYBBQUHMAKGSWh0dHA6Ly9jcnQuc2VjdGlnby5jb20vU2Vj
    -- dGlnb1JTQUNsaWVudEF1dGhlbnRpY2F0aW9uYW5kU2VjdXJlRW1haWxDQS5jcnQwIwYIKwYBBQUH
    -- MAGGF2h0dHA6Ly9vY3NwLnNlY3RpZ28uY29tMCIGA1UdEQQbMBmBF3N1cHBvcnRAY2hpbGthdHNv
    -- ZnQuY29tMA0GCSqGSIb3DQEBCwUAA4IBAQDHuPFffssUPGEnKOWPgBhABEzFKSWq8plEyPXraFdY
    -- l4LBMm4uIpkUXsKdHyAu7m66CsNQR8RYN0xITVlyKVCaCu/bv1vLiPIEX6SF26HkQRlj4J4Amu7r
    -- RcrNGhgQ/ofgihEwiVBIze3uvi5zY+QZwmRFikdHrUhWx3qQYG9sW42U0eN+CULbz4QHt3+4xRCq
    -- YHbfazG4pK2ukI7xKI1sjxs5237fmdYhqUkH0S+0I708zv10/jLN6Ev8r6Q0ItwRh4gt5dTC8qPK
    -- EmGQs/8v1etTdHzRwWUofOyzgkykGg4MdvFkC8hJ4Hinhpg3Y1vdjX6f3pZPsipKwB+MdYSIMYIC
    -- MzCCAi8CAQEwgawwgZYxCzAJBgNVBAYTAkdCMRswGQYDVQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIx
    -- EDAOBgNVBAcTB1NhbGZvcmQxGDAWBgNVBAoTD1NlY3RpZ28gTGltaXRlZDE+MDwGA1UEAxM1U2Vj
    -- dGlnbyBSU0EgQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBTZWN1cmUgRW1haWwgQ0ECEQDwUrMy
    -- 4rq9PIFHeb0WvDbOMAkGBSsOAwIaBQCgXTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqG
    -- SIb3DQEJBTEPFw0xOTA0MDIyMjM2MjRaMCMGCSqGSIb3DQEJBDEWBBQKTVWo13jlAi+rcBl3xdhA
    -- u8SG0DANBgkqhkiG9w0BAQEFAASCAQAWaz5itTD7L3YUWuRS5nBzJKv6RUe84muwLICW+U5HEEd7
    -- aSNXCivMzsIYGN3VpBAfIGbRywDZ8SLFkmR3ernA4y0ki5SUNxfe6s3fsqPVIO56JEEF0/OZ54KU
    -- 6o/Tn2Qw8mYyYW5n2O690DGqtN/mZjI5vyRM8fG6KDrlnMplACF4St6XZx9Od/Znq039IPzgukJI
    -- rCIm9n0LFEdSYlajjS/d5GsIKWmy5ktrsXk5Ym6+Um95FBDuwrZyW1qYo+cLAWSBSf9poMUxwQNY
    -- EAA1iZqJjnlwsgMgM44h9voAW+wDMfcGFUwGDTxaLday6vqxHobR3MwjsBI8qjRbb28G
    -- -----END PKCS7-----
    -- 
    -- 

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbPem


END
GO