(SQL Server) List Keys in a Microsoft Storage Provider

Demonstrates how to list the keys found in a Microsoft CNG Storage Provider, or a Legacy CryptoAPI Storage Provider.

Important: This example requires Chilkat v9.5.0.83 or greater.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires Chilkat v9.5.0.83 or greater.

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

    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

    DECLARE @success int
    EXEC sp_OAMethod @keyCon, 'GetKeys', @success OUT, 'Microsoft Software Key Storage Provider', @json
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @keyCon, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @keyCon
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

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

    -- Sample JSON returned by GetKeys

    -- If the storage provider is CNG, then each key has an "algId".
    -- If the storage provider is legacy CryptoAPI, then each key has a "provType" (see below)

    -- If the storage provider is CNG:
    -- {
    --   "storageProvider": "Microsoft Software Key Storage Provider",
    --   "key": [
    --     {
    --       "name": "cn=Chilkat Admin,ou=Persona Not Verified,ou=For Test Purposes Only Decryption Key",
    --       "machineKey": false,
    --       "algId": "ECDH_P384"
    --     },
    --     {
    --       "name": "CD1CC265-0DA0-4230-8419-CB6F808FE688",
    --       "machineKey": false,
    --       "algId": "SP800_108_CTR_HMAC"
    --     },
    --     {
    --       "name": "{FF14487C-0AED-471E-8FAD-4BF757C2195C}",
    --       "machineKey": false,
    --       "algId": "ECDH_P256"
    --     },
    --     {
    --       "name": "{869FD9A6-D815-44FD-A916-4D4A708CD949}",
    --       "machineKey": false,
    --       "algId": "ECDH_P256"
    --     },
    --     {
    --       "name": "D530ECA9-FF5A-4A6A-AAB3-6EC1870F2CC3",
    --       "machineKey": false,
    --       "algId": "SP800_108_CTR_HMAC"
    --     },
    --     {
    --       "name": "{F524771E-93C0-4D56-8DE7-2FE3CB5BD837}",
    --       "machineKey": false,
    --       "algId": "RSA"
    --     },
    --     {
    --       "name": "Microsoft Connected Devices Platform device certificate",
    --       "machineKey": false,
    --       "algId": "ECDSA_P256"
    --     }
    --   ]
    -- }

    -- If the storage provider is legacy CryptoAPI:

    -- {
    --   "storageProvider": "Microsoft Base Cryptographic Provider v1.0",
    --   "key": [
    --     {
    --       "name": "{48D7FF88-A52E-41E2-9EBD-0CC7E880DC65}",
    --       "machineKey": false,
    --       "provType": "PROV_RSA_FULL"
    --     },
    --     {
    --       "name": "{CBF7A33A-F9CF-4C30-8A06-94A5C7981058}",
    --       "machineKey": false,
    --       "provType": "PROV_RSA_FULL"
    --     }
    --   ]
    -- }

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

    DECLARE @name nvarchar(4000)

    DECLARE @machineKey int

    DECLARE @algId nvarchar(4000)

    DECLARE @provType nvarchar(4000)

    DECLARE @storageProvider nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @storageProvider OUT, 'storageProvider'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'key'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'key[i].name'
        EXEC sp_OAMethod @json, 'BoolOf', @machineKey OUT, 'key[i].machineKey'
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'key[i].algId'
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @json, 'StringOf', @algId OUT, 'key[i].algId'
          END
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'key[i].provType'
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @json, 'StringOf', @provType OUT, 'key[i].provType'
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @keyCon
    EXEC @hr = sp_OADestroy @json


END
GO