(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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

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

    DECLARE @mailman int
    -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT

    DECLARE @success int
    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
            -- Use "Chilkat_9_5_0.OAuth2" for versions of Chilkat < 10.0.0
            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
            -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
            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