BlogFileMaker

Microsoft Graph API for Office 365 Integrations in FileMaker: Part One

By March 5, 2020 March 25th, 2020 7 Comments

Welcome to part one of a blog series on Microsoft’s Graph API for Office 365 in FileMaker.

In part one, I share the demise of basic authentication and why relying on strictly passwords is no longer a viable or safe option. In part two, I provide insights on using the Microsoft Graph API functionality in your FileMaker solution.

Your Username and Password Are No Good Anymore: the Demise of Basic Authentication

Traditionally when you needed to log into something, you just typed in your username and password, and that was it. That’s how we have always opened a FileMaker file, for instance.

Basic Authentication is, well…basic: you provide a username and a password, and those get validated. If they pass, you are granted access to whatever it was you were trying to authenticate against.

It is a simple one-step mechanism to prove who you are. It relies entirely on identifying you only by your account name and that password. The account name more often than is not very secret, so that leaves just the password as the one thing that both identifies you and protects that identity.

This is an intriguing read on why relying strictly on passwords and trying to enforce complexity rules for passwords is not a good approach.

This primitive form of authentication is not considered good enough anymore, and basic authentication is going away. Gradually, but we will see less and less of it in the future. “Modern Auth” is taking its place, and that typically means OAuth 2.0 supplemented with multiple ‘factors’ to prove that you are who you say you are. We see more interest in using OAuth to log into FileMaker solutions, but the demise of basic authentication extends into other functionality as well.

Back in September 2019, Microsoft announced that as of later this year (2020), Basic Authentication will no longer be supported for a good number of their services, and that includes POP and IMAP to collect emails or for integrations with their Exchange Online and Exchange Web Services. Google announced very much the same thing in December 2019 for what they refer to as “Less Secure Applications,” i.e., those using Basic Authentication. Google takes it a step further than Microsoft by also disallowing basic authentication for sending email through SMTP.

The alternative for both of these providers is to use OAuth 2.0 as the authentication mechanism (we’ll call it OAuth from here on).

This blog post series is inspired by working on a client’s system where their workflows require keeping track of incoming emails and replying to them. Email service is provided by Microsoft Office 365. These blog posts will document the process of discovery on how to do deep integration with the Office 365 APIs (weirdly named “Graph API”) and the challenges that you are likely going to encounter with similar APIs.

For the longest time we have been able to send email from FileMaker in one of two ways:

  1. by handing it off to the email client on the workstation or
  2. through an SMTP connection to the mail server.

FileMaker never had native capabilities to read email. If you wanted to retrieve emails, you had to resort to a plugin that supported POP or IMAP.

In all of the above cases (SMTP natively, POP/IMAP through a plugin), the authentication is simple basic authentication.

Authenticating for SMTP access

Figure 1: Authenticating for SMTP access

OAuth is not simple. It involves multiple steps, which is one of the reasons why it is inherently more secure than the simple one-step Basic Authentication; there are more checks and balances in the process.

Before we get into the details on how to make this authentication mechanism work from inside FileMaker, let’s take a glance at how OAuth is designed to work so that the challenge becomes clear.

First off, purists will point out that OAuth is not an authentication mechanism; it’s an authorization mechanism. With ‘authentication’ meaning strictly: who are you. And ‘authorization’ meaning: what are you allowed to do. For our purpose, consider it to be both: in order to know what you are allowed to do, you first need to establish that you are who you claim to be.

The “Resource Owner” is typically you (or your user). You own the data to which you want to access. “The Authorization Server” is where the user’s identity is kept. That could be the API provider itself, or it could be an Identity Provider (IdP) to which they delegate the authentication. For Office365, for instance, that’s Azure Active Directory. Some APIs let you log in with your Google account, or your LinkedIn account or even your Facebook account. In that case, those providers are the “Authorization Server.” The “Resource Server” is the one that provides the API functionality: in our case, Office 365 with its mail, files, calendaring, and so on.

While the distinctions between those roles on the right-hand side are important, we can simplify them for our purpose by just calling them “the API.”

The complete flow or ‘dance’ is described in the OAuth 2.0 RFC and looks like this:

Abstract protocol flow

Figure 2: Abstract protocol flow

