(SQL Server) Verify Okta Access Token Locally

This example demonstrates how to validate an Okta access token using Chilkat's JWT class.

For more information, see https://developer.okta.com/docs/guides/validate-access-tokens/overview/#what-to-check-when-validating-an-access-token

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

    -- This example begins with two JSON files:
    -- 
    -- 1. The access token obtained from Okta as shown in one fo these examples:  
    --    Get Okta Token using Resource Owner Password Flow
    -- 
    -- 2. The Okta web keys obtained by this example:  Get Okta Web Keys
    -- 
    -- 

    -- Load the access token to be verified.
    -- It contains JSON that looks like this:
    -- {
    --   "access_token": "eyJraWQiOiJhb ... O_eVu-kBp6g",
    --   "token_type": "Bearer",
    --   "expires_in": 3600,
    --   "scope": "openid",
    --   "id_token": "eyJraWQi ... FrL9WOuwbQtUg"
    -- }
    -- This example verifies the access_token.  (The id_token is verified in this example:  Verify Okta ID Token

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

    DECLARE @success int
    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/okta_access_token.json'

    -- Load the public keys (Okta web keys), one of which is needed to validate.
    -- The web keys JSON looks like this:
    -- {
    --   "keys": [
    --     {
    --       "kty": "RSA",
    --       "alg": "RS256",
    --       "kid": "anSaRDPfWGOSCVNZEIZB9quCbNsdsvl5uWGBzxbudWQ",
    --       "use": "sig",
    --       "e": "AQAB",
    --       "n": "jT8uAgd5w ... euLB1HaVw"
    --     },
    --     {
    -- 	...
    --     }
    --   ]
    -- }

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

    EXEC sp_OAMethod @jsonWebKeys, 'LoadFile', @success OUT, 'qa_data/tokens/okta_web_keys.json'

    -- ------------------------
    -- Step 1: Get the JOSE header from the JWT.  The JOSE header contains JSON.  One of the JSON members will be the key ID "kid" which identifies the web key to be used for validation.
    -- 
    DECLARE @jwt int
    -- Use "Chilkat_9_5_0.Jwt" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT

    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @jsonToken, 'StringOf', @accessToken OUT, 'access_token'
    DECLARE @joseHeader nvarchar(4000)
    EXEC sp_OAMethod @jwt, 'GetHeader', @joseHeader OUT, @accessToken


    PRINT @joseHeader
    -- The joseHeader contains this:   {"kid":"anSaRDPfWGOSCVNZEIZB9quCbNsdsvl5uWGBzxbudWQ","alg":"RS256"}

    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_OAMethod @json, 'Load', @success OUT, @joseHeader
    DECLARE @kid nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @kid OUT, 'kid'

    PRINT 'kid to find: ' + @kid

    -- ------------------------
    -- Step 2: Find the key with the same "kid" in the Okta web keys.

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

    DECLARE @e nvarchar(4000)
    SELECT @e = ''
    DECLARE @n nvarchar(4000)
    SELECT @n = ''

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonWebKeys, 'SizeOfArray', @count_i OUT, 'keys'
    DECLARE @bFound int
    SELECT @bFound = 0
    DECLARE @iMatch int
    SELECT @iMatch = 0
    WHILE (@bFound = 0) and (@i < @count_i)
      BEGIN
        EXEC sp_OASetProperty @jsonWebKeys, 'I', @i
        EXEC sp_OAMethod @sbKid, 'Clear', NULL
        EXEC sp_OAMethod @jsonWebKeys, 'StringOfSb', @success OUT, 'keys[i].kid', @sbKid

        EXEC sp_OAMethod @sbKid, 'GetAsString', @sTmp0 OUT
        PRINT 'checking kid: ' + @sTmp0

        EXEC sp_OAMethod @sbKid, 'ContentsEqual', @iTmp0 OUT, @kid, 1
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @jsonWebKeys, 'StringOf', @e OUT, 'keys[i].e'
            EXEC sp_OAMethod @jsonWebKeys, 'StringOf', @n OUT, 'keys[i].n'
            -- Exit the loop. 

            PRINT 'Found matching kid.'
            SELECT @iMatch = @i
            SELECT @bFound = 1
          END
        SELECT @i = @i + 1
      END

    IF @bFound = 0
      BEGIN

        PRINT 'No matching key ID found.'
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @jsonWebKeys
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbKid
        RETURN
      END


    PRINT 'Matching key:'

    PRINT '  exponent = ' + @e

    PRINT '  modulus = ' + @n

    -- ------------------------
    -- Step 3: Load the RSA modulus and exponent into a Chilkat public key object.
    DECLARE @pubkey int
    -- Use "Chilkat_9_5_0.PublicKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubkey OUT

    -- Get the matching JSON key from the array of keys.
    EXEC sp_OASetProperty @jsonWebKeys, 'I', @iMatch
    DECLARE @jsonWebKey int
    EXEC sp_OAMethod @jsonWebKeys, 'ObjectOf', @jsonWebKey OUT, 'keys[i]'
    EXEC sp_OAMethod @jsonWebKey, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @pubkey, 'LoadFromString', @success OUT, @sTmp0
    IF @success = 0
      BEGIN

        PRINT 'Failed to load JSON web key.'
        EXEC sp_OAMethod @jsonWebKey, 'Emit', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @pubkey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonWebKey

        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @jsonWebKeys
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbKid
        EXEC @hr = sp_OADestroy @pubkey
        RETURN
      END

    EXEC @hr = sp_OADestroy @jsonWebKey


    PRINT 'successfully loaded web key.'

    -- OK.. we have the desired JSON web key loaded into our public key object.
    -- Now we can verify the access token.

    -- ------------------------
    -- Step 4: Verify the access token.
    DECLARE @bVerified int
    EXEC sp_OAMethod @jwt, 'VerifyJwtPk', @bVerified OUT, @accessToken, @pubkey
    IF @bVerified = 1
      BEGIN

        PRINT 'The access token is valid.'
      END
    ELSE
      BEGIN

        PRINT 'The access token is NOT valid.'
      END

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @jsonWebKeys
    EXEC @hr = sp_OADestroy @jwt
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbKid
    EXEC @hr = sp_OADestroy @pubkey


END
GO