« MBS FileMaker Plugin … | Home | Thanksgiving Sale »

SMTP with OAuth for Office 365 in FileMaker

For years we had the XOAuth2Bearer option in our CURL functions for our plugins. Any customer asking for how to use oAuth with Microsoft or Gmail got pointed to this property. Register an application with Microsoft or Google to get your client ID & secret. Then show a login screen in a browser for the user to login and grab the token. There are plenty of frameworks available and implementations in various programming languages. Once you got the access token, you can pass it to the CURL XOAuth2Bearer option. Since this is some work, we today like to show you sample scripts in FileMaker to do this:

 

App Setup

 

For the Office 365 access, please go the portal.azure.com website and login. Then go to Azure Active Directory (currently a blue pyramid icon). When you come there, you can copy your Tenant ID (an UUID) for later. 

 

Click on the left bar on the App Registrations section and then click there to add a new registration. Pick a name for your application and pick which account types you like to use. We picked the third one for multiple organizations and personal accounts. For the redirect URI, we pick web and then put in the "http://localhost:9999/". This is what we use with the WebHook functions later to catch the answer from the authentication. The port number can be chosen freely from 1025 to 65535 and 9999 is easy to remember for our example. 

 

After you created the application, please copy the application ID. That is the client ID (an UUID) in the scripts. For the secrets, pick second tab for client secrets and add a new client secret. Pick a nice name and a long expiration date, e.g. 24 months. Now copy the client secret, a string with random characters. 

 

Microsoft has an article to explain registration here. Since the exact steps may vary, please be prepared to look for the buttons on a new place, if you read this in a few months.


Start Script

 

Let's start to get an authorization. First we setup a WebHook to catch the response later. We listen on the port we provided above, in our example 9999. We then define the WebHookReceived script to be triggered. Please do not forget to check if fmplugin privilege for FileMaker 19.2 is granted if it exists. Next we define the response to send, which may show briefly in the web viewer later. 

 

If [ IsEmpty ( $$WebHooks ) ] 

# setup callback

Set Variable [ $$WebHooks ; Value: MBS("WebHook.Create") ] 

Set Variable [ $r ; Value: MBS("WebHook.Listen"; $$WebHooks; 9999) ] 

Set Variable [ $r ; Value: MBS("WebHook.SetScript"; $$WebHooks; Get(FileName); "WebHookReceived") ] 

Set Variable [ $text ; Value: "<html><p>Request arrived.</p></html>" ] 

Set Variable [ $text ; Value: "HTTP/1.1 200 OK¶Server: MyServer 1.0¶Connection: close¶Content-Type: text/html¶Content-Length: 36¶¶" & $text ] 

Set Variable [ $text ; Value: MBS( "Text.ReplaceNewline"; $Text; 3 ) ] 

Set Variable [ $r ; Value: MBS("WebHook.SetAutoAnswer"; $$Webhooks; $text; "UTF-8") ] 

End If

 

Load login page


Next we calculate the URL to load in the web viewer. This includes the scope and we can ask for a lot of things, but here we just ask for SMTP send permissions. Once we have the right URL, we load it into the browser to show the login screen. This seems to work better on macOS when we set the custom user agent to pose as Safari.

 

Set Variable [ $clientID ; Value: Trim(Office 365 oAuth SMTP::ClientID) ] 

Set Variable [ $TenantID ; Value: Trim(Office 365 oAuth SMTP::TenantID) ] 

Set Variable [ $redirectURI ; Value: "http://localhost:9999/" ] 

Set Variable [ $redirectURI ; Value: MBS("Text.EncodeURLComponent"; $redirectURI; "UTF-8") ] 

