Connecting Azure SQL Tables zu MC Access using WAAD Authentification

Question

Hi,

we use a small Azure database which we use both in Excel and in an Access application.

We do not operate our own AD. There is only the O365 AD. I think it means WAAD.

In Excel, the database is integrated once via the Power Query Editor. The users have to log in once with their Office 365 access data the first time they use it. After that, the data is always available and one does not have to authenticate again.

That's great.

In Access, the required tables can apparently only be linked via ODBC. Various authentication methods are available here, of which only "With Azure Active Directory Interactive authentication using a login ID entered by the user." functions for us.

This is very annoying as the user has to type in both their username and password each time and also has to perform the MFA.

Is there a way to make accessing Azure SQL within Access as easy as it is in Excel?

Thank you for your help.

Best regards

Patrick

Answer 1

Use Azure Active Directory - Universal with MFA, this is an authentication method available in Azure SQL Database which supports both AAD authentication and MFA. If the ODBC driver you're using supports this, it could be a seamless experience for your users.

Or instead of having users manually sign in every time, you can use an application registration in Azure AD to authenticate to Azure SQL Database.

• Register an application in Azure AD, and grant it the required permissions to the database.
• Use a service or backend script to retrieve an access token for the application.
• Use this token to connect to the Azure SQL Database from Access. The token will have a limited lifetime, so you'll need to handle token refreshes in your application.

Answer 2

@Patrick2P Thank you for reaching out.

My understanding is that you are trying to access an Azure SQL Database from Microsoft Access without having to authenticate using both username, password and MFA.

The scenario you describe sounds like one shared here on the Access forum where the cx does not want to repeatedly enter or store the creds but instead have interactive AAD auth via MFA. If this is ask, it is not possible to automate AAD MFA currently. 

Regards,

Oury