SQL Server
SQL Server
Verify Okta ID Token Locally
See more Okta OAuth/OIDC Examples
This example demonstrates how to validate an Okta ID token using Chilkat's JWT class.Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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
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
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
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
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 @hr = sp_OACreate 'Chilkat.JsonObject', @jsonWebKey OUT
EXEC sp_OAMethod @jsonWebKeys, 'ObjectOf2', @success OUT, 'keys[i]', @jsonWebKey
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 @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 @jsonWebKey
RETURN
END
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
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
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 @jsonWebKey
EXEC @hr = sp_OADestroy @jsonClaims
EXEC @hr = sp_OADestroy @dtExp
END
GO