SQL Server

Understanding a few ECDSA Public Key Formats

See more ECC Examples

Describes a few ECDSA public key formats. 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

    -- Here we have the output of the following openssl command:  openssl ec -in key.pem -pubout -text

    -- Private-Key: (256 bit)
    -- priv:
    --     0e:63:25:8a:73:3c:71:b6:c0:e7:a3:0f:94:b9:74:
    --     e0:be:bd:46:18:be:40:7e:66:9e:21:99:85:0e:ed:
    --     87:2d
    -- pub:
    --     04:5d:1a:4f:d9:bd:49:9e:e4:fd:55:2c:0d:ea:6d:
    --     b1:66:64:7a:71:91:13:63:86:fe:ca:94:d4:47:51:
    --     39:66:ff:43:d5:62:de:f2:f2:41:3c:2e:3f:95:18:
    --     2d:23:f7:e7:8e:75:19:3b:c6:50:fb:d9:90:f5:e8:
    --     12:b7:b8:6a:43
    -- ASN1 OID: prime256v1
    -- NIST CURVE: P-256
    -- writing EC key
    -- -----BEGIN PUBLIC KEY-----
    -- MFkwEwYHKoZIzj0CAQYIKoZIzj0DAQcDQgAEXRpP2b1JnuT9VSwN6m2xZmR6cZET
    -- Y4b+ypTUR1E5Zv9D1WLe8vJBPC4/lRgtI/fnjnUZO8ZQ+9mQ9egSt7hqQw==
    -- -----END PUBLIC KEY-----

    -- The public key is shown in two different formats.
    -- The first is this:

    -- pub:
    --     04:5d:1a:4f:d9:bd:49:9e:e4:fd:55:2c:0d:ea:6d:
    --     b1:66:64:7a:71:91:13:63:86:fe:ca:94:d4:47:51:
    --     39:66:ff:43:d5:62:de:f2:f2:41:3c:2e:3f:95:18:
    --     2d:23:f7:e7:8e:75:19:3b:c6:50:fb:d9:90:f5:e8:
    --     12:b7:b8:6a:43

    -- It is the ANSI X9.63 format.
    -- 65-bytes are the uncompressed public key (04 || X || Y) 

    -- This is the same public key, but in PEM format

    -- -----BEGIN PUBLIC KEY-----
    -- MFkwEwYHKoZIzj0CAQYIKoZIzj0DAQcDQgAEXRpP2b1JnuT9VSwN6m2xZmR6cZET
    -- Y4b+ypTUR1E5Zv9D1WLe8vJBPC4/lRgtI/fnjnUZO8ZQ+9mQ9egSt7hqQw==
    -- -----END PUBLIC KEY-----

    -- It contains ASN.1 that more explicitly identifies the key type.
    -- 
    -- SEQUENCE (2 elem)
    --   SEQUENCE (2 elem)
    --     OBJECT IDENTIFIER 1.2.840.10045.2.1 ecPublicKey (ANSI X9.62 public key type)
    --     OBJECT IDENTIFIER 1.2.840.10045.3.1.7 prime256v1 (ANSI X9.62 named elliptic curve)
    --   BIT STRING (520 bit) 000001000101110100011010010011111101100110111101010010011001111011100...

    -- PEM format can be loaded into a Chilkat public key object like this:

    DECLARE @sbPem int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPem OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbPem, 'Append', @success OUT, '-----BEGIN PUBLIC KEY-----' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbPem, 'Append', @success OUT, 'MFkwEwYHKoZIzj0CAQYIKoZIzj0DAQcDQgAEXRpP2b1JnuT9VSwN6m2xZmR6cZET' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbPem, 'Append', @success OUT, 'Y4b+ypTUR1E5Zv9D1WLe8vJBPC4/lRgtI/fnjnUZO8ZQ+9mQ9egSt7hqQw==' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbPem, 'Append', @success OUT, '-----END PUBLIC KEY-----' + CHAR(13) + CHAR(10)

    DECLARE @pubKey1 int
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey1 OUT

    EXEC sp_OAMethod @sbPem, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @pubKey1, 'LoadFromString', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pubKey1, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbPem
        EXEC @hr = sp_OADestroy @pubKey1
        RETURN
      END

    -- The X9.63 format can be loaded like this:
    DECLARE @sbHex int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHex OUT

    EXEC sp_OAMethod @sbHex, 'Append', @success OUT, '04:5d:1a:4f:d9:bd:49:9e:e4:fd:55:2c:0d:ea:6d:'
    EXEC sp_OAMethod @sbHex, 'Append', @success OUT, 'b1:66:64:7a:71:91:13:63:86:fe:ca:94:d4:47:51:'
    EXEC sp_OAMethod @sbHex, 'Append', @success OUT, '39:66:ff:43:d5:62:de:f2:f2:41:3c:2e:3f:95:18:'
    EXEC sp_OAMethod @sbHex, 'Append', @success OUT, '2d:23:f7:e7:8e:75:19:3b:c6:50:fb:d9:90:f5:e8:'
    EXEC sp_OAMethod @sbHex, 'Append', @success OUT, '12:b7:b8:6a:43'

    -- Get rid of the ":" chars.
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbHex, 'Replace', @numReplaced OUT, ':', ''

    -- We'll need to convert hex to base64..
    DECLARE @bdKey int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdKey OUT

    EXEC sp_OAMethod @sbHex, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @bdKey, 'AppendEncoded', @success OUT, @sTmp0, 'hex'

    DECLARE @pubKey2 int
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey2 OUT

    EXEC sp_OAMethod @bdKey, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @pubKey2, 'LoadFromString', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pubKey2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbPem
        EXEC @hr = sp_OADestroy @pubKey1
        EXEC @hr = sp_OADestroy @sbHex
        EXEC @hr = sp_OADestroy @bdKey
        EXEC @hr = sp_OADestroy @pubKey2
        RETURN
      END

    -- Let's get the key in pubKey2 as PEM.
    -- It should be idential to the PEM above.
    EXEC sp_OAMethod @pubKey2, 'GetPem', @sTmp0 OUT, 1
    PRINT @sTmp0

    -- Here's the output.  You can see it's the same as the PEM above..

    -- -----BEGIN PUBLIC KEY-----
    -- MFkwEwYHKoZIzj0CAQYIKoZIzj0DAQcDQgAEXRpP2b1JnuT9VSwN6m2xZmR6cZET
    -- Y4b+ypTUR1E5Zv9D1WLe8vJBPC4/lRgtI/fnjnUZO8ZQ+9mQ9egSt7hqQw==
    -- -----END PUBLIC KEY-----

    EXEC @hr = sp_OADestroy @sbPem
    EXEC @hr = sp_OADestroy @pubKey1
    EXEC @hr = sp_OADestroy @sbHex
    EXEC @hr = sp_OADestroy @bdKey
    EXEC @hr = sp_OADestroy @pubKey2


END
GO