(SQL Server) PKCS11 Find all Private Keys

See more PKCS11 Examples

Demonstrates how to list all private keys on an HSM.

Note: This example requires Chilkat v9.5.0.96 or later.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Note: Chilkat's PKCS11 implementation runs on Windows, Linux, Mac OS X, and other supported operating systems.

    DECLARE @pkcs11 int
    -- Use "Chilkat_9_5_0.Pkcs11" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pkcs11', @pkcs11 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use the PKCS11 driver (.dll, .so, .dylib) for your particular HSM.
    -- (The format of the path will change with the operating system.  Obviously, "C:/" is not used on non-Windows systems.
    EXEC sp_OASetProperty @pkcs11, 'SharedLibPath', 'C:/Program Files (x86)/Gemalto/IDGo 800 PKCS#11/IDPrimePKCS1164.dll'

    -- Establish a logged-on session.
    -- Use your actual PIN here.
    DECLARE @pin nvarchar(4000)
    SELECT @pin = '0000'
    DECLARE @userType int
    SELECT @userType = 1
    DECLARE @success int
    EXEC sp_OAMethod @pkcs11, 'QuickSession', @success OUT, @userType, @pin
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        RETURN
      END

    -- Note: A logged-on session is required to list private keys.

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    EXEC sp_OAMethod @pkcs11, 'FindAllKeys', @success OUT, 'private', @json
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output, with parsing code below..

    -- {
    --   "keys": [
    --     {
    --       "handle": 74842125,
    --       "id": "0001020304",
    --       "key_type": "RSA",
    --       "label": "Chilkat Software, Inc.",
    --       "modulus": "twVRf6O ... Rwa1ebFY0=",
    --       "exponent": "AAEAAQ=="
    --     },
    --     {
    --       "handle": 18415630,
    --       "id": "010203040A0B0C0D0E0F",
    --       "key_type": "RSA",
    --       "label": "ehealth private key",
    --       "modulus": "qdKjhOwA1 ... A4MtX8BYgHmLw==",
    --       "exponent": "AAEAAQ=="
    --     },
    --     {
    --       "handle": 3735567,
    --       "id": "D531B4B8F308489DA58350596178845973A4562E",
    --       "key_type": "RSA",
    --       "label": "d531b4b8-f308-489d-a583-505961788459",
    --       "modulus": "r0MmXRKBP ... HAd1kUPsNyzcQ==",
    --       "exponent": "AAEAAQ=="
    --     },
    --     {
    --       "handle": 238092304,
    --       "id": "0A0B0C0D01020304",
    --       "key_type": "RSA",
    --       "label": "MySshKey",
    --       "modulus": "ykFHcfBFOq ... rfXBK/6g9t+S6UjJ1kUQ==",
    --       "exponent": "AAEAAQ=="
    --     },
    --     {
    --       "handle": 49348625,
    --       "id": "48656C6C6F",
    --       "key_type": "RSA",
    --       "label": "2048-bit RSA key for testing",
    --       "modulus": "vReVaJzXZYIOB ... kamD/8iNvhAKlKbQ==",
    --       "exponent": "AAEAAQ=="
    --     }
    --   ]
    -- }

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    DECLARE @handle int

    DECLARE @id nvarchar(4000)

    DECLARE @key_type nvarchar(4000)

    DECLARE @label nvarchar(4000)

    DECLARE @modulus nvarchar(4000)

    DECLARE @exponent nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'keys'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'IntOf', @handle OUT, 'keys[i].handle'
        EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'keys[i].id'
        EXEC sp_OAMethod @json, 'StringOf', @key_type OUT, 'keys[i].key_type'
        EXEC sp_OAMethod @json, 'StringOf', @label OUT, 'keys[i].label'
        EXEC sp_OAMethod @json, 'StringOf', @modulus OUT, 'keys[i].modulus'
        EXEC sp_OAMethod @json, 'StringOf', @exponent OUT, 'keys[i].exponent'
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @pkcs11, 'Logout', @success OUT
    EXEC sp_OAMethod @pkcs11, 'CloseSession', @success OUT

    EXEC @hr = sp_OADestroy @pkcs11
    EXEC @hr = sp_OADestroy @json


END
GO