![]() |
Chilkat HOME Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi DLL Go Java Node.js Objective-C PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(SQL Server) OAuth2 for a GMail using a JSON Service Account KeyThis example shows how to obtain an OAuth2 access token for Gmail using a Google Service Account and a JSON private key. Once acquired, the access token can be used to send emails. Remember, upon token expiration, this process needs to be repeated to obtain a new token. Note: This procedure is specific to OAuth2 with Google Service Account keys.
-- 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) -- This example requires 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 -- -------------------------------------------------------------------------------- -- First load the JSON key into a string. DECLARE @fac int EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT IF @hr <> 0 BEGIN PRINT 'Failed to create ActiveX component' RETURN END DECLARE @jsonKey nvarchar(4000) EXEC sp_OAMethod @fac, 'ReadEntireTextFile', @jsonKey OUT, 'qa_data/googleApi/chilkat25-b4214220e565.json', 'utf-8' EXEC sp_OAGetProperty @fac, 'LastMethodSuccess', @iTmp0 OUT IF @iTmp0 <> 1 BEGIN EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @fac RETURN END -- A Google service account JSON private key looks like this: -- { -- "type": "service_account", -- "project_id": "chilkat25", -- "private_key_id": "b4214220f565881e19eeb97c2699bf5a0d1e3e0b", -- "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQ...NXcM=\n-----END PRIVATE KEY-----\n", -- "client_email": "chilkatsvc@chilkat25.iam.gserviceaccount.com", -- "client_id": "109122032928932715958", -- "auth_uri": "https://accounts.google.com/o/oauth2/auth", -- "token_uri": "https://oauth2.googleapis.com/token", -- "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", -- "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/chilkatsvc%40chilkat25.iam.gserviceaccount.com", -- "universe_domain": "googleapis.com" -- } DECLARE @gAuth int EXEC @hr = sp_OACreate 'Chilkat.AuthGoogle', @gAuth OUT EXEC sp_OASetProperty @gAuth, 'JsonKey', @jsonKey -- Specify a scope. EXEC sp_OASetProperty @gAuth, 'Scope', 'https://mail.google.com/' -- Request an access token that is valid for this many seconds. EXEC sp_OASetProperty @gAuth, 'ExpireNumSeconds', 3600 -- When using a Google Workspace account with Gmail APIs, a service account can impersonate a user -- via a process called domain-wide delegation � and the "sub" claim in the JWT is what enables this. -- Domain-wide delegation allows a Google Workspace administrator to authorize a service account to -- act on behalf of any user in the domain, without user interaction. -- This is required for server-to-server access to user data � such as reading/sending Gmail from a background service. -- This is your company email address. EXEC sp_OASetProperty @gAuth, 'SubEmailAddress', 'info@chilkat.xyz' -- Connect to www.googleapis.com using TLS DECLARE @tlsSock int EXEC @hr = sp_OACreate 'Chilkat.Socket', @tlsSock OUT DECLARE @success int EXEC sp_OAMethod @tlsSock, 'Connect', @success OUT, 'www.googleapis.com', 443, 1, 5000 IF @success <> 1 BEGIN EXEC sp_OAGetProperty @tlsSock, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @fac EXEC @hr = sp_OADestroy @gAuth EXEC @hr = sp_OADestroy @tlsSock RETURN END -- Send the request to obtain the access token. EXEC sp_OAMethod @gAuth, 'ObtainAccessToken', @success OUT, @tlsSock IF @success <> 1 BEGIN EXEC sp_OAGetProperty @gAuth, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @fac EXEC @hr = sp_OADestroy @gAuth EXEC @hr = sp_OADestroy @tlsSock RETURN END -- Examine the access token: DECLARE @accessToken nvarchar(4000) EXEC sp_OAGetProperty @gAuth, 'AccessToken', @accessToken OUT PRINT 'Access Token: ' + @accessToken -- Sample output: -- ya29.a0AW4XtxjGTD67Z8 .... IRw0218 -- 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', 'info@chilkat.xyz' 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', 'Chilkat Test <info@chilkat.xyz>' EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Software', 'info@chilkatsoft.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 @fac EXEC @hr = sp_OADestroy @gAuth EXEC @hr = sp_OADestroy @tlsSock 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 @fac EXEC @hr = sp_OADestroy @gAuth EXEC @hr = sp_OADestroy @tlsSock EXEC @hr = sp_OADestroy @mailman EXEC @hr = sp_OADestroy @email END GO |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.