(SQL Server) Verify Okta ID Token Locally

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

For more information, see https://developer.okta.com/docs/guides/validate-id-tokens/overview/

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 + id 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
    -- 
    -- 

    -- ----------------------------------------------------------------
    -- Note: The very last step of this example is where the claims, such as iss, aud, iat, exp, and nonce
    -- are extracted from the ID token and examined.
    -- ----------------------------------------------------------------

    -- Load the access/id 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 id_token.  (The access_token is verified in this example:  Verify Okta Access 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 @idToken nvarchar(4000)
    EXEC sp_OAMethod @jsonToken, 'StringOf', @idToken OUT, 'id_token'
    DECLARE @joseHeader nvarchar(4000)
    EXEC sp_OAMethod @jwt, 'GetHeader', @joseHeader OUT, @idToken


    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, @idToken, @pubkey
    IF @bVerified = 1
      BEGIN

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

        PRINT 'The ID token is NOT valid.'
      END

    -- ------------------------
    -- Step 5: Extract the claims (payload) from the ID token and examine them..

    DECLARE @claims nvarchar(4000)
    EXEC sp_OAMethod @jwt, 'GetPayload', @claims OUT, @idToken

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

    EXEC sp_OAMethod @jsonClaims, 'Load', @success OUT, @claims
    EXEC sp_OASetProperty @jsonClaims, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonClaims, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample claims:
    -- {
    --   "sub": "00utrr8ehubooPhjj356",
    --   "ver": 1,
    --   "iss": "https://dev-765951.okta.com/oauth2/default",
    --   "aud": "0oatrr20vPYgVDlGr356",
    --   "iat": 1562190727,
    --   "exp": 1562194327,
    --   "jti": "ID.JvlMhlnCj5ZqqGjk-jlgcOxHEyVUwIl9_Kpz69U2D_4",
    --   "amr": [
    --     "pwd"
    --   ],
    --   "idp": "00os29azljkqyx99Q356",
    --   "auth_time": 1562190726,
    --   "at_hash": "SLMiVeyNWWEDaZ-O32nKMg"
    -- }

    -- The exp (expiry time) claim is the time at which this token will expire., expressed in Unix time. You should make sure that this time has not already passed.
    DECLARE @dtExp int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtExp OUT

    EXEC sp_OAMethod @jsonClaims, 'IntOf', @iTmp0 OUT, 'exp'
    EXEC sp_OAMethod @dtExp, 'SetFromUnixTime', @success OUT, 0, @iTmp0

    EXEC sp_OAMethod @dtExp, 'GetAsTimestamp', @sTmp0 OUT, 0
    PRINT 'expire timestamp = ' + @sTmp0

    -- Check to see if this date/time expires within 0 seconds (i.e. is already past)
    DECLARE @bExpired int
    EXEC sp_OAMethod @dtExp, 'ExpiresWithin', @bExpired OUT, 0, 'seconds'

    PRINT 'bExpired = ' + @bExpired

    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
    EXEC @hr = sp_OADestroy @jsonClaims
    EXEC @hr = sp_OADestroy @dtExp


END
GO