SQL Server
SQL Server
POP3 Auto-Refresh Office365 Access Token
See more Office365 Examples
Demonstrates how to automatically recover from an expired OAuth2 access token when OAuth2 authentication fails in the POP3 protocol. If the server responds with "-ERR Authentication failure: unknown user name or bad password.", then we refresh the access token and retry.Chilkat SQL Server Downloads
-- 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 @mailman int
EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @mailman, 'MailHost', 'outlook.office365.com'
EXEC sp_OASetProperty @mailman, 'MailPort', 995
EXEC sp_OASetProperty @mailman, 'PopSsl', 1
-- Use your O365 email address here.
EXEC sp_OASetProperty @mailman, 'PopUsername', 'OFFICE365_EMAIL_ADDRESS'
-- When using OAuth2 authentication, leave the password empty.
EXEC sp_OASetProperty @mailman, 'PopPassword', ''
-- Load our previously obtained OAuth2 access token.
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/office365.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @mailman, 'OAuth2AccessToken', @sTmp0
-- Make the TLS connection to the outlook.office365.com POP3 server.
EXEC sp_OAMethod @mailman, 'Pop3Connect', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
-- Authenticate using XOAUTH2
EXEC sp_OAMethod @mailman, 'Pop3Authenticate', @success OUT
IF @success <> 1
BEGIN
-- If we're still connected to the mail server, then it means the server sent a non-success response,
-- Such as: -ERR Authentication failure: unknown user name or bad password.
EXEC sp_OAGetProperty @mailman, 'IsPop3Connected', @iTmp0 OUT
IF @iTmp0 = 1
BEGIN
-- Refresh the OAuth2 access token, and if successful, save the new (refreshed) access token and try authenticating again.
DECLARE @oauth2 int
EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT
-- Use your actual Directory (tenant) ID instead of "112d7ed6-71bf-4eba-a866-738364321bfc"
EXEC sp_OASetProperty @oauth2, 'TokenEndpoint', 'https://login.microsoftonline.com/112d7ed6-71bf-4eba-a866-738364321bfc/oauth2/v2.0/token'
-- Replace these with your Azure App Registration's actual values.
EXEC sp_OASetProperty @oauth2, 'ClientId', 'CLIENT_ID'
EXEC sp_OASetProperty @oauth2, 'ClientSecret', 'CLIENT_SECRET'
-- Get the "refresh_token"
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 @mailman
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT
PRINT 'New access token: ' + @sTmp0
EXEC sp_OAGetProperty @oauth2, 'RefreshToken', @sTmp0 OUT
PRINT 'New refresh token: ' + @sTmp0
-- Update the JSON with the new tokens.
EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT
EXEC sp_OAMethod @jsonToken, 'UpdateString', @success OUT, 'access_token', @sTmp0
EXEC sp_OAGetProperty @oauth2, 'RefreshToken', @sTmp0 OUT
EXEC sp_OAMethod @jsonToken, 'UpdateString', @success OUT, 'refresh_token', @sTmp0
-- Save the new JSON access token response to a file.
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/office365.json', 'utf-8', 0
EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT
PRINT 'New Access Token = ' + @sTmp0
-- Update the mailman with the new access token.
EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT
EXEC sp_OASetProperty @mailman, 'OAuth2AccessToken', @sTmp0
-- Retry the authentication.
EXEC sp_OAMethod @mailman, 'Pop3Authenticate', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbJson
RETURN
END
END
ELSE
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbJson
RETURN
END
END
-- Find out how many emails are on the server..
DECLARE @numEmails int
EXEC sp_OAMethod @mailman, 'CheckMail', @numEmails OUT
IF @numEmails < 0
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbJson
RETURN
END
-- Examine the POP3 session log:
EXEC sp_OAGetProperty @mailman, 'Pop3SessionLog', @sTmp0 OUT
PRINT @sTmp0
-- The POP3 session log will look something like this:
-- **** Connected to outlook.office365.com:995
-- < +OK The Microsoft Exchange POP3 service is ready. [QwBIADIAUABSADEANgBDAEEAMAAwADEAMgAuAG4AYQBtAHAAcgBkADEANgAuAHAAcgBvAGQALgBvAHUAdABsAG8AbwBrAC4AYwBvAG0A]
-- > AUTH XOAUTH2
-- < +
-- > <base64 string in XOAUTH2 format>
-- < -ERR Authentication failure: unknown user name or bad password.
-- > AUTH XOAUTH2
-- < +
-- > <base64 string in XOAUTH2 format>
-- < +OK User successfully authenticated.
-- > STAT
-- < +OK 248 46637086
-- End the POP3 session and close the connection to the GMail server.
EXEC sp_OAMethod @mailman, 'Pop3EndSession', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbJson
RETURN
END
PRINT 'Finished.'
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbJson
END
GO