In practical terms, when we need access to the API, this flow will go like this:

  1. We take the user to a login screen that belongs to the API (or whomever they delegated the Identity Management to) and we are also providing info as to what exactly we are requesting access to (typically in the form of a ‘Client ID’)
  2. The user authenticates thereby either providing a username/password or any other form of authentication (a YubiKey, smart card, text message, phone app, etc.)
  3. Assuming that the authentication works, the API responds to a pre-set URL (the ‘redirect URL’) with a code
  4. We take that code and make another call to the API giving it back the code plus a secret that belongs to the resource we want to work with (typically an ‘app’ we set up on the API provider)
  5. The API responds with JSON that contains a token (more typically a set of tokens but more about that later)
  6. We use that token for all subsequent calls to the API’s functionality since it is our ‘passport’ that shows that we are authorized for the data and functionality we are asking it to perform. The token has a limited lifespan, typically one hour.

What we’ve described above is the “Authorization Code” flow which involves actual user input, the user has to physically do the authentication on step 2 as part of this process.

This will not work, of course, if we want to do server-to-server processes (such as collecting email as part of a FileMaker Server-side schedule). For that, there is a variant called the “Client Credential” flow. We’ll discuss both and show how to implement them in your FileMaker solution.

We know that FileMaker’s “Send Mail” script doesn’t support this kind of authentication since there is only room for a username and password (see Figure 1: authenticating for SMTP access). Perhaps we can expect an update to this FileMaker feature to support modern authentication. However, in the meantime, we can explore the alternatives that Microsoft, Google, and many others provide to deliver services through APIs. This series of blog posts will document our discovery of the Microsoft Office 365 APIs. We conclude they are easy to use and immensely powerful. For instance: those APIs are by far the easiest way to send out HTML emails. Multiple attachments? No problem.

Our experience is that these APIs provide much more powerful features more easily than trying to work through the SMTP and POP/IMAP protocols.

There are other resources in our community available too that provide their own spin and are good for further exploration: from ClickWorks, SeedCode, and dbservices.

Here is our first challenge: how do we authenticate? Step 3 in the flow we described above is usually the trickiest because that is where the API has to respond to us, and we need to be able to capture that response.

Demo File

You can follow along in our demo file.

Menu screen from the FileMaker demo file

Figure 3: FileMaker demo file

Authentication

As mentioned earlier, we have two different authentication flows that we need to build:

  • One where the user is involved and has to provide their identity proof
  • One where the user is not involved for use in server-to-server automated flows

In Microsoft Office 365 terms, those are described as “on behalf of the user” and “service access (without a user).” In OAuth terms, they are named “Authorization Code flow” and “Client Credentials flow.”

We will show how to construct both of these in FileMaker. But it all starts with an app in Azure AD. This is common to many APIs where the user’s rights are set in an application that you register with the API. You grant that application certain permissions, as well as access to certain data and functional areas.

The app

The app that you register on the Microsoft side will give you the ‘Client ID’ that we mention in step 1 of the ‘Authorization Code’ flow we outlined above. In addition, the app gives you a place where you can specify what permissions any API interaction through the app should have.

If you have used OAuth authentication for your FileMaker solutions, then this part of the process will be familiar since that too requires setting up an app in Azure AD. (See my 2017 DevCon presentation.)

Start by logging into admin.microsoft.com and expand the left navigation panel until you see the Azure Active Directory.

Azure AD in the Microsoft Admin Section

Figure 4: Azure AD in the Microsoft Admin Section

For the Office 365 (Graph) API, it is Azure AD that holds the user’s identities, and that is responsible for providing the authentication for the API.

Click on ‘Azure Active Directory,’ and in the new Azure portal browser tab that opens up, select ‘Azure Active Directory’ again and click on “App registrations.”

App registrations in Azure AD

Figure 5: App registrations in Azure AD

In the ‘App registrations’ panel select ‘New registration.’

New registration

Figure 6: New registration

There are only two settings required: a name and a redirect URL.

Registering a new app

Figure 7: Registering a new app

The redirect URL is used in step 3 of the OAuth flow and has to be a valid URL. We’ll use our Soliant home page. When you finish by clicking the ‘Register’ button, you will see some information about your new app. Make a note of the ‘Client ID’ and the ‘Tenant ID,’ you will need those later.

Client ID and Tenant ID of the new app

Figure 8: Client ID and Tenant ID of the new app

The next step is to add permissions to the app. In the middle pane, select ‘API permissions.’

Assign permissions to the app

Figure 9: Assign permissions to the app

The API that we are after is the Graph API, which is the unified API that spans the whole of Office 365: users, email, calendar, OneDrive, and more.

Graph API

Figure 10. Graph API

Select ‘Microsoft Graph,’ and the next choice will be between ‘delegated permissions’ and ‘application permissions.’

Choosing between delegated and application permissions

Figure 11: Choosing between delegated and application applications

