SQL Server Requires Chilkat v11.0.0+

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
    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