7 Step Guide to Using Access with Azure Multi-Factor Authentication

One of our clients wanted to use Azure multi-factor authentication with Microsoft Access and Azure SQL, requiring users to authenticate when they launch Access through a second validation at least once every 60 days.

Step 1: Extend your domain to Azure AD

Before you can enable MFA in Azure you need to extend the customer’s domain to Azure using Azure Active directory, which allows employees to authenticate anywhere in the world. Having the ability to just add or deactivate employees on your local domain and automatically have extend to your Azure resources is priceless. Here’s a link to get you started: https://docs.microsoft.com/en-us/azure/architecture/reference-architectures/identity/adds-extend-domain

Step 2: (Optional) Get the Microsoft Authenticator App

If you don’t use the app you will most likely get a text message on your cellphone with a one time code, which you must enter into the browser or application for validation. Instead, download the Authenticator App for you phone’s OS and simply Approve or Reject the login request. Bonus: You can approve the request on your smart watch too!

Bonus 2: You can use the authenticator with all of your Microsoft accounts, corporate and personal. I use it on my Outlook.com accounts as well as on IT Impact and client accounts.

Step 3: Enable Azure Multi-Factor Authentication

As consultants we usually get granted admin privileges on the client’s Azure account using our IT Impact email, (more on why this will not work with MFA in Access in Step 4 below), so we are used to using MFA with Azure: https://docs.microsoft.com/en-us/azure/active-directory/authentication/howto-mfa-mfasettings

Step 4: (Optional) Obtain account on client’s domain

Given the lack of support in OLEDB drivers for MFA, we first try and use our IT Impact email to login into the Access database, but there was one case when we needed an account on the customer’s domain in order to get the system working on our lab PC. If you’re consulting try to authenticate with your own email first, and if that fails you may need a client account.

Step 5: Enable MFA on your account

Here’s how: https://aka.ms/MFASetup Note that it may not default to using the Microsoft authenticator app so you may need to go back into the page after it’s setup to use it.

Note: If you are still using applications that don’t support MFA, (such as Skype for Business), you will need an app password. You could use one for all non-MFA apps but I recommend having one for each app for added security.

Step 6: Use the latest ODBC and OLEDB drivers

Currently only ODBC 17 and above support MFA with Azure SQL, which you will need to use when linking your tables to your SQL database. OLEDB 18 is the latest version and does not support MFA yet. For more on these drivers please see this blog post.

Step 7: Login to Access using your MFA client account

If you’re logged into Access with an account that is not your client’s, log off, start Access without the application loading, (as if you were creating a new database), and login with the account created. Once logged in you can load the app and it should not prompt you for MFA for at least another 30 days.

Join me for SQL Server’s Path Toward an Intelligent Database at my next Access with SQL Server user group meeting! Pedro Lopez from Microsoft will present on SQL Server 2019’s introduction of ground-breaking query performance enhancements under the Intelligent Query Processing family. Click here for details on how to join us on Tuesday October 8th.