(SQL Server) How to Load a JWK Set Published at a Remote URL

Demonstrates how to load a JWK set published at a remote URL. In general, this example can fetch any JSON, or any text (non-binary) document from a remote URL. It uses the Chilkat HTTP API to do it.

Note: This example requires Chilkat v9.5.0.66 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 assumes the Chilkat HTTP API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    -- I put a sample JWK set at this URL: https://www.chilkatsoft.com/data/jwk-set.json
    -- You can test this example with it.
    DECLARE @sbJwkSet int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJwkSet OUT

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/data/jwk-set.json', @sbJwkSet
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbJwkSet
        RETURN
      END

    -- Load the JWK into a JSON object..
    DECLARE @jwkSet int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jwkSet OUT

    EXEC sp_OAMethod @jwkSet, 'LoadSb', @success OUT, @sbJwkSet
    EXEC sp_OASetProperty @jwkSet, 'EmitCompact', 0

    PRINT 'The full JWK Key Set:'
    EXEC sp_OAMethod @jwkSet, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Iterate over each key...
    DECLARE @numKeys int
    EXEC sp_OAMethod @jwkSet, 'SizeOfArray', @numKeys OUT, 'keys'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numKeys
      BEGIN
        EXEC sp_OASetProperty @jwkSet, 'I', @i


        PRINT '---- JWK ' + @i + ' ----'
        DECLARE @jwk int
        EXEC sp_OAMethod @jwkSet, 'ObjectOf', @jwk OUT, 'keys[i]'
        EXEC sp_OASetProperty @jwk, 'EmitCompact', 0
        EXEC sp_OAMethod @jwk, 'Emit', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jwk

        SELECT @i = @i + 1
      END

    -- Output:

    -- The full JWK Key Set:
    -- { 
    --   "keys": [
    --     { 
    --       "kty": "EC",
    --       "crv": "P-256",
    --       "x": "MKBCTNIcKUSDii11ySs3526iDZ8AiTo7Tu6KPAqv7D4",
    --       "y": "4Etl6SRW2YiLUrN5vfvVHuhp7x8PxltmWWlbbM4IFyM",
    --       "d": "870MB6gfuTJ4HtUnUvYMyJpr5eUZNP4Bk43bVdj3eAE",
    --       "use": "enc",
    --       "kid": "1"
    --     },
    --     { 
    --       "kty": "RSA",
    --       "n": "0vx7agoebGcQSuuPiLJXZptN9nndrQmbXEps2aiAFbWhM78LhWx4
    -- cbbfAAtVT86zwu1RK7aPFFxuhDR1L6tSoc_BJECPebWKRXjBZCiFV4n3oknjhMst
    -- n64tZ_2W-5JsGY4Hc5n9yBXArwl93lqt7_RN5w6Cf0h4QyQ5v-65YGjQR0_FDW2Q
    -- vzqY368QQMicAtaSqzs8KJZgnYb9c7d0zgdAZHzu6qMQvRL5hajrn1n91CbOpbIS
    -- D08qNLyrdkt-bFTWhAI4vMQFh6WeZu0fM4lFd2NcRwr3XPksINHaQ-G_xBniIqbw
    -- 0Ls1jF44-csFCur-kEgU8awapJzKnqDKgw",
    --       "e": "AQAB",
    --       "d": "X4cTteJY_gn4FYPsXB8rdXix5vwsg1FLN5E3EaG6RJoVH-HLLKD9
    -- M7dx5oo7GURknchnrRweUkC7hT5fJLM0WbFAKNLWY2vv7B6NqXSzUvxT0_YSfqij
    -- wp3RTzlBaCxWp4doFk5N2o8Gy_nHNKroADIkJ46pRUohsXywbReAdYaMwFs9tv8d
    -- _cPVY3i07a3t8MN6TNwm0dSawm9v47UiCl3Sk5ZiG7xojPLu4sbg1U2jx4IBTNBz
    -- nbJSzFHK66jT8bgkuqsk0GjskDJk19Z4qwjwbsnn4j2WBii3RL-Us2lGVkY8fkFz
    -- me1z0HbIkfz0Y6mqnOYtqc0X4jfcKoAC8Q",
    --       "p": "83i-7IvMGXoMXCskv73TKr8637FiO7Z27zv8oj6pbWUQyLPQBQxtPV
    -- nwD20R-60eTDmD2ujnMt5PoqMrm8RfmNhVWDtjjMmCMjOpSXicFHj7XOuVIYQyqV
    -- WlWEh6dN36GVZYk93N8Bc9vY41xy8B9RzzOGVQzXvNEvn7O0nVbfs",
    --       "q": "3dfOR9cuYq-0S-mkFLzgItgMEfFzB2q3hWehMuG0oCuqnb3vobLyum
    -- qjVZQO1dIrdwgTnCdpYzBcOfW5r370AFXjiWft_NGEiovonizhKpo9VVS78TzFgx
    -- kIdrecRezsZ-1kYd_s1qDbxtkDEgfAITAG9LUnADun4vIcb6yelxk",
    --       "dp": "G4sPXkc6Ya9y8oJW9_ILj4xuppu0lzi_H7VTkS8xj5SdX3coE0oim
    -- YwxIi2emTAue0UOa5dpgFGyBJ4c8tQ2VF402XRugKDTP8akYhFo5tAA77Qe_Nmtu
    -- YZc3C3m3I24G2GvR5sSDxUyAN2zq8Lfn9EUms6rY3Ob8YeiKkTiBj0",
    --       "dq": "s9lAH9fggBsoFR8Oac2R_E2gw282rT2kGOAhvIllETE1efrA6huUU
    -- vMfBcMpn8lqeW6vzznYY5SSQF7pMdC_agI3nG8Ibp1BUb0JUiraRNqUfLhcQb_d9
    -- GF4Dh7e74WbRsobRonujTYN1xCaP6TO61jvWrX-L18txXw494Q_cgk",
    --       "qi": "GyM_p6JrXySiz1toFgKbWV-JdI3jQ4ypu9rbMWx3rQJBfmt0FoYzg
    -- UIZEVFEcOqwemRN81zoDAaa-Bk0KWNGDjJHZDdDmFhW3AN7lI-puxk_mHZGJ11rx
    -- yR8O55XLSe3SPmRfKwZI6yU24ZxvQKFYItdldUKGzO6Ia6zTKhAVRU",
    --       "alg": "RS256",
    --       "kid": "2011-04-29"
    --     },
    --     { 
    --       "kty": "oct",
    --       "alg": "A128KW",
    --       "k": "GawgguFyGrWKav7AX4VKUg",
    --       "kid": "AES KEK"
    --     },
    --     { 
    --       "kty": "oct",
    --       "k": "AyM1SysPpbyDfgZld3umj1qzKObwVMkoqQ-EstJQLr_T-1qS0gZH75
    -- aKtMN3Yj0iPS4hcgUuTwjAzZr1Z9CAow",
    --       "kid": "HMAC key used in JWS spec Appendix A.1 example"
    --     }
    --   ]
    -- }
    -- 
    -- ---- JWK 0 ----
    -- { 
    --   "kty": "EC",
    --   "crv": "P-256",
    --   "x": "MKBCTNIcKUSDii11ySs3526iDZ8AiTo7Tu6KPAqv7D4",
    --   "y": "4Etl6SRW2YiLUrN5vfvVHuhp7x8PxltmWWlbbM4IFyM",
    --   "d": "870MB6gfuTJ4HtUnUvYMyJpr5eUZNP4Bk43bVdj3eAE",
    --   "use": "enc",
    --   "kid": "1"
    -- }
    -- 
    -- ---- JWK 1 ----
    -- { 
    --   "kty": "RSA",
    --   "n": "0vx7agoebGcQSuuPiLJXZptN9nndrQmbXEps2aiAFbWhM78LhWx4
    -- cbbfAAtVT86zwu1RK7aPFFxuhDR1L6tSoc_BJECPebWKRXjBZCiFV4n3oknjhMst
    -- n64tZ_2W-5JsGY4Hc5n9yBXArwl93lqt7_RN5w6Cf0h4QyQ5v-65YGjQR0_FDW2Q
    -- vzqY368QQMicAtaSqzs8KJZgnYb9c7d0zgdAZHzu6qMQvRL5hajrn1n91CbOpbIS
    -- D08qNLyrdkt-bFTWhAI4vMQFh6WeZu0fM4lFd2NcRwr3XPksINHaQ-G_xBniIqbw
    -- 0Ls1jF44-csFCur-kEgU8awapJzKnqDKgw",
    --   "e": "AQAB",
    --   "d": "X4cTteJY_gn4FYPsXB8rdXix5vwsg1FLN5E3EaG6RJoVH-HLLKD9
    -- M7dx5oo7GURknchnrRweUkC7hT5fJLM0WbFAKNLWY2vv7B6NqXSzUvxT0_YSfqij
    -- wp3RTzlBaCxWp4doFk5N2o8Gy_nHNKroADIkJ46pRUohsXywbReAdYaMwFs9tv8d
    -- _cPVY3i07a3t8MN6TNwm0dSawm9v47UiCl3Sk5ZiG7xojPLu4sbg1U2jx4IBTNBz
    -- nbJSzFHK66jT8bgkuqsk0GjskDJk19Z4qwjwbsnn4j2WBii3RL-Us2lGVkY8fkFz
    -- me1z0HbIkfz0Y6mqnOYtqc0X4jfcKoAC8Q",
    --   "p": "83i-7IvMGXoMXCskv73TKr8637FiO7Z27zv8oj6pbWUQyLPQBQxtPV
    -- nwD20R-60eTDmD2ujnMt5PoqMrm8RfmNhVWDtjjMmCMjOpSXicFHj7XOuVIYQyqV
    -- WlWEh6dN36GVZYk93N8Bc9vY41xy8B9RzzOGVQzXvNEvn7O0nVbfs",
    --   "q": "3dfOR9cuYq-0S-mkFLzgItgMEfFzB2q3hWehMuG0oCuqnb3vobLyum
    -- qjVZQO1dIrdwgTnCdpYzBcOfW5r370AFXjiWft_NGEiovonizhKpo9VVS78TzFgx
    -- kIdrecRezsZ-1kYd_s1qDbxtkDEgfAITAG9LUnADun4vIcb6yelxk",
    --   "dp": "G4sPXkc6Ya9y8oJW9_ILj4xuppu0lzi_H7VTkS8xj5SdX3coE0oim
    -- YwxIi2emTAue0UOa5dpgFGyBJ4c8tQ2VF402XRugKDTP8akYhFo5tAA77Qe_Nmtu
    -- YZc3C3m3I24G2GvR5sSDxUyAN2zq8Lfn9EUms6rY3Ob8YeiKkTiBj0",
    --   "dq": "s9lAH9fggBsoFR8Oac2R_E2gw282rT2kGOAhvIllETE1efrA6huUU
    -- vMfBcMpn8lqeW6vzznYY5SSQF7pMdC_agI3nG8Ibp1BUb0JUiraRNqUfLhcQb_d9
    -- GF4Dh7e74WbRsobRonujTYN1xCaP6TO61jvWrX-L18txXw494Q_cgk",
    --   "qi": "GyM_p6JrXySiz1toFgKbWV-JdI3jQ4ypu9rbMWx3rQJBfmt0FoYzg
    -- UIZEVFEcOqwemRN81zoDAaa-Bk0KWNGDjJHZDdDmFhW3AN7lI-puxk_mHZGJ11rx
    -- yR8O55XLSe3SPmRfKwZI6yU24ZxvQKFYItdldUKGzO6Ia6zTKhAVRU",
    --   "alg": "RS256",
    --   "kid": "2011-04-29"
    -- }
    -- 
    -- ---- JWK 2 ----
    -- { 
    --   "kty": "oct",
    --   "alg": "A128KW",
    --   "k": "GawgguFyGrWKav7AX4VKUg",
    --   "kid": "AES KEK"
    -- }
    -- 
    -- ---- JWK 3 ----
    -- { 
    --   "kty": "oct",
    --   "k": "AyM1SysPpbyDfgZld3umj1qzKObwVMkoqQ-EstJQLr_T-1qS0gZH75
    -- aKtMN3Yj0iPS4hcgUuTwjAzZr1Z9CAow",
    --   "kid": "HMAC key used in JWS spec Appendix A.1 example"
    -- }
    -- 

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbJwkSet
    EXEC @hr = sp_OADestroy @jwkSet


END
GO