(SQL Server) Decode Microsoft Graph ID Token

Demonstrates how to decode a Microsoft Graph 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.

    -- In a previous example, we obtained a Microsoft Graph OAuth2 access token.
    -- This example loads the JSON saved from the previous example and decodes the id_token.

    -- Our Microsoft Graph OAuth2 token looks like this:

    -- {
    --   "token_type": "Bearer",
    --   "scope": "openid profile User.ReadWrite Mail.ReadWrite Mail.Send Files.ReadWrite User.Read Calendars.ReadWrite Group.ReadWrite.All",
    --   "expires_in": 3600,
    --   "ext_expires_in": 3600,
    --   "access_token": "EwCQA8l6...0HhMKYwC",
    --   "refresh_token": "MCWulIvzi2yD0S...igEFn51mqcByhZtAJg",
    --   "id_token": "eyJ0eXAiOiJKV1...Q7lRDaR-7A",
    --   "expires_on": "1562862714"
    -- }

    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/microsoftGraph.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:

    -- {
    --   "typ": "JWT",
    --   "alg": "RS256",
    --   "kid": "1LTMzakihiRla_8z2BEJVXeWMqo"
    -- }

    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:

    -- {
    --   "ver": "2.0",
    --   "iss": "https://login.microsoftonline.com/9188040d-6c67-4c5b-b112-36a304b66dad/v2.0",
    --   "sub": "AAAAAAAAAAAAAAAAAAAAAHJFryd6Gydo-XtTd1nhUNQ",
    --   "aud": "18c456bd-db75-43fe-9724-9e5d821c68ff",
    --   "exp": 1562945513,
    --   "iat": 1562858813,
    --   "nbf": 1562858813,
    --   "name": "Matt Chilkat",
    --   "preferred_username": "matt@example.com",
    --   "oid": "00000000-0000-0000-3a33-fceb9b74cc15",
    --   "tid": "9188040d-6c67-4c5b-b112-36a304b66dad",
    --   "aio": "DfibJqKnWC1c0FS6G ... W6pvTrQuYzyq16ghY$"
    -- }
    -- 

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- Get each of the claims..
    DECLARE @ver nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @ver OUT, 'ver'
    DECLARE @iss nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @iss OUT, 'iss'
    DECLARE @s_sub nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @s_sub OUT, 'sub'
    DECLARE @aud nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @aud OUT, 'aud'
    DECLARE @exp int
    EXEC sp_OAMethod @jsonClaims, 'IntOf', @exp OUT, 'exp'
    DECLARE @iat int
    EXEC sp_OAMethod @jsonClaims, 'IntOf', @iat OUT, 'iat'
    DECLARE @nbf int
    EXEC sp_OAMethod @jsonClaims, 'IntOf', @nbf OUT, 'nbf'
    DECLARE @name nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @name OUT, 'name'
    DECLARE @preferred_username nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @preferred_username OUT, 'preferred_username'
    DECLARE @oid nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @oid OUT, 'oid'
    DECLARE @tid nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @tid OUT, 'tid'
    DECLARE @aio nvarchar(4000)
    EXEC sp_OAMethod @jsonClaims, 'StringOf', @aio OUT, 'aio'

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


END
GO