Choose ‘delegated’ if you are planning on making the users authenticate manually and choose ‘application’ if you plan on using the API in the background without the user’s logging in. If you want both, then you will have to set up the required permissions twice.

The Graph API documentation lists what permissions you need to perform each API function, for instance, to get a list of emails in a user’s mail folder you would need the permissions below:

Required permission for an API call

Figure 12: Required permission for an API call

The demo file requires the following permissions to cover both ‘delegated’ and ‘application’ scenarios.

Permission required for the demo file

Figure 13: Permissions required for the demo file

There is one more app configuration that we need to make: we need to generate a ‘Client Secret’ that we use in step 4 of the flow to exchange the code for a token. In the menu options for your app, choose ‘Certificates & secrets’ and click on ‘New client secret.’

New client secret

Figure 14. New client secret

Give the secret a name and choose how long it should be valid for. It is not recommended to choose a secret that does not expire.

Name the secret

Figure 15: Name the secret

Make sure to copy the secret and store it somewhere safe for now. When you navigate away from this section, you will no longer be able to see the secret.

Save the generated secret

Figure 16: Save the generated secret

Now that we have the app set up, we can store its settings in our demo app. From the menu, click on ‘Subscriptions,’ create a new record, and populate the fields. Note that we also keep track of the expiry date of that secret so that we can prompt the admins ahead of that date.

Storing the app information in FileMaker

Figure 17: Storing the app information in FileMaker

At this point, we are done with the configuration on the Office 365 side. From here on, we will be in FileMaker, making the API calls work.

Earlier, we noted that there are two different types of authentication flows we can take:

  • One where the user physically needs to be involved (‘on behalf of the user,’ ‘delegated’)
  • And one where the integration can run as service without the user’s involvement (‘service,’ ‘application’)

The next two sections will describe how to make both of those flows work in FileMaker.

On Behalf of the User

When we want the user to authenticate themselves to get access to their email, calendar,  and files, then this is the route to take.

In the demo file, toggle on “interactive login” and then click on the “log in” button:

Screenshot showing enablig user authentication on the main menu

Figure 18: Enabling user authentication

The FileMaker Script will take the user to a layout with a web viewer on it, and it will construct the proper URL to go to the Microsoft login page.

The URL and its parts are kept in the preferences section of the demo app, and you can adjust them there in case Microsoft changes their API.

Setting for constructing the login URL

Figure 19: Settings for constructing the login URL

For my Office 365 account, I have password-less authentication set up through the Microsoft Authenticator app on my phone, and the web viewer will display this login page.

User authentication

Figure 20: User authentication

That is step 1 and step 2 of our flow. Since we have to wait for the result of the authentication, there is an OnTimer trigger that starts as soon as we select what Office 365 subscription we are logging into (the demo file is set up to handle multiple subscriptions). The timer will check every second for a minute, and if we did not receive the expected response, it will stop and show an error to the user.

As soon as I approve the login request on my phone, the Microsoft login page will redirect the web viewer to the URL we have provided in the Azure AD app (www.soliantconsulting.com), and it will append query parameters to the URL. One of those query parameters will be the code that we require for step 4.

The OnTimer checks the ‘source’ of the web viewer (which is the URL – line 27 in the screenshot) to see if it contains the string “code=” which would indicate that the web viewer was indeed properly redirected and that the authentication worked:

Looking for code in the redirect URL

Figure 21: Looking for code in the redirect URL

This is step 3, and the key here is that the web viewer is redirected. That means that for all intents and purposes, the redirect URL that we configure in the Azure AD app is meaningless; there does not need to be anything there except a working URL. We could have used www.microsoft.com, for instance. Only the query parameters that Microsoft appends to that URL when it instructs the browser (the web viewer in our case) to redirect matters.

As soon as the OnTimer script detects that there is a code in the URL that the web viewer displays, it parses out that code and makes a call to the login URL with the code, the client id, and the client secret. This call is an HTTP POST, so we use the “insert from URL” script step; we no longer need the web viewer.

The result of that call is a JSON response from Microsoft that contains the access token that we will need for all subsequent calls to the API. The JSON also includes information about when the token expires (in 3599 seconds, one hour), and we also get a refresh token.

JSON response after exchanging the code for a token

Figure 22: JSON response after exchanging the code for a token

We can use the refresh token to obtain a new access token when it expires, without having to make the user re-authenticate completely from step 1. The refresh token is valid for 90 days by default. We will cover that later.

In our FileMaker file the user simply sees the confirmation that they are logged in:

Menu screen showing the user is logged in

