SQL Server

OAuth2 for a GMail using a P12 Service Account Key

See more GMail REST API Examples

Demonstrates how to use GMail with OAuth2 for a service account within a Google Workspace Account where your email domain is custom (e.g., @yourcompany.com).

Note: This example does not work for Personal Google Accounts where the email domain is @gmail.com

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

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

    -- --------------------------------------------------------------------------------
    -- 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
    -- --------------------------------------------------------------------------------
    -- Begin by loading your Google service account key (.p12)
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'c:/someDirectory/keys/chilkat25-cbd7b42afbd8.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 ISS is your service account email address ending in gserviceaccount.com.
    DECLARE @iss nvarchar(4000)
    SELECT @iss = 'chilkatsvc@chilkat25.iam.gserviceaccount.com'

    -- The scope is always the following string:
    DECLARE @scope nvarchar(4000)
    SELECT @scope = 'https://mail.google.com/'

    -- The sub is your company email address
    DECLARE @oauth_sub nvarchar(4000)
    SELECT @oauth_sub = 'bob@yourcompany.com'

    -- The access token is valid for this number of seconds.
    DECLARE @numSec int
    SELECT @numSec = 3600

    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @http, 'G_SvcOauthAccessToken', @accessToken OUT, @iss, @scope, @oauth_sub, @numSec, @cert
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    ELSE
      BEGIN

        PRINT 'access token: ' + @accessToken
      END

    -- The access token allows us to send unlimited emails while it's valid. Once it expires, we must obtain and use a new one.

    -- -----------------------------------------------------------------------
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

    -- Set the properties for the GMail SMTP server:
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.gmail.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
    EXEC sp_OASetProperty @mailman, 'StartTLS', 1

    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'bob@yourcompany.com'
    EXEC sp_OASetProperty @mailman, 'OAuth2AccessToken', @accessToken

    -- Create a new email object
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OASetProperty @email, 'Subject', 'This is a test'
    EXEC sp_OASetProperty @email, 'Body', 'This is a test'
    EXEC sp_OASetProperty @email, 'From', 'Bob <bob@yourcompany.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Recipient', 'recipient@example.com'
    -- To add more recipients, call AddTo, AddCC, or AddBcc once per recipient.

    EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

        PRINT 'Connection to SMTP server not closed cleanly.'
      END


    PRINT 'Successfully sent email using Gmail with a service account key.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @email


END
GO