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

See more Office365 Examples

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.

Note: This example requires Chilkat v11.0.0 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)
    DECLARE @success int
    SELECT @success = 0

    -- 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
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
    EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'

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

    -- Replace "{tenant}" with your tenant ID, such as "112d7ed6-71bf-4eba-a866-738364321bfc".req.HttpVerb = "POST";

    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token', @req, @resp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @resp
        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
        EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

        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 @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @fac


END
GO