Figure 23: The user is logged in

Since we will use the token often and we do have to keep track of whether it is still valid we store that info in a set of global variables:

Global variables to keep track of the tokens and their expiry

Figure 24: Global variables to keep track of the tokens and their expiry

We store it as JSON because the demo file can be used with multiple Office 365 subscriptions, so we store the id of the FileMaker record for the subscription and the token that goes with that. In the expiry variable, we store the numerical representation of the timestamp of when the token expires.

The scripts involved in this flow are these:

Authorization code flow scripts

Figure 25: Authorization code flow scripts

Server-to-server authentication

For the scenarios where we do not want or cannot have the user do the authentication, we have to use a different flow called the “Client Credentials” flow. The main difference is that instead of asking the user to authenticate, we give the Identity Provider the app’s client id and client secret to act like the app’s username and password. The authorization server responds with the access token directly without going through the steps to first get a code and then exchange that code for a token.

Official Client Credentials flow chart

Figure 26: Official Client Credentials flow chart

Unlike the client-interactive flow, we only get an access token; we do not get a refresh token:

JSON response from a Client Credentials flow

Figure 27: JSON response from a Client Credentials flow

This is for security reasons: when the token expires, you have to re-authenticate and prove that you still have the client id and client secret. If we think the client secret is compromised, we can revoke it in Azure AD, and the exposure would be minimized. There is no long-lived refresh token that can be used to get a new access token.

Since there is no UI in this process, there is just one script that does the required HTTPS POST call and parses the returned JSON:

Client Credentials flow script

Figure 28: Client Credentials flow script

Stay tune for part two, in which I’ll share how to use the API functionality to which we have given our Azure AD app permissions. If you have any questions about the content above or would like to discuss enhancing authentication for your FileMaker solution, contact our team.

Wim Decorte

Wim Decorte

Wim is a Senior Technical Solution Architect at Soliant. He is a FileMaker 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 and 18 Certified FileMaker Developer and the author of numerous Tech Briefs and articles on FileMaker Server. Wim is one of the very few multiple FileMaker Excellence Award winners and was most recently awarded the FileMaker Community Leader of the Year award at the 2015 FileMaker Developer Conference. He is also a frequent speaker at the FileMaker Developer Conference and at FileMaker Developer groups throughout the world. In addition to being a renowned expert on FileMaker Server, Wim also specializes in integrating FileMaker with other applications and systems. His pet project is the open source fmDotNet connector class that he created.

7 Comments

  • Avatar Jason Wood says:

    Really great introduction, Wim. Thank you!

    Is there a recommended permission configuration for using an app-specific email account (like myapp@mycompany.com)?

    If you use Delegated Permissions, a person will have to manually input the credentials every 90 days for it to continue to work. And if you use Application Permissions, you have access to all accounts, which your administrator may not like. Seems like my requirement falls in a gap that doesn’t fit well with either…

    • Wim Decorte Wim Decorte says:

      Thanks, Jason.
      Two options:
      – perhaps there is something that can be done on the Azure AD side so that the app’s permissions only extend to an ‘organizational unit’ equivalent. I haven’t explored that but it may be worth a conversation with the Azure AD administrators.
      – or: carefully manage the refresh token, if you use it to get a new access token you also get a new refresh token. It’s only if you don’t use the refresh token for > 90 days (or whatever expiry you configure it for) that the user will need to authenticate manually again. Obviously the storage of and access to that refresh token needs to be very secure.

      • Avatar Jason Wood says:

        Ah – got it. I didn’t realize the refresh token could be replaced without starting over.

        I’m working in the sample file now and I’m having trouble finding where the USER record is supposed to get created. I would have expected it would happen in or after “parse_me”. I authenticated successfully and I see in APICALLLOG that my user profile was pulled from the /me/ endpoint, but no user record is created so any subsequent attempts to get folders or mail are failing… I tried manually creating the USER record manually from the data returned from /me/ but then I get ErrorAccessDenied when I try to get the folders, so I must be doing something wrong when creating the USER record.

        • Wim Decorte Wim Decorte says:

          On the Users list there is a button at the bottom that will grab the available users from the subscription. If you are using the interactive login it should bring back only your user details.

  • Avatar Mario De Witte says:

    Very nice explanation on how to interact with O365!
    I was wondering if it is possible to also get profile information from the user? Such as picture, email, telephone, …the usual stuff I guess, but also things like licensing info, group membership, …

  • Thanks Wim, much appreciated and know it took a bit of work to put this together. This certainly seems to be the future of access to many APIs.

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant