(SQL Server) Office365 OAuth2 Client Credentials Flow for SMTP, IMAP, POP

See more Office365 Examples

Demonstrates how to get an OAuth2 access token using the Client Credentials flow for use with Office 365 in the SMTP, IMAP, and POP3 protocols.

This is a way of getting an OAuth2 access token for the O365 account you own, WITHOUT needing to grant access interactively via a browser.

Note: OAuth2 access tokens acquired using client credentials are not refreshed. This is because no interactive browser authentication was initially required. You can simply fetch another access token using client credentials using this sample code. Access tokens aquired using client credentials typically have a lifetime of 1 hour.

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.

    -- Get an OAuth2 access token by sending a POST like this:

    -- 	POST {tenant}/oauth2/v2.0/token
    -- 	Host: login.microsoftonline.com
    -- 	Content-Type: application/x-www-form-urlencoded
    -- 
    -- 	client_id=6731de76-14a6-49ae-97bc-6eba6914391e
    -- 	&client_secret=*****************
    -- 	&scope=https%3A%2F%2Foutlook.office365.com%2F.default
    -- 	&grant_type=client_credentials

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

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

    -- ----------------------------------------------------------------------------------------
    -- 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 following permissions.
    -- For IMAP: IMAP.AccessAsApp
    -- For POP3: POP.AccessAsApp
    -- For SMTP: SMTP.SendAsApp
    -- ----------------------------------------------------------------------------------------

    -- Use the application ID for the client_id.
    -- (In Azure App Registrations, use the Application (client) ID)
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', 'CLIENT_ID'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_secret', 'CLIENT_SECRET'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'scope', 'https://outlook.office365.com/.default'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'

    DECLARE @resp int

    -- Replace "{tenant}" with your tenant ID, such as "112d7ed6-71bf-4eba-a866-738364321bfc".
    EXEC sp_OAMethod @http, 'PostUrlEncoded', @resp OUT, 'https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token', @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT

    PRINT 'response status code: ' + @statusCode

    PRINT 'response body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    -- The successful JSON response looks like this:

    -- {
    --   "token_type": "Bearer",
    --   "expires_in": 3599,
    --   "ext_expires_in": 3599,
    --   "access_token": "eyJ0eX...ZKaeSVSg"
    -- }

    -- Save the JSON to a file for future requests.
    -- The Office365 access token acquired by client credentials is typically valid for ..
    IF @statusCode = 200
      BEGIN
        DECLARE @fac int
        -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

        DECLARE @success int
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'qa_data/tokens/office365.json', @sTmp0, 'utf-8', 0
      END

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @fac


END
GO