(SQL Server) Validate the at_hash Claim of an ID Token

Demonstrates how to hash an access token to compare it with the at_hash claim of an ID 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example uses a Google access_token + id_token that looks like this:

    --  {
    --   "access_token": "ya29.a0...0f",
    --   "expires_in": 3599,
    --   "scope": "openid https://www.googleapis.com/auth/userinfo.email",
    --   "token_type": "Bearer",
    --   "id_token": "eyJhb...o5nQ"
    -- }

    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/google_sample_id_token.json'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load the JSON file...'
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    -- Use Chilkat's JWT API to examine the id_token..
    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'

    -- Extract the JOSE header..
    DECLARE @jose nvarchar(4000)
    EXEC sp_OAMethod @jwt, 'GetHeader', @jose OUT, @idToken

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

    EXEC sp_OAMethod @jsonHeader, 'Load', @success OUT, @jose
    EXEC sp_OASetProperty @jsonHeader, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonHeader, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The JOSE header looks like this:

    -- {
    --   "alg": "RS256",
    --   "kid": "e8799db06287515556213c80acbcfd022fb302a9",
    --   "typ": "JWT"
    -- }

    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

    -- The claims look like this:

    -- {
    --   "iss": "https://accounts.google.com",
    --   "azp": "258999997753-5ni8lu5f15r7mno97d82f5lir9i9f6i1.apps.googleusercontent.com",
    --   "aud": "258999997753-5ni8lu5f15r7mno97d82f5lir9i9f6i1.apps.googleusercontent.com",
    --   "sub": "111787341816486547572",
    --   "email": "somebody@gmail.com",
    --   "email_verified": true,
    --   "at_hash": "HYJZImlW3mUK-UfjRfXjKw",
    --   "iat": 1615315968,
    --   "exp": 1615319568
    -- }

    -- The at_hash is the Access Token hash value. Its value is the base64url encoding of the
    -- left-most half of the hash of the octets of the ASCII representation of the access_token value,
    -- where the hash algorithm used is the hash algorithm used in the alg Header Parameter of the
    -- ID Token's JOSE Header. For instance, if the alg is RS256, hash the access_token value with SHA-256,
    -- then take the left-most 128 bits and base64url encode them. The at_hash value is a case sensitive string.

    DECLARE @token_to_hash nvarchar(4000)
    EXEC sp_OAMethod @jsonToken, 'StringOf', @token_to_hash OUT, 'access_token'
    DECLARE @token_hash_expected nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @token_hash_expected OUT, 'at_hash'

    -- Step 1. hashes the access token using SHA-256 (Google uses `RS256` as the ID Token `alg`).
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

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

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    -- This encoding mode must match the encoding mode passed in the 2nd arg to AppendEncoded.
    -- The encoding mode can be anything, as long as they are the same in both places.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'

    EXEC sp_OAMethod @crypt, 'HashStringENC', @sTmp0 OUT, @token_to_hash
    EXEC sp_OAMethod @bdHash, 'AppendEncoded', @success OUT, @sTmp0, 'hex'
    DECLARE @sz int
    EXEC sp_OAGetProperty @bdHash, 'NumBytes', @sz OUT

    DECLARE @token_hash_computed nvarchar(4000)
    EXEC sp_OAMethod @bdHash, 'GetEncodedChunk', @token_hash_computed OUT, 0, @sz / 2, 'base64url'

    -- If the hashes are identical, then the access_token as issued for the given id_token.

    PRINT 'token_hash_expected: ' + @token_hash_expected

    PRINT 'token_hash_computed: ' + @token_hash_computed

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @jwt
    EXEC @hr = sp_OADestroy @jsonHeader
    EXEC @hr = sp_OADestroy @jsonClaims
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bdHash


END
GO