SQL Server

RSASSA-PSS Sign String to Create Base64 PCKS7 Signature

See more Digital Signatures Examples

Signs a string to create a PKCS7 signature in the base64 encoding. 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 @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.

    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'

    -- Sign a string and return the base64 PKCS7 detached signature
    DECLARE @originalText nvarchar(4000)
    SELECT @originalText = 'This is a test'
    DECLARE @pkcs7sig nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'SignStringENC', @pkcs7sig OUT, @originalText

    PRINT 'Detached Signature:'

    PRINT @pkcs7sig

    -- This signature looks like this:
    -- MIIG5wYJKoZIhvcNAQcCoIIG2DCCBtQCAQExDzANBgl .. YToLqEwTdU87ox5g7rvw==

    -- 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, 'VerifyStringENC', @success OUT, @originalText, @pkcs7sig

    PRINT 'Signature verified: ' + @success
    EXEC sp_OAMethod @crypt, 'VerifyStringENC', @success OUT, 'Not the original text', @pkcs7sig

    PRINT 'Signature verified: ' + @success

    -- Now we'll create an opaque signature (the opposite of a detached signature). 
    -- An opaque signature is a PKCS7 message that contains both the original data and
    -- the signature.  The verification process extracts the original data.
    DECLARE @opaqueSig nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'OpaqueSignStringENC', @opaqueSig OUT, @originalText

    PRINT 'Opaque Signature:'

    PRINT @opaqueSig

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

    -- We can verify and extract the original data:
    DECLARE @origTxt nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'OpaqueVerifyStringENC', @origTxt OUT, @opaqueSig
    EXEC sp_OAGetProperty @crypt, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

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


    PRINT 'Signature verified.'

    PRINT 'Extracted text:' + @origTxt

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


END
GO