Set Variable [ $scope ; Value: "https://outlook.office365.com/SMTP.Send" // "openid profile offline_access https://outlook.office365.com/POP.AccessAsUser.All https://outlook.office365.com/IMAP.AccessAsUser.All" ] 

Set Variable [ $scope ; Value: MBS("Text.EncodeURLComponent"; $scope; "UTF-8") ] 

Set Variable [ $URL ; Value: "https://login.microsoftonline.com/" & $TenantID & "/oauth2/v2.0/authorize?response_type=code&scope=" & $scope & "&redirect_uri=" & $redirectURI & "&client_id=" & $clientID & "&state=test" ] 

# let web viewer be Safari

Set Variable [ $r ; Value: MBS("WebView.SetCustomUserAgent"; "web"; "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.1 Safari/605.1.15") ] 

# Load the URL

Set Variable [ $r ; Value: MBS( "WebView.LoadURL"; "web"; $URL) ] 

 

Catch response with WebHook


Our next script is the one triggered by the WebHook for the incoming request. There we pick the request and ask it for the URL components, where our plugin parses the URL into the parts. Once we got the URL parsed, we can free the request and the web hook itself. The web viewer can load the "about:blank" URL to discard the Microsoft login screen.

 

Set Variable [ $WebRequest ; Value: Get(ScriptParameter) ] 

# we got the answer

Set Variable [ $URLComponents ; Value: MBS( "WebRequest.URLComponents"; $WebRequest ) ] 

Set Field [ Office 365 oAuth SMTP::Answer ; $URLComponents ] 

# and show full request for debugging

Set Variable [ $Text ; Value: MBS( "Text.ReplaceNewline"; MBS("WebRequest.GetRawData"; $WebRequest; "UTF-8"); 1) ] 

Set Field [ Office 365 oAuth SMTP::Debug ; $Text ] 

# free the webhook and request

Set Variable [ $r ; Value: MBS("WebRequest.Release"; $WebRequest) ] 

Set Variable [ $r ; Value: MBS("WebHook.Release"; $$WebHooks) ] 

Set Variable [ $$WebHooks ; Value: "" ] 

# clear web viewer

Set Variable [ $r ; Value: MBS( "WebView.LoadURL"; "web"; "about:blank") ] 

 

Query access token


After we got the URL components and pick the parameters and inside it the code field. When we have a code field, we can build the URL with the tenant ID. We make a POST request and build the data to send. This includes the code we just got, the client ID and the client secret. So we pass the code, which is only valid for client ID to the server and proof with the secret, that we are the right one to pick the access token up.

 

Set Variable [ $URLComponents ; Value: Office 365 oAuth SMTP::Answer ] 

Set Variable [ $Parameters ; Value: JSONGetElement ( $URLComponents ; "Parameters" ) ] 

Set Variable [ $code ; Value: JSONGetElement ( $Parameters ; "code" ) ] 

Set Variable [ $state ; Value: JSONGetElement ( $Parameters ; "state" ) ] 

Set Variable [ $session_state ; Value: JSONGetElement ( $Parameters ; "session_state" ) ] 

# if we got a code, we continue to query the access code

If [ Length ( $code ) > 0 ] 

Set Variable [ $TenantID ; Value: Trim(Office 365 oAuth SMTP::TenantID) ] 

Set Variable [ $clientID ; Value: Trim ( Office 365 oAuth SMTP::ClientID ) ] 

Set Variable [ $clientSecret ; Value: Trim(Office 365 oAuth SMTP::ClientSecret) ] 

Set Variable [ $URL ; Value: "https://login.microsoftonline.com/" & $TenantID & "/oauth2/v2.0/token" ] 

Set Variable [ $redirectURI ; Value: MBS("Text.EncodeURLComponent"; "http://localhost:9999/"; "UTF-8") ] 

Set Variable [ $Data ; Value: "code=" & $code & "&client_id=" & $clientID & "&client_secret=" & $clientSecret & "&grant_type=authorization_code" & "&redirect_uri=" & $redirectURI ] 

Set Variable [ $curl ; Value: MBS("CURL.New") ] 

Set Variable [ $result ; Value: MBS("CURL.SetOptionURL"; $curl; $URL) ] 

Set Variable [ $result ; Value: MBS("CURL.SetOptionPostFields"; $curl; $Data; "UTF-8") ] 

Set Variable [ $result ; Value: MBS("CURL.SetOptionHTTPHeader"; $curl; "application/x-www-form-urlencoded") ] 

Set Variable [ $result ; Value: MBS("CURL.Perform"; $curl) ] 

# Pick Result

Set Variable [ $code ; Value: MBS( "CURL.GetResponseCode"; $curl ) ] 

If [ $result = "OK" and $code = 200 ] 

Set Variable [ $resultText ; Value: MBS("CURL.GetResultAsText"; $curl) ] 

Set Variable [ $debugText ; Value: MBS("CURL.GetDebugAsText"; $curl) ] 

Set Field [ Office 365 oAuth SMTP::CURL Debug ; $DebugText ] 

Set Field [ Office 365 oAuth SMTP::CURL Result ; $resultText ] 

Perform Script [ Specified: From list ; “Extract Access Token” ; Parameter:    ]

Else

Show Custom Dialog [ "SMTP" ; "Failed to query token." ] 

End If

Set Variable [ $result ; Value: MBS("CURL.Cleanup"; $curl) ] 

End If

 

Once we got the result text, we can extract the values from the JSON. The important thing here is the access token. You may also store the expiration time and calculate the end date to get a new token in-time.

 

Set Variable [ $Result ; Value: Office 365 oAuth SMTP::CURL Result ] 

# get access token

Set Variable [ $token_type ; Value: JSONGetElement ( $Result ; "token_type" ) ] 

Set Variable [ $scope ; Value: JSONGetElement ( $Result ; "scope" ) ] 

Set Variable [ $expires_in ; Value: JSONGetElement ( $Result ; "expires_in" ) ] 

Set Variable [ $ext_expires_in ; Value: JSONGetElement ( $Result ; "ext_expires_in" ) ] 

Set Variable [ $access_token ; Value: JSONGetElement ( $Result ; "access_token" ) ] 

If [ Length ( $access_token ) > 0 ] 

Set Field [ Office 365 oAuth SMTP::access_token ; $access_token ] 

Show Custom Dialog [ "Got token!" ] 

End If

 

Send an email


We got a token, so let's send an email. We use our SendMail functions to build an email. The SMTP server is smtp.office365.com with port 587 for Office 365. We need to enable TLSv1.2 and require encryption. On top we pass user name, but no password as we pass below the bearer token. If CURL.Perform returns OK, the email is sent. 

 

# create email

Set Variable [ $EmailID ; Value: MBS("SendMail.CreateEmail") ] 

Set Variable [ $r ; Value: MBS("SendMail.SetFrom"; $EmailID; Trim(Office 365 oAuth SMTP::From Email); Trim(Office 365 oAuth SMTP::From Name)) ] 

Set Variable [ $r ; Value: MBS("SendMail.SetPlainText"; $EmailID; Office 365 oAuth SMTP::Email Text) ] 

Set Variable [ $r ; Value: MBS("SendMail.SetSubject"; $EmailID; Office 365 oAuth SMTP::Subject) ] 

Set Variable [ $r ; Value: MBS("SendMail.SetSMTPServer"; $EmailID; "smtp.office365.com") ] 

Set Variable [ $r ; Value: MBS("SendMail.SetSMTPUsername"; $EmailID; Trim(Office 365 oAuth SMTP::From Email)) ] 

Set Variable [ $r ; Value: MBS("SendMail.AddTO"; $EmailID; Trim (Office 365 oAuth SMTP::To Email); Trim (Office 365 oAuth SMTP::To Name)) ] 

Set Variable [ $curl ; Value: MBS("CURL.New") ] 

Set Variable [ $r ; Value: MBS("SendMail.PrepareCURL"; $EmailID; $curl) ] 

# Maybe use alternative SMTP port?

Set Variable [ $r ; Value: MBS("CURL.SetOptionPort"; $curl; 587) ] 

# This turns TLS on and requires connection to be encrypted

Set Variable [ $r ; Value: MBS("CURL.SetOptionUseSSL"; $curl; 3) ] 

# force TLS v1.2

Set Variable [ $r ; Value: MBS("CURL.SetOptionSSLVersion"; $curl; 6) ] 

# put in token

Set Variable [ $r ; Value: MBS("CURL.SetOptionXOAuth2Bearer"; $curl; Office 365 oAuth SMTP::access_token) ] 

# Run the transfer

Set Variable [ $r ; Value: MBS("CURL.Perform"; $curl) ] 

# get debug messages

Set Field [ Office 365 oAuth SMTP::CURL Debug ; MBS("CURL.GetDebugAsText"; $curl; "UTF-8") ] 

Set Variable [ $r ; Value: MBS("CURL.Release"; $curl) ] 

#  Cleanup

Set Variable [ $r ; Value: MBS("SendMail.Release"; $EmailID) ] 

 

Please try it. We hope this works fine for you. Of course you can adapt the same code to work with other providers.

The example database will be included with future plugin downloads. Email us if you need a copy or have a question.

22 11 22 - 10:34