(SQL Server) Office365 POP3 with Client Credentials

See more Office365 Examples

Demonstrates how to interact with the Office365 POP3 server using OAuth2 client credentials. This example uses a new feature available starting in Chilkat v9.5.0.97, which is to provide the client ID, client secret, and tenant ID to Chilkat, and internally Chilkat will fetch OAuth2 access token via client credentials as necessary. Therefore, it is no longer necessary for the application to explicitly fetch the OAuth2 access token beforehand.

Note: This requires the Azure Entra ID application to be configured for client credentials as detailed in this guide: Office365 App Setup for SMTP, POP, IMAP OAuth2 Client Credentials

Note: This example requires Chilkat v9.5.0.97 or greater.

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
    -- 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 @success int

    -- ----------------------------------------------------------------------------------------
    -- Important:
    -- Your Azure Entra ID app must be explicitly setup to allow for the OAuth2 client credentials flow.
    -- Please follow the detailed guide for how to do it here:
    -- Office365 App Setup for SMTP, POP, IMAP OAuth2 Client Credentials
    -- 
    -- Note: Your registered App must have the SMTP.SendAsApp permission.
    -- ----------------------------------------------------------------------------------------

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use your App's actual client ID here:
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', '2871da2c-8176-4b7f-869b-2311aa82e743'

    -- Use your App's actual client secret here:
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', '2hu9Q~r5QuryUcEkNbg1btLtnfU1VUXzhSCG6brH'

    -- Use this specific scope.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'scope', 'https://outlook.office365.com/.default'

    -- Replace the tenant ID part of the URL with your tenant ID.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/114d7ed6-71bf-4dbe-a866-748364121bf2/oauth2/v2.0/token'

    DECLARE @mailman int
    -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

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

    -- Here is where we use a new feature introduced in Chilkat v9.5.0.97
    -- Instead of providing a previously fetched OAuth2 access token, we instead provide the information (above)
    -- required for Chilkat to automatically fetch the access token via client credentials.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    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 @json
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      END

    -- Authenticate using XOAUTH2
    EXEC sp_OAMethod @mailman, 'Pop3Authenticate', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      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 @json
        EXEC @hr = sp_OADestroy @mailman
        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. [QwBIADIAUABSADEAOABD...YwBvAG0A]
    -- > AUTH XOAUTH2
    -- < + 
    -- > <base64 string in XOAUTH2 format>
    -- < +OK User successfully authenticated.
    -- > STAT
    -- < +OK 3 375302

    -- 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 @json
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      END


    PRINT 'Finished.'

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @mailman


END
GO