(SQL Server) RSASSA-PSS Sign Binary Data

Signs binary data to create a PKCS7/CMS signature. The signature algorithm is RSASSA-PSS with SHA256.

Note: This example requires Chilkat v9.5.0.67 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat Crypt API to have been previously unlocked.
    -- See Unlock Chilkat Crypt for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Get a digital certificate with private key from a .pfx
    -- (Chilkat has many different ways to provide a cert + private key for siging.
    -- Using a PFX is just one possible option.)
    DECLARE @pfx int
    -- Use "Chilkat_9_5_0.Pfx" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT

    DECLARE @success int
    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/rsassa-pss/privatekey.pfx', 'PFX_PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Get the certificate to be used for signing.
    -- (The typical case for a PFX is that it contains a cert with an associated private key,
    -- as well as other certificates in the chain of authentication.  The cert with the private
    -- key should be in the first position at index 0.)
    DECLARE @cert int
    EXEC sp_OAMethod @pfx, 'GetCert', @cert OUT, 0
    EXEC sp_OAGetProperty @pfx, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END
    EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert

    -- Indicate that RSASSA-PSS with SHA256 should be used.
    EXEC sp_OASetProperty @crypt, 'SigningAlg', 'pss'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64_mime'

    -- Load a binary file to be signed:
    DECLARE @binaryData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @binaryData OUT

    EXEC sp_OAMethod @binaryData, 'LoadFile', @success OUT, 'qa_data/jpg/starfish20.jpg'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load file.'
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @binaryData
        RETURN
      END

    -- Sign the binary bytes to get a PKCS7 detached signature in base64 format:
    DECLARE @pkcs7sig nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'SignBdENC', @pkcs7sig OUT, @binaryData

    PRINT 'Detached PCKS7 Signature:'

    PRINT @pkcs7sig

    -- This signature looks like this:

    -- MIIG5wYJKoZIhvcNAQcCoIIG2DCCBtQCAQExDzANBglghkgBZQMEAgEFADALBgkqhkiG9w0BBwGg
    -- ggL4MIIC9DCCAl2gAwIBAgIJAMPsJCT11cniMA0GCSqGSIb3DQEBCwUAMIGSMQswCQYDVQQGEwJB
    -- VTERMA8GA1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEhMB8GA1UECgwYSW50ZXJu
    -- ZXQgV2lkZ2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkqhkiG9w0BCQEWGWFkbWlu
    -- QGludGVybmV0d2lkZ2V0cy5jb20wHhcNMTYxMTAxMTY1MjMyWhcNMjExMDMxMTY1MjMyWjCBkjEL
    -- MAkGA1UEBhMCQVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNV
    -- BAoMGEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcN
    -- AQkBFhlhZG1pbkBpbnRlcm5ldHdpZGdldHMuY29tMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKB
    -- gQDGIdoCjyavs+F/Rm0VIB4m6O7VL1j+1IqieoR9NEX2GQvu2VCdceyxf9qaw1bxipEvjLwUkw7M
    -- e+BTlLpWQbBMH87s6KpsC8MVyXhMLpP0oM8NFix/vLz2wdLhUh7CZvJA0plqkJk9bj57QIu+EO1k
    -- tUHM2DFb6sckvCL2yybD1wIDAQABo1AwTjAdBgNVHQ4EFgQUONKKu2zsXIrinWxIGT654vrcQwsw
    -- HwYDVR0jBBgwFoAUONKKu2zsXIrinWxIGT654vrcQwswDAYDVR0TBAUwAwEB/zANBgkqhkiG9w0B
    -- AQsFAAOBgQArFvdi5u9i2QF1Qw+cdC1l7w2Y3+q6RIkln2W8rWJFje00644o8hXy7v46giJCedmF
    -- ULlhm1n7XIsZGy2W3lJ77v5agn9gFwXu1h3cqkGXkoteE6SQJQXWgsW3GWPveObvTL8LF4y57fgM
    -- 9ZWS+V9MJajeu44Rf/tU17TLYKjvEjGCA7MwggOvAgEBMIGgMIGSMQswCQYDVQQGEwJBVTERMA8G
    -- A1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEhMB8GA1UECgwYSW50ZXJuZXQgV2lk
    -- Z2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkqhkiG9w0BCQEWGWFkbWluQGludGVy
    -- bmV0d2lkZ2V0cy5jb20CCQDD7CQk9dXJ4jANBglghkgBZQMEAgEFAKCCAjQwGAYJKoZIhvcNAQkD
    -- MQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMTcwNDI5MTYxMDI2WjAvBgkqhkiG9w0BCQQx
    -- IgQgrjUQkoMeBYUhmDGjPg147WybF0w2LAY6F+Ih6qHUMB8wXwYJKoZIhvcNAQkPMVIwUDALBglg
    -- hkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsO
    -- AwIHMA0GCCqGSIb3DQMCAgEoMIGxBgkrBgEEAYI3EAQxgaMwgaAwgZIxCzAJBgNVBAYTAkFVMREw
    -- DwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJbnRlcm5ldCBX
    -- aWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZYWRtaW5AaW50
    -- ZXJuZXR3aWRnZXRzLmNvbQIJAMPsJCT11cniMIGzBgsqhkiG9w0BCRACCzGBo6CBoDCBkjELMAkG
    -- A1UEBhMCQVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNVBAoM
    -- GEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcNAQkB
    -- FhlhZG1pbkBpbnRlcm5ldHdpZGdldHMuY29tAgkAw+wkJPXVyeIwPQYJKoZIhvcNAQEKMDCgDTAL
    -- BglghkgBZQMEAgGhGjAYBgkqhkiG9w0BAQgwCwYJYIZIAWUDBAIBogMCASAEgYCWV0g82volvnwf
    -- YpwIpqpQzMmTPBKNQmFGjbyH2opdcbJwgu2qEFvaXkyjYDtgQ7XsCqc15dm6Ee1Ujkosbp57kLTt
    -- /WbwxY1CC/uxs3oV+5ESUyB+2iocTYABYn4ye0FhBPut86n/gzZTL+RLG6Z1fxwwzkoxWUp7GjKK
    -- 58mveQ==

    -- The ASN.1 of the signature can be examined by browsing to https://lapo.it/asn1js/ ,
    -- then copy-and-paste the Base64 signature into the form and decode..

    -- The signature can be verified against the original data like this:
    EXEC sp_OAMethod @crypt, 'VerifyBdENC', @success OUT, @binaryData, @pkcs7sig

    PRINT 'Signature verified: ' + @success

    -- Now we'll create an opaque signature (the opposite of a detached signature). 
    -- An opaque signature is a PKCS7/CMS message that contains both the original data and
    -- the signature.  The verification process extracts the original data.

    -- Then OpaqueSignBd method in-place signs the binaryData.
    -- The contents of binaryData are replaced with the CMS/PKCS7 message.
    EXEC sp_OAMethod @crypt, 'OpaqueSignBd', @success OUT, @binaryData

    -- Show the contents of the opaque signature in base64 format:

    PRINT 'Opaque Signature:'
    EXEC sp_OAMethod @binaryData, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    PRINT @sTmp0

    -- MIIKCgYJKoZIhvcNAQcCoIIJ+zCCCfcCAQExDzANBglghkgBZQMEAgEFADCCAywGCSqGSIb3DQEH
    -- AaCCAx0EggMZ/9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBo
    -- b3Rvc2hvcD8gNC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBE
    -- V0U3OFBtUVdfYmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2Nj
    -- Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/E
    -- ABcAAAMBAAAAAAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQAC
    -- EAMQAAAB2kZYNNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgB
    -- AQABBQL0XqN+pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgB
    -- AwEBPwHqU5aqAxx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIB
    -- AT8B3Bhqy7ZcenyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEB
    -- AAY/ArZyn+CgxtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEA
    -- AT8hkEwPUUR9DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAA
    -- AAAAAAAAAAAAAAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAA
    -- AAAAAAAAAREAQRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAA
    -- AAABEQAhMUFxEFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO
    -- /n//2aCCAvgwggL0MIICXaADAgECAgkAw+wkJPXVyeIwDQYJKoZIhvcNAQELBQAwgZIxCzAJBgNV
    -- BAYTAkFVMREwDwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJ
    -- bnRlcm5ldCBXaWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZ
    -- YWRtaW5AaW50ZXJuZXR3aWRnZXRzLmNvbTAeFw0xNjExMDExNjUyMzJaFw0yMTEwMzExNjUyMzJa
    -- MIGSMQswCQYDVQQGEwJBVTERMA8GA1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEh
    -- MB8GA1UECgwYSW50ZXJuZXQgV2lkZ2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkq
    -- hkiG9w0BCQEWGWFkbWluQGludGVybmV0d2lkZ2V0cy5jb20wgZ8wDQYJKoZIhvcNAQEBBQADgY0A
    -- MIGJAoGBAMYh2gKPJq+z4X9GbRUgHibo7tUvWP7UiqJ6hH00RfYZC+7ZUJ1x7LF/2prDVvGKkS+M
    -- vBSTDsx74FOUulZBsEwfzuzoqmwLwxXJeEwuk/Sgzw0WLH+8vPbB0uFSHsJm8kDSmWqQmT1uPntA
    -- i74Q7WS1QczYMVvqxyS8IvbLJsPXAgMBAAGjUDBOMB0GA1UdDgQWBBQ40oq7bOxciuKdbEgZPrni
    -- +txDCzAfBgNVHSMEGDAWgBQ40oq7bOxciuKdbEgZPrni+txDCzAMBgNVHRMEBTADAQH/MA0GCSqG
    -- SIb3DQEBCwUAA4GBACsW92Lm72LZAXVDD5x0LWXvDZjf6rpEiSWfZbytYkWN7TTrjijyFfLu/jqC
    -- IkJ52YVQuWGbWftcixkbLZbeUnvu/lqCf2AXBe7WHdyqQZeSi14TpJAlBdaCxbcZY+945u9MvwsX
    -- jLnt+Az1lZL5X0wlqN67jhF/+1TXtMtgqO8SMYIDszCCA68CAQEwgaAwgZIxCzAJBgNVBAYTAkFV
    -- MREwDwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJbnRlcm5l
    -- dCBXaWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZYWRtaW5A
    -- aW50ZXJuZXR3aWRnZXRzLmNvbQIJAMPsJCT11cniMA0GCWCGSAFlAwQCAQUAoIICNDAYBgkqhkiG
    -- 9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xNzA0MjkxNjEwMjZaMC8GCSqGSIb3
    -- DQEJBDEiBCCuNRCSgx4FhSGYMaM+DXjtbJsXTDYsBjoX4iHqodQwHzBfBgkqhkiG9w0BCQ8xUjBQ
    -- MAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAw
    -- BwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgbEGCSsGAQQBgjcQBDGBozCBoDCBkjELMAkGA1UEBhMC
    -- QVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNVBAoMGEludGVy
    -- bmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcNAQkBFhlhZG1p
    -- bkBpbnRlcm5ldHdpZGdldHMuY29tAgkAw+wkJPXVyeIwgbMGCyqGSIb3DQEJEAILMYGjoIGgMIGS
    -- MQswCQYDVQQGEwJBVTERMA8GA1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEhMB8G
    -- A1UECgwYSW50ZXJuZXQgV2lkZ2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkqhkiG
    -- 9w0BCQEWGWFkbWluQGludGVybmV0d2lkZ2V0cy5jb20CCQDD7CQk9dXJ4jA9BgkqhkiG9w0BAQow
    -- MKANMAsGCWCGSAFlAwQCAaEaMBgGCSqGSIb3DQEBCDALBglghkgBZQMEAgGiAwIBIASBgAGVtpI5
    -- slxfw+1EyJK4jqxokLvUrqksBLotv1vaP4QaSeF2A1lNrsPfJoEjZJpD1F6vXrFPsR4sPD+6n7P/
    -- lz3sGoFykTjE2rPwKEFIbzfxD3gSZKJPWFgDa19DojarmwJMkSPXt9TQEzdjDPrbsCGLYOy29Puq
    -- ZDI1rUcyxg7Y

    -- The ASN.1 of the signature can be examined by browsing to https://lapo.it/asn1js/ ,
    -- then copy-and-paste the Base64 signature into the form and decode..

    -- The signature is verified, and the original data restored like this:
    EXEC sp_OAMethod @crypt, 'OpaqueVerifyBd', @success OUT, @binaryData
    IF @success <> 1
      BEGIN

        PRINT 'Signature verification failed.'
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @binaryData
        RETURN
      END

    -- Save the extracted data to a file:
    EXEC sp_OAMethod @binaryData, 'WriteFile', @success OUT, 'qa_output/extractedStarfish20.jpg'


    PRINT 'Signature verified.'

    EXEC @hr = sp_OADestroy @cert


    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @binaryData


END
GO