SQL Server

Get OAuth 2.0 Access Token using G_SvcOauthAccessToken2

Obtains an OAUTH2 access token from the Google OAuth 2.0 Authorization Server using the G_SvcOauthAccessToken2 method. This is for Server to server applications using Google API's that need an access token. See https://developers.google.com/accounts/docs/OAuth2ServiceAccount 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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

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

    -- --------------------------------------------------------------------------------
    -- For a step-by-step guide for setting up your Google Workspace service account,
    -- see Setup Google Workspace Account for Sending SMTP GMail from a Service Account
    -- --------------------------------------------------------------------------------

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

    -- When a service account (Client ID) is created at https://code.google.com/apis/console/
    -- Google will generate a P12 key.  This is a PKCS12 (PFX) file that you will download
    -- and save.  The password to access the contents of this file is "notasecret".
    -- NOTE: The Chilkat Pfx API provides the ability to load a PFX/P12 and re-save
    -- with a different password.

    -- Begin by loading the downloaded .p12 into a Chilkat certificate object:
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, '/myDir/API Project-1c43a291e2a1-notasecret.p12', 'notasecret'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- The next (and final) step is to request the access token.  Chilkat internally
    -- does all the work of forming the JWT header and JWT claim set, encoding and
    -- signing the JWT, and sending the access token request.
    -- The application need only provide the inputs: The iss, scope(s), sub, and the
    -- desired duration with a max of 3600 seconds (1 hour).
    -- 
    -- Each of these inputs is defined as follows 
    -- (see https://developers.google.com/accounts/docs/OAuth2ServiceAccount
    -- iss: The email address of the service account.
    -- scope: A space-delimited list of the permissions that the application requests.
    -- sub: The email address of the user for which the application is requesting delegated access.
    -- numSec: The number of seconds for which the access token will be valid (max 3600).

    DECLARE @claimParams int
    EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @claimParams OUT

    -- The required claim parameters that must be provided by the application are
    -- the "iss" and "scope" params:
    EXEC sp_OAMethod @claimParams, 'AddStr', @success OUT, 'iss', '761326798069-r5mljlln1rd4lrbhg75efgigp36m78j5@developer.gserviceaccount.com'
    EXEC sp_OAMethod @claimParams, 'AddStr', @success OUT, 'scope', 'https://mail.google.com/'

    -- The "sub" param is required if there is an email address, such as for a
    -- Google Apps domain—if you use Google Apps for Work, where the administrator of the Google Apps domain 
    -- can authorize an application to access user data on behalf of users in the Google Apps domain.
    -- 
    -- Comment out this line if there is no such email address.
    EXEC sp_OAMethod @claimParams, 'AddStr', @success OUT, 'sub', 'user@your-domain.com'

    -- The "aud" parameter is optional.  If not set, it defaults to the value shown here:
    EXEC sp_OAMethod @claimParams, 'AddStr', @success OUT, 'aud', 'https://accounts.google.com/o/oauth2/token'

    DECLARE @numSec int
    SELECT @numSec = 3600

    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @http, 'G_SvcOauthAccessToken2', @accessToken OUT, @claimParams, @numSec, @cert
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END
    ELSE
      BEGIN

        PRINT 'access token: ' + @accessToken
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @claimParams


END
GO