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