Hi all,
I've been working with Access for several decades now, and consider it an underrated product for many reasons.
One of my clients is running an Access interface, something I developed many years ago and have continued to develop. It has around 30 users and is used often by most. The backend (~30GB) was moved to SQL (on prem) around 10 years ago.
Recently, I undertook the move (after some testing!) to an Azure SQL instance. It has taken quite a bit of refining and work with views, passthroughs to pass parameters efficiently, and quite a bit of fudging to get working well with Entra/MFA, considering the client is still on a local domain. For this, I use a local account mapping table which matches the user's local domain Windows username with their Entra ID (email address) and requests interactive authentication on launch, reconnecting all tables and adjusting passthrough queries to connect with the user's ID.
All in all, it's a big success. However, some of the users (around 10) are being asked to reauthenticate (with MFA, which is a bit of an annoyance) roughly every 90 minutes. I did not experience this in development or testing, however I do have an authentication window pop up with roughly this frequency for a split second, and then disappear (as if to say 'yep, you're authenticated') and everything carries on.
All users have their 365 account added as a 'linked account' in Windows (and all running Win11 Pro).
I use a combination of ODBC (18) for linked tables, connection strings in passthroughs (which hold the e-mail address but not the password) and connection strings using ADO in VBA. I don't think it's the latter, as the auth popups can happen any time (e.g. when code isn't running).
What I'd really like to do is understand why some have this problem and others don't, and perhaps fix it rather than work around it.
Any pointers or advice would be hugely appreciated, I'm happy to elaborate on anything if needed.
I've been working with Access for several decades now, and consider it an underrated product for many reasons.
One of my clients is running an Access interface, something I developed many years ago and have continued to develop. It has around 30 users and is used often by most. The backend (~30GB) was moved to SQL (on prem) around 10 years ago.
Recently, I undertook the move (after some testing!) to an Azure SQL instance. It has taken quite a bit of refining and work with views, passthroughs to pass parameters efficiently, and quite a bit of fudging to get working well with Entra/MFA, considering the client is still on a local domain. For this, I use a local account mapping table which matches the user's local domain Windows username with their Entra ID (email address) and requests interactive authentication on launch, reconnecting all tables and adjusting passthrough queries to connect with the user's ID.
All in all, it's a big success. However, some of the users (around 10) are being asked to reauthenticate (with MFA, which is a bit of an annoyance) roughly every 90 minutes. I did not experience this in development or testing, however I do have an authentication window pop up with roughly this frequency for a split second, and then disappear (as if to say 'yep, you're authenticated') and everything carries on.
All users have their 365 account added as a 'linked account' in Windows (and all running Win11 Pro).
I use a combination of ODBC (18) for linked tables, connection strings in passthroughs (which hold the e-mail address but not the password) and connection strings using ADO in VBA. I don't think it's the latter, as the auth popups can happen any time (e.g. when code isn't running).
What I'd really like to do is understand why some have this problem and others don't, and perhaps fix it rather than work around it.
Any pointers or advice would be hugely appreciated, I'm happy to elaborate on anything if needed.
