SQL Server

Decode Microsoft Graph ID Token

See more Microsoft Graph Examples

Demonstrates how to decode a Microsoft Graph ID token. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    --  This example loads an id_token and decodes it.

    --  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
    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/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
    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
    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
    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