SQL Server

RSASSA-PSS Sign Text

Signs text to create a PKCS7/CMS signature. The signature algorithm is RSASSA-PSS with SHA256. 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 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

    -- 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
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT

    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/rsassa-pss/privatekey.pfx', 'PFX_PASSWORD'
    IF @success = 0
      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 @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @pfx, 'CertAt', @success OUT, 0, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @cert
        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'

    -- Build a string to sign
    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    DECLARE @i int
    SELECT @i = 1
    WHILE @i < 12
      BEGIN
        EXEC sp_OAMethod @sb, 'AppendInt', @success OUT, @i
        EXEC sp_OAMethod @sb, 'Append', @success OUT, ' the quick brown fox jumped over the lazy dog.' + CHAR(13) + CHAR(10)
        SELECT @i = @i + 1
      END

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

    -- The string to be encrypted looks like this:

    -- 1 the quick brown fox jumped over the lazy dog.
    -- 2 the quick brown fox jumped over the lazy dog.
    -- 3 the quick brown fox jumped over the lazy dog.
    -- 4 the quick brown fox jumped over the lazy dog.
    -- ...

    -- Make sure to specify the byte representation (character encoding)
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'

    -- Sign the string to get a PKCS7 detached signature in base64 format.
    DECLARE @pkcs7sig nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'SignSbENC', @pkcs7sig OUT, @sb

    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
    -- MQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMTcwNDI5MTYzNTQ3WjAvBgkqhkiG9w0BCQQx
    -- IgQgnXqtpfBjs9ZZUJxbNYbwYvpmXTGgIbYErkIA3jQo0EEwXwYJKoZIhvcNAQkPMVIwUDALBglg
    -- hkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsO
    -- AwIHMA0GCCqGSIb3DQMCAgEoMIGxBgkrBgEEAYI3EAQxgaMwgaAwgZIxCzAJBgNVBAYTAkFVMREw
    -- DwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJbnRlcm5ldCBX
    -- aWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZYWRtaW5AaW50
    -- ZXJuZXR3aWRnZXRzLmNvbQIJAMPsJCT11cniMIGzBgsqhkiG9w0BCRACCzGBo6CBoDCBkjELMAkG
    -- A1UEBhMCQVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNVBAoM
    -- GEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcNAQkB
    -- FhlhZG1pbkBpbnRlcm5ldHdpZGdldHMuY29tAgkAw+wkJPXVyeIwPQYJKoZIhvcNAQEKMDCgDTAL
    -- BglghkgBZQMEAgGhGjAYBgkqhkiG9w0BAQgwCwYJYIZIAWUDBAIBogMCASAEgYBFgBS1vtBoac6P
    -- /NRcY3jdytoxK7xc8vEYW/PhgsbE+sP7dvu6eoRLVicoQ8RIeMh3CtPZFq1+JcL5NNzQoAotizTX
    -- EKhiceX1mC5ocCeEvgNouxkN2Qr/X12m60rNvNl5r+GEqiqx/1fs2yRAl5sIzwPsMMY3p9tGOpy0
    -- 5p7sRQ==

    -- 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, 'VerifySbENC', @success OUT, @sb, @pkcs7sig

    PRINT 'Signature verified: ' + @success

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sb


END
GO