SQL Server

Office365 IMAP OAuth2 with Shared Mailbox

See more Office365 Examples

Demonstrates how to authenticate using OAuth2 with shared mailboxes in outlook.office365.com. (SASL XOAUTH2 authentication for shared mailboxes in Office 365) 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- An Office365 OAuth2 access token must first be obtained prior
    -- to running this code.

    -- Getting the OAuth2 access token for the 1st time requires the O365 account owner's 
    -- interactive authorizaition via a web browser.  Afterwards, the access token
    -- can be repeatedly refreshed automatically.

    -- See the following examples for getting and refreshing an OAuth2 access token

    -- Get Office365 SMTP/IMAP/POP3 OAuth2 Access Token
    -- Refresh Office365 SMTP/IMAP/POP3 OAuth2 Access Token

    -- First get our previously obtained OAuth2 access token.
    DECLARE @jsonToken int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/office365.json'
    IF @success = 0
      BEGIN

        PRINT 'Failed to open the office365 OAuth JSON file.'
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT

    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993

    -- Connect to the Office365 IMAP server.
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'outlook.office365.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Use OAuth2 authentication.
    EXEC sp_OASetProperty @imap, 'AuthMethod', 'XOAUTH2'

    -- In case of shared mailbox access using OAuth, application needs to obtain the access token on behalf of a user
    -- but replace the userName field in the SASL XOAUTH2 encoded string with the email address of the shared mailbox.

    -- Login using the shared mailbox email address for the username, and the access token for the password.
    -- This will cause the SASL XOAUTH2 format to be used, as described at Office 365 SASL XOAUTH2 format 

    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'SHARED_MAILBOX_EMAIL_ADDRESS', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END


    PRINT 'O365 OAuth2 shared mailbox authentication is successful.'

    -- Do something...
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Your application can continue to do other things in the IMAP session....

    -- When finished, logout and close the connection.
    EXEC sp_OAMethod @imap, 'Logout', @success OUT
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT


    PRINT 'Finished.'

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @imap


END
GO