SQL Server

Refresh OAuth2 Access Token with Optional Params

See more OAuth2 Examples

Demonstrates how to refresh an OAuth2 access token with optional query params included in the HTTP request. 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- Here is a sampling of possible optional parameters that might
    -- be used by some OAuth2 providers.

    -- Optional Parameters
    -- 
    --     "scope":
    --         Specifies the scope of the access request. If omitted, the authorization
    --         server may issue a token with the same scope as the original token.
    --         Example: "scope=read write"
    -- 
    --     "redirect_uri":
    --         The redirect URI used in the original authorization request. Some
    --         servers may require this for validation.
    --         Example: "redirect_uri=https://example.com/callback"
    -- 
    --     "resource":
    --         Indicates the target resource or audience for the token. This is used in
    --         some implementations (e.g., Microsoft Identity Platform).
    --         Example: "resource=https://api.example.com"
    -- 
    --     "audience":
    --         Similar to "resource", this specifies the intended audience for the
    --         token (used in some OAuth2 implementations like Auth0).
    --         Example: "audience=https://api.example.com"
    -- 
    --     "client_assertion" and "client_assertion_type":
    --         Used for client authentication using a signed JWT instead of a client
    --         secret.
    --         Example:client_assertion=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...        
    --         client_assertion_type=urn:ietf:params:oauth:client-assertion-type:jwt-bearer
    -- 
    --     "token_type_hint":
    --         Provides a hint to the authorization server about the type of token
    --         being refreshed. This is rarely used but can be helpful in some cases.
    --         Example: "token_type_hint=refresh_token"
    -- 
    --     "assertion":
    --         Used in some flows (e.g., SAML bearer assertion flow) to provide an
    --         assertion for token issuance.
    --         Example: "assertion=PHNhbWxwOl..."
    -- 
    --     "requested_token_use":
    --         Specifies how the token will be used (e.g., "on_behalf_of" in the
    --         On-Behalf-Of flow used by Microsoft Identity Platform).
    --         Example: "requested_token_use=on_behalf_of"
    -- 

    -- --------------------------------------------------------------------------------
    -- This example wll refresh the access token and includes the "audience"
    -- optional query parameter.
    -- 
    -- 

    -- Get the access token to be refreshed.
    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/myAccessToken.json'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load hmrc.json'
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    DECLARE @oauth2 int
    EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT

    EXEC sp_OASetProperty @oauth2, 'TokenEndpoint', 'https://api.example.com/oauth/token'

    -- Replace these with actual values.
    EXEC sp_OASetProperty @oauth2, 'ClientId', 'CLIENT_ID'
    EXEC sp_OASetProperty @oauth2, 'ClientSecret', 'CLIENT_SECRET'

    -- Add the optional refresh query param.
    -- Call AddRefreshQueryParam multiple times to add additional params.
    EXEC sp_OAMethod @oauth2, 'AddRefreshQueryParam', @success OUT, 'audience', 'https://api.example.com'

    -- Provide the existing refresh token from the JSON.
    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'refresh_token'
    EXEC sp_OASetProperty @oauth2, 'RefreshToken', @sTmp0

    -- Send the HTTP POST to refresh the access token..
    EXEC sp_OAMethod @oauth2, 'RefreshAccessToken', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @oauth2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @oauth2
        RETURN
      END

    -- Load the access token response into the json object 
    EXEC sp_OAGetProperty @oauth2, 'AccessTokenResponse', @sTmp0 OUT
    EXEC sp_OAMethod @jsonToken, 'Load', @success OUT, @sTmp0

    -- Save the new JSON access token response to a file.
    -- The access + refresh tokens contained in this JSON will be needed for the next refresh.
    DECLARE @sbJson int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJson OUT

    EXEC sp_OASetProperty @jsonToken, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonToken, 'EmitSb', @success OUT, @sbJson
    EXEC sp_OAMethod @sbJson, 'WriteFile', @success OUT, 'qa_data/tokens/myAccessToken.json', 'utf-8', 0


    PRINT 'OAuth2 access token refreshed!'

    EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT
    PRINT 'New Access Token = ' + @sTmp0

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @oauth2
    EXEC @hr = sp_OADestroy @sbJson


END
GO