SQL Server Requires Chilkat v11.0.0+
SQL Server
RSASSA-PSS Sign Binary Data
See more Digital Signatures Examples
Signs binary data to create a PKCS7/CMS signature. The signature algorithm is RSASSA-PSS with SHA256.Chilkat SQL Server Downloads
-- 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'
-- Load a binary file to be signed:
DECLARE @binaryData int
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 @crypt
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @cert
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 @crypt
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @cert
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 @crypt
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @binaryData
END
GO