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