Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.0.0+

Microsoft 365: Obtain Mail OAuth2 Tokens with Desktop PKCE

See more Office365 Examples

Obtain delegated SMTP, IMAP, and POP access with authorization code flow and S256 PKCE. Register a public desktop application in Microsoft Entra with a Mobile and desktop applications loopback redirect URI of http://localhost:3017/. Do not register this callback as a Web or SPA application. No client secret belongs in a distributed desktop application. Run the sample where the user can launch a browser and return to the local listener; it is not a web-server callback example.

The common authority supports work/school and personal accounts only if the app registration supports those account types. For a single-tenant work/school app, replace common in both endpoints with the mailbox tenant ID. The account needs an Exchange Online mailbox; administrator consent or Conditional Access can restrict authorization.

The example requests all three delegated protocol permissions for demonstration. Remove unused scopes in your application. Microsoft Graph scopes and ID tokens are not substitutes for mail-protocol access tokens. The user may need consent again after revocation or policy changes; refresh is not guaranteed indefinitely.

Create qa_data/tokens in a private, access-controlled working directory before running. The saved JSON contains credentials, not just settings. Use separate caches per account, app, and authority; prevent concurrent writers, use an OS credential store in production, and never publish token responses. The sample validates the response before writing, but a production cache also needs atomic replacement and recovery from write failures.

Microsoft reference documentation

Chilkat SQL Server Downloads

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
    DECLARE @iTmp0 int
    DECLARE @iTmp1 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Assumes Chilkat is unlocked. See Global Unlock Sample.

    DECLARE @oauth int
    EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @oauth, 'TokenEndpoint', 'https://login.microsoftonline.com/common/oauth2/v2.0/token'
    EXEC sp_OASetProperty @oauth, 'ClientId', 'CLIENT_ID'
    --  A public desktop client does not have a client secret.

    EXEC sp_OASetProperty @oauth, 'AuthorizationEndpoint', 'https://login.microsoftonline.com/common/oauth2/v2.0/authorize'
    EXEC sp_OASetProperty @oauth, 'LocalHost', 'localhost'
    EXEC sp_OASetProperty @oauth, 'ListenPort', 3017
    EXEC sp_OASetProperty @oauth, 'CodeChallenge', 1
    EXEC sp_OASetProperty @oauth, 'CodeChallengeMethod', 'S256'
    --  Request only the protocols your application needs; this sample covers all three.
    EXEC sp_OASetProperty @oauth, 'Scope', 'offline_access https://outlook.office.com/SMTP.Send https://outlook.office.com/IMAP.AccessAsUser.All https://outlook.office.com/POP.AccessAsUser.All'
    EXEC sp_OAMethod @oauth, 'AddAuthQueryParam', @success OUT, 'prompt', 'select_account'
    IF @success <> 1
      BEGIN

        PRINT 'Could not configure account selection.'
        EXEC @hr = sp_OADestroy @oauth
        RETURN
      END
    DECLARE @authUrl nvarchar(4000)
    EXEC sp_OAMethod @oauth, 'StartAuth', @authUrl OUT
    EXEC sp_OAGetProperty @oauth, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Could not start the loopback listener.'
        EXEC @hr = sp_OADestroy @oauth
        RETURN
      END
    EXEC sp_OAMethod @oauth, 'LaunchBrowser', @success OUT, @authUrl
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @oauth, 'Cancel', @success OUT

        PRINT 'Could not open the system browser.'
        EXEC @hr = sp_OADestroy @oauth
        RETURN
      END
    DECLARE @waited int
    SELECT @waited = 0
    EXEC sp_OAGetProperty @oauth, 'AuthFlowState', @iTmp0 OUT
    WHILE ((@iTmp0 < 3) and (@waited < 180000))
      BEGIN
        EXEC sp_OAMethod @oauth, 'SleepMs', NULL, 200
        SELECT @waited = @waited + 200
      END
    EXEC sp_OAGetProperty @oauth, 'AuthFlowState', @iTmp0 OUT
    IF @iTmp0 < 3
      BEGIN
        EXEC sp_OAMethod @oauth, 'Cancel', @success OUT

        PRINT 'Authorization timed out.'
        EXEC @hr = sp_OADestroy @oauth
        RETURN
      END
    EXEC sp_OAGetProperty @oauth, 'AuthFlowState', @iTmp0 OUT
    IF @iTmp0 <> 3
      BEGIN

        EXEC sp_OAGetProperty @oauth, 'AuthFlowState', @iTmp0 OUT
        PRINT 'Authorization did not complete. Flow state: ' + @iTmp0
        EXEC @hr = sp_OADestroy @oauth
        RETURN
      END
    DECLARE @token int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @token OUT

    EXEC sp_OAGetProperty @oauth, 'AccessTokenResponse', @sTmp0 OUT
    EXEC sp_OAMethod @token, 'Load', @success OUT, @sTmp0
    IF @success <> 1
      BEGIN

        PRINT 'Could not parse the token response.'
        EXEC @hr = sp_OADestroy @oauth
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'refresh_token'
    EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'refresh_token', '', 1
    IF (@iTmp0 <> 1) or (@iTmp1 = 1)
      BEGIN

        PRINT 'No offline refresh token was issued. The existing cache was preserved.'
        EXEC @hr = sp_OADestroy @oauth
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'access_token', '', 1
    IF (@iTmp0 <> 1) or (@iTmp1 = 1)
      BEGIN

        PRINT 'The response did not contain an access token. The existing cache was preserved.'
        EXEC @hr = sp_OADestroy @oauth
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    DECLARE @obtained int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @obtained OUT

    EXEC sp_OAMethod @obtained, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @obtained, 'GetAsTimestamp', @sTmp0 OUT, 0
    EXEC sp_OAMethod @token, 'UpdateString', @success OUT, 'obtained_at', @sTmp0
    --  The token response includes expires_in (seconds). Refresh before the access token expires.
    --  The file contains credentials: use a private, access-controlled directory.
    EXEC sp_OAMethod @token, 'WriteFile', @success OUT, 'qa_data/tokens/office365.json'
    IF @success <> 1
      BEGIN

        PRINT 'Could not save the token file.'
        EXEC @hr = sp_OADestroy @oauth
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @obtained
        RETURN
      END

    PRINT 'Credentials saved in the private working directory.'

    EXEC @hr = sp_OADestroy @oauth
    EXEC @hr = sp_OADestroy @token
    EXEC @hr = sp_OADestroy @obtained


END
GO