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:
- by handing it off to the email client on the workstation or
- 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.
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:
In practical terms, when we need access to the API, this flow will go like this:
- 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’)
- The user authenticates thereby either providing a username/password or any other form of authentication (a YubiKey, smart card, text message, phone app, etc.)
- Assuming that the authentication works, the API responds to a pre-set URL (the ‘redirect URL’) with a code
- 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)
- The API responds with JSON that contains a token (more typically a set of tokens but more about that later)
- 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.
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.
You can follow along in our demo file.
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 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.
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.”
In the ‘App registrations’ panel select ‘New registration.’
There are only two settings required: a name and a redirect URL.
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.
The next step is to add permissions to the app. In the middle pane, select ‘API permissions.’
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.
Select ‘Microsoft Graph,’ and the next choice will be between ‘delegated permissions’ and ‘application permissions.’
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:
The demo file requires the following permissions to cover both ‘delegated’ and ‘application’ scenarios.
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.’
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.
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.
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.
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:
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.
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.
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:
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.
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:
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:
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:
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.
Unlike the client-interactive flow, we only get an access token; we do not get a refresh token:
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:
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.