(SQL Server) Get an Azure AD Access Token

Demonstrates how to obtain an Azure AD access token for authentication using a client ID, client secret, and tenant ID.

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 @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @socket int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Socket', @socket OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Make a TLS connection to login.microsoftonline.com, waiting at most 5000 milliseconds.
    DECLARE @success int
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'login.microsoftonline.com', 443, 1, 5000
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Create an Azure AD auth object, and provide the required information for authorization.
    DECLARE @azureAD int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.AuthAzureAD', @azureAD OUT

    EXEC sp_OASetProperty @azureAD, 'ClientId', 'AZURE_AD_CLIENT_ID'
    EXEC sp_OASetProperty @azureAD, 'ClientSecret', 'AZURE_AD_CLIENT_SECRET'
    EXEC sp_OASetProperty @azureAD, 'TenantId', 'AZURE_TENANT_ID'

    EXEC sp_OASetProperty @azureAD, 'Resource', 'https://outlook.office365.com/'

    -- Retrieve the access token using the TLS connection to login.microsoftonline.com
    EXEC sp_OAMethod @azureAD, 'ObtainAccessToken', @success OUT, @socket
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @azureAD
        RETURN
      END

    -- Show the access token, and then save it to a JSON file
    -- for future use (such as with a REST method call).

    EXEC sp_OAGetProperty @azureAD, 'AccessToken', @sTmp0 OUT
    PRINT 'Azure AD Access Token = ' + @sTmp0

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAGetProperty @azureAD, 'AccessToken', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'accessToken', @sTmp0

    -- Save our access token to a file.  It will be used in subsequent REST API calls.
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.FileAccess', @fac OUT

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'qa_data/tokens/azureAD_office365.json', @sTmp0, 'utf-8', 0

    -- Let's get the id information out of the access token.
    -- Our JSON looks like this:
    -- {"accessToken":"eyJ0eXAiO---TdjT3RjIn0.eyJhdWQiOiJo---jEuMCJ9.CIx0sUT8s---KvzKKUw"}

    -- I used "---" instead of "..." to indicate a large quantity of omitted chars.
    -- The accessToken is a long string composed of 3 base64 strings concatenated with "." chars.
    -- The 1st part is the JOSE header.  If you decode from base64, you'll get the JSON JOSE header.
    -- The 2nd part is the id token.  When decoded this is the JSON that contains information about the authenticated application.
    -- The 3rd part is binary and does not decode to JSON.

    -- Let's get the information from the 2nd part (the id token)
    DECLARE @sa int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringArray', @sa OUT

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'accessToken'
    EXEC sp_OAMethod @sa, 'SplitAndAppend', NULL, @sTmp0, '.'

    DECLARE @sbIdToken int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbIdToken OUT

    -- The 2nd string is at index 1.
    EXEC sp_OAMethod @sa, 'GetString', @sTmp0 OUT, 1
    EXEC sp_OAMethod @sbIdToken, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbIdToken, 'Decode', @success OUT, 'base64', 'utf-8'

    DECLARE @jsonIdToken int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jsonIdToken OUT

    EXEC sp_OAMethod @jsonIdToken, 'LoadSb', @success OUT, @sbIdToken
    EXEC sp_OASetProperty @jsonIdToken, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonIdToken, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- We have something like this:
    -- {
    --   "aud": "https://outlook.office365.com/",
    --   "iss": "https://sts.windows.net/6e8ddd66-68d1-43b0-af5c-e31b4b7dd5cd/",
    --   "iat": 1586350465,
    --   "nbf": 1586350465,
    --   "exp": 1586354365,
    --   "aio": "42dgYNjyOtX8ZZB7JLfMFJGeKUmvAA==",
    --   "app_displayname": "ChilkatTest",
    --   "appid": "f125d695-c50e-456e-a578-a486f06d1213",
    --   "appidacr": "1",
    --   "idp": "https://sts.windows.net/6d8ddd66-68d1-43b0-af5c-e31b4b7dd5cd/",
    --   "oid": "7545a2fd-3f0d-48a4-9c58-a1a5700a24b1",
    --   "sid": "ab981252-9378-4d0c-964b-eb2e1451138f",
    --   "sub": "7546a2fc-3f0d-48a4-9c58-a1a5700a24b1",
    --   "tid": "6d8ddd66-68d1-43b0-af5c-e31b4b7dd5cd",
    --   "uti": "huIJBAa1tvGpczwV5S1BAA",
    --   "ver": "1.0"
    -- }

    -- Get the desired information from the JSON:
    DECLARE @aud nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @aud OUT, 'aud'
    DECLARE @iss nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @iss OUT, 'iss'
    DECLARE @iat int
    EXEC sp_OAMethod @jsonIdToken, 'IntOf', @iat OUT, 'iat'
    DECLARE @nbf int
    EXEC sp_OAMethod @jsonIdToken, 'IntOf', @nbf OUT, 'nbf'
    DECLARE @exp int
    EXEC sp_OAMethod @jsonIdToken, 'IntOf', @exp OUT, 'exp'
    DECLARE @aio nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @aio OUT, 'aio'
    DECLARE @app_displayname nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @app_displayname OUT, 'app_displayname'
    DECLARE @appid nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @appid OUT, 'appid'
    DECLARE @appidacr nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @appidacr OUT, 'appidacr'
    DECLARE @idp nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @idp OUT, 'idp'
    DECLARE @oid nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @oid OUT, 'oid'
    DECLARE @sid nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @sid OUT, 'sid'
    DECLARE @s_sub nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @s_sub OUT, 'sub'
    DECLARE @tid nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @tid OUT, 'tid'
    DECLARE @uti nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @uti OUT, 'uti'
    DECLARE @ver nvarchar(4000)
    EXEC sp_OAMethod @jsonIdToken, 'StringOf', @ver OUT, 'ver'

    EXEC @hr = sp_OADestroy @socket
    EXEC @hr = sp_OADestroy @azureAD
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @sa
    EXEC @hr = sp_OADestroy @sbIdToken
    EXEC @hr = sp_OADestroy @jsonIdToken


END
GO