(SQL Server) Using the OAuth2 Authorization Token in REST API Calls

Demonstrates how to use an OAuth2 authorization token in REST API calls after obtaining it.

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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example demonstrates how to include the OAuth2 authorization token in HTTP requests (REST API calls).
    -- An OAuth2 authorization token is typically in JSON format, and looks something 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...rW5az09bI0C",
    --   "refresh_token": "MCZhZ...6jBNRcpuQW",
    --   "id_token": "eyJ0eXAi...kcuQQrT03jMyA",
    --   "expires_on": "1569281808"
    -- }

    -- A few notes about the JSON above:
    -- 
    -- 1) Different OAuth2 implementations (servers) may have different JSON members. 
    --    The important ones for this discussion are "access_token" and "refresh_token".   
    --    These members should always be named exactly "access_token" and "refresh_token".  
    --    (I've never seen them named differently, although I don't think it's a formal standard.)
    -- 
    -- 2) The "id_token" is present if you obtained the OAuth2 authorization token including "openid" in the scope.
    --    It contains information about the user.  It is a JWT (per the OIDC specification) and here is the Chilkat
    --    example for decoding the id_token.
    -- 
    -- 3) If you don't have a "refresh_token" in your JSON, some REST API's require "offline_access" to be included
    --    in the scope when obtaining the OAuth2 token.
    -- 
    -- 4) IMPORTANT: Quite often, access_token's are only valid for a limited amount of time.  (Often just 1 hour (i.e. 3600 seconds)).
    --    When the access token expires, your HTTP request will fail with a 401 Unauthorized status response.  This is where your application
    --    can automatically recover by fetching a new access_token and re-sending the request.  I'll explain...  
    --    Usually getting an OAuth2 token for a user requires interactive approval from the user in a browser.
    --    However, refreshing the access_token does NOT require user interaction.  You should design
    --    your application to automatically recover from an expired access token by 
    --    (A) Automatically fetch a new access_token using the refresh_token as shown in this example.
    --    (B) Persist the new JSON to wherever you're storing the access token, such as in a file or database record.  You'll need it for the next time you refresh.
    --    (C) Update the http.AuthToken or rest.Authorization property (as shown below)
    --    (D) Re-send the request using the updated auth token.
    --    The above 4 steps (A, B, C, D) can be automatic such that the user never notices, except for a small delay in performance.

    -- When your application obtains the OAuth2 access token, it should store the JSON in persistent manner, such as in 
    -- a file, a database record, etc.  The "access_token" is used by your application when sending REST requests.  Typically, it is sent
    -- in the Authorization request header.  For example:
    -- 
    -- Authorization: Bearer <token>
    -- 

    -- -----
    -- Chilkat has two classes for sending HTTP requests.  One is named "Http" and the other is named "Rest".  Either can be used.  
    -- Once you become familiar with both, you'll find that some requests are more convenient to code in one or the other.
    -- 
    -- I'll demonstrate how to get the access_token from the JSON and add the Authorization header for both cases.
    -- 

    -- ----
    -- ---- (1) Get the access_token ----
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/tokens/myToken.json'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Get the access_token member.
    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @accessToken OUT, 'access_token'

    -- ----
    -- ---- (2) Demonstrate adding the "Authorization: Bearer <token>" header using Chilkat Http ----
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Setting the AuthToken property causes the "Authorization: Bearer <token>" header to be added to each request.
    EXEC sp_OASetProperty @http, 'AuthToken', @accessToken

    -- For example:
    DECLARE @responseStr nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @responseStr OUT, 'https://example.com/someApiCall'

    -- Another example:
    DECLARE @req int
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    -- ...
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostUrlEncoded', @resp OUT, 'https://example.com/someApiCall', @req
    -- ...
    EXEC @hr = sp_OADestroy @resp

    -- In both of the above cases, the "Authorization: Bearer <token>" header is automatically added to each request.

    -- ----
    -- ---- (3) Add the Authorization header using Chilkat Rest ----
    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'example.com', 443, 1, 1
    -- ...

    -- Set the Authorization property to "Bearer <token>"
    DECLARE @sbAuthHeaderVal int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuthHeaderVal OUT

    EXEC sp_OAMethod @sbAuthHeaderVal, 'Append', @success OUT, 'Bearer '
    EXEC sp_OAMethod @sbAuthHeaderVal, 'Append', @success OUT, @accessToken
    EXEC sp_OAMethod @sbAuthHeaderVal, 'GetAsString', @sTmp0 OUT
    EXEC sp_OASetProperty @rest, 'Authorization', @sTmp0

    -- All requests sent by the rest object will now include the "Authorization: Bearer <token>" header.

    -- For example:
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseStr OUT, 'GET', '/someApiCall'

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbAuthHeaderVal


END
GO