SQL Server
SQL Server
ECDSA Sign and Verify
See more ECC Examples
Demonstrates how to create an ECDSA signature on the SHA256 hash of some data, and then verify.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
-- 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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First load an ECDSA private key to be used for signing.
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, 'qa_data/ecc/secp256r1-key-pkcs8-secret.pem', 'secret'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @privKey
RETURN
END
-- Sign the SHA256 hash of some data.
DECLARE @bd int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/hamlet.xml'
IF @success = 0
BEGIN
PRINT 'Failed to load file to be hashed.'
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @bd
RETURN
END
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
DECLARE @hashStr nvarchar(4000)
EXEC sp_OAMethod @crypt, 'HashBdENC', @hashStr OUT, @bd
DECLARE @ecdsa int
EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ecdsa OUT
DECLARE @prng int
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
-- Returns ASN.1 signature as a base64 string.
DECLARE @sig nvarchar(4000)
EXEC sp_OAMethod @ecdsa, 'SignHashENC', @sig OUT, @hashStr, 'base64', @privKey, @prng
PRINT 'sig = ' + @sig
-- The signature is in ASN.1 format (which may be described as the "encoded DSS signature").
-- SEQUENCE (2 elem)
-- INTEGER (255 bit) 4849395540832462044300553275435608522154141569743642905628579547100940...
-- INTEGER (255 bit) 3680701124244788134409868118208591399799457104230118295614152238560005...
-- If you wish, you can get the r and s components of the signature like this:
DECLARE @asn int
EXEC @hr = sp_OACreate 'Chilkat.Asn', @asn OUT
EXEC sp_OAMethod @asn, 'LoadEncoded', @success OUT, @sig, 'base64'
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @asn, 'AsnToXml', @sTmp0 OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- We now have this:
-- <?xml version="1.0" encoding="utf-8"?>
-- <sequence>
-- <int>6650D422D86BA4A228B5617604E59052591B9B2C32EF324C44D09EF67E5F0060</int>
-- <int>0CFD9F6AC85042FC70F672C141BA6B2A4CAFBB906C3D907BCCC1BED62B28326F</int>
-- </sequence>
-- Get the "r" and "s" as hex strings
DECLARE @r nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetChildContentByIndex', @r OUT, 0
DECLARE @s nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetChildContentByIndex', @s OUT, 1
PRINT 'r = ' + @r
PRINT 's = ' + @s
-- --------------------------------------------------------------------
-- Now verify against the hash of the original data.
-- Get the corresponding public key.
DECLARE @pubKey int
EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT
EXEC sp_OAMethod @pubKey, 'LoadFromFile', @success OUT, 'qa_data/ecc/secp256r1-pub.pem'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pubKey, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @ecdsa
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @asn
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @pubKey
RETURN
END
-- We already have the SHA256 hash of the original data (hashStr) so no need to re-do it..
DECLARE @ecc2 int
EXEC @hr = sp_OACreate 'Chilkat.Ecc', @ecc2 OUT
DECLARE @result int
EXEC sp_OAMethod @ecc2, 'VerifyHashENC', @result OUT, @hashStr, @sig, 'base64', @pubKey
IF @result <> 1
BEGIN
EXEC sp_OAGetProperty @ecc2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @ecdsa
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @asn
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @pubKey
EXEC @hr = sp_OADestroy @ecc2
RETURN
END
PRINT 'Verified!'
-- Note: If we have only r,s and wish to reconstruct the ASN.1 signature, we do it like this:
DECLARE @xml2 int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml2 OUT
EXEC sp_OASetProperty @xml2, 'Tag', 'sequence'
EXEC sp_OAMethod @xml2, 'NewChild2', NULL, 'int', @r
EXEC sp_OAMethod @xml2, 'NewChild2', NULL, 'int', @s
DECLARE @asn2 int
EXEC @hr = sp_OACreate 'Chilkat.Asn', @asn2 OUT
EXEC sp_OAMethod @xml2, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @asn2, 'LoadAsnXml', @success OUT, @sTmp0
DECLARE @encodedSig nvarchar(4000)
EXEC sp_OAMethod @asn2, 'GetEncodedDer', @encodedSig OUT, 'base64'
PRINT 'encoded DSS signature: ' + @encodedSig
-- You can go to https://lapo.it/asn1js/ and copy/paste the base64 encodedSig into the online tool, then press the "decode" button.
-- You will see the ASN.1 such as this:
-- SEQUENCE (2 elem)
-- INTEGER (255 bit) 4849395540832462044300553275435608522154141569743642905628579547100940...
-- INTEGER (255 bit) 3680701124244788134409868118208591399799457104230118295614152238560005...
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @ecdsa
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @asn
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @pubKey
EXEC @hr = sp_OADestroy @ecc2
EXEC @hr = sp_OADestroy @xml2
EXEC @hr = sp_OADestroy @asn2
END
GO