Access and Azure SQL - Interactive Entra Authentication

[bossjohnc]

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.

 

[bossjohnc]

I thought I'd update this with my progress so far. This is slightly removed from mainstream Access issues, but should help anyone in the same situation moving backend data to an Azure SQL instance either voluntarily or by force!

In my situation, the local domain is not directly, nor hybrid joined to Azure/Entra. This means that as well as Azure not knowing who the user is and me needing to fudge things so that account lookups are done within the DB and a local table, there is a hard expiry for logins at 60 minutes via ODBC.

Now, in some situations a 'silent re-authentication' is allowed to happen where users have elected to remain signed in to 365. In cases where they have not, credentials are requested every 60 mins (or sometimes more if no resources are requested). Silent re-auth may fail for several other reasons if the user has elected to remain signed in.

The correct way to fix this, is to Hybrid Join the local domain to 365 using Microsoft AD Sync tools. This is not hard, but does require some consideration. If this is done, operation should be seamless. If I can't get all users working, this is probably what will happen.

 

[RonPaii]

The correct way to fix this, is to Hybrid Join the local domain to 365 using Microsoft AD Sync tools. This is not hard, but does require some consideration. If this is done, operation should be seamless. If I can't get all users working, this is probably what will happen.

We started with a Hybrid Join when we moved to 365. It did make it easer to onboard everyone but it did present some issues. The biggest one was security. Office 365 accounts are very easy to hack, even more so with multifactor authentication. If a user gives up there password, at minimum it can be used to send emails. Authenticators are can be hacked by clicking on a link allowing the hacker to hijack the authentication token. We removed the join to protect the onsite AD from this hacking.

 

[bossjohnc]

We started with a Hybrid Join when we moved to 365. It did make it easer to onboard everyone but it did present some issues. The biggest one was security. Office 365 accounts are very easy to hack, even more so with multifactor authentication. If a user gives up there password, at minimum it can be used to send emails. Authenticators are can be hacked by clicking on a link allowing the hacker to hijack the authentication token. We removed the join to protect the onsite AD from this hacking.

Thanks for this, it's useful and interesting information. So, when you removed the hybrid join were you faced with some users having 60 min re-authentication for ODBC? How did you settle that?

 

[RonPaii]

Thanks for this, it's useful and interesting information. So, when you removed the hybrid join were you faced with some users having 60 min re-authentication for ODBC? How did you settle that?

I'm not using Azure so my users would see ODBC connection issues from Access FE to a Azure BE. That being said, if only some users have the re-authentication problem, did those uses check the stay longed in box? Also are the computers domain joined?

To improve 365 security I used conditional access in Entra admin center with locations and allowed counties. Microsoft did add a feature that will disconnect a user if the request start jumping around to distant locations.

 

[bossjohnc]

I'm not using Azure so my users would see ODBC connection issues from Access FE to a Azure BE. That being said, if only some users have the re-authentication problem, did those uses check the stay longed in box? Also are the computers domain joined?

To improve 365 security I used conditional access in Entra admin center with locations and allowed counties. Microsoft did add a feature that will disconnect a user if the request start jumping around to distant locations.

Some of the users are having difficulty navigating to a situation where they have the 'Stay signed in?' box. It doesn't appear for ODBC authentication unfortunately. There are also various (some complex) reasons why tokens might not match and users get prompted for re-auth regardless of choosing this option. I'm working on this though, it would be the best solution.

Implementing CA would also be a way to deal with this as we could elongate the expiry time in addition to locking things down geographically. Unfortunately I'm not in direct control of this, so it would need some conversation. I think Conditional Access may be the eventual solution.

A good link here (I don't seem to be able to post links at the moment, so copy/paste URL if you're reading this and need help!):

learn.microsoft.com/en-us/entra/identity/conditional-access/howto-conditional-access-session-lifetime

 

[RonPaii]

Some of the users are having difficulty navigating to a situation where they have the 'Stay signed in?' box. It doesn't appear for ODBC authentication unfortunately. There are also various (some complex) reasons why tokens might not match and users get prompted for re-auth regardless of choosing this option. I'm working on this though, it would be the best solution.

Implementing CA would also be a way to deal with this as we could elongate the expiry time in addition to locking things down geographically. Unfortunately I'm not in direct control of this, so it would need some conversation. I think Conditional Access may be the eventual solution.

A good link here (I don't seem to be able to post links at the moment, so copy/paste URL if you're reading this and need help!):

learn.microsoft.com/en-us/entra/identity/conditional-access/howto-conditional-access-session-lifetime

A few other links

Deployment considerations for Microsoft Entra multifactor authentication - Microsoft Entra ID

Learn about deployment considerations and strategy for successful implementation of Microsoft Entra multifactor authentication

go.microsoft.com

Microsoft Entra Conditional Access: Zero Trust Policy Engine - Microsoft Entra ID

Explore Microsoft Entra Conditional Access, the Zero Trust policy engine that integrates signals to secure access to resources.

learn.microsoft.com

Enable Microsoft Entra multifactor authentication - Microsoft Entra ID

In this tutorial, you learn how to enable Microsoft Entra multifactor authentication for a group of users and test the secondary factor prompt during a sign-in event.

learn.microsoft.com

 

[dalski]

It has taken quite a bit of refining and work with views, passthroughs to pass parameters efficiently,

Do you have any advice to share? I'm considering Azure but I am terrified.

Office 365 accounts are very easy to hack, even more so with multifactor authentication. If a user gives up there password, at minimum it can be used to send emails.

This is concerning, is a Windows user account authentication safer?

 

[bossjohnc]

Do you have any advice to share? I'm considering Azure but I am terrified.



This is concerning, is a Windows user account authentication safer?

I totally understand the way you feel! I think my main piece of advice would be to make sure as much processing as possible is done server side before presenting the data client side. Linked tables are ok for relatively small data sets and presenting all information, but as soon as you start to add filters or refine client side things get messy very quickly. There are a few different ways to deal with this (such as local tables, or snapshot recordsets), but I went for either Views, where your criteria are fixed, or passthroughs where you need to send parameters. You can rewrite the passthrough using query defs to add your parameters, and they will run server side.

Also, if like me you don't have any form of join to 365, Windows is not going to know who the user is, so some form of local lookup table for users / UPN (email addresses) would be a good idea. Also on launch you should either reconnect linked tables or make sure they are linked, and also rewrite the connection string for each passthrough query (using query defs) to insert the UPN for the current user. Keeping the UPN as a global variable for each session is a good idea, as you'll probably need to refer to it fairly regularly.

I'll post the code I used for changing passthrough connection strings on startup below:

Function ReconnectPassthrough(strUPN As String, Optional strConnString As String) As Boolean
Dim strSQL As String: strSQL = "SELECT Name AS ObjTyp FROM MSysObjects WHERE [Name] NOT Like '~*'   AND  MSysObjects.Type = 5"
Dim rsQ As DAO.Recordset
Dim qdf As DAO.QueryDef

Set rsQ = CurrentDb.OpenRecordset(strSQL)
rsQ.MoveFirst
Do While rsQ.EOF = False
    If CurrentDb.QueryDefs(rsQ(0)).Type = 112 Then
        Set qdf = CurrentDb.QueryDefs(rsQ(0))
        If Len(Nz(strConnString, "")) > 0 Then
            qdf.Connect = strConnString
        Else
            qdf.Connect = "ODBC;Driver={ODBC Driver 18 for SQL Server};Server=tcp:yourservernamehere.database.windows.net;Database=DBName;Uid=" & strUPN & ";Connection Timeout=30;Authentication=ActiveDirectoryInteractive"
        End If
    End If
    rsQ.MoveNext
Loop

ReconnectPassthrough = True

End Function

^ Above is good for interactive auth - i.e. when you don't have hybrid join.

 

[dalski]

Thanks John, very helpful. I was worried I'd have to convert all my recordsets to ADODB, but judging from your code that doesn't appear so .

 

[bossjohnc]

Thanks John, very helpful. I was worried I'd have to convert all my recordsets to ADODB, but judging from your code that doesn't appear so .

It will take some trial and error to get a good balance between working with linked tables and DAO (as you presumably are now) and performance. Moving to connecting direcly with ADO shouldn't be too taxing, depending on what you're doing, and will improve performance, but isn't 100% necessary in all cases.

ETA: It should be noted that I'm using DAO to change locally held queries which directly connect to Azure themselves using the connection string (which I'm adjusting) and the SQL within the passthrough query. I'm not addressing anything in Azure with this recordset, although it is possible to do so with linked tables and DAO. As I say though, this can get messy quickly with anything complex or including filters/parameters/where clauses.

 

[RonPaii]

Do you have any advice to share? I'm considering Azure but I am terrified.



This is concerning, is a Windows user account authentication safer?

• We are using Windows Authenticator.
  • Use all possible security measure available to your license on 365 accounts.
  • Limit remote access to corporate 365 accounts to only those users needing it.
    • With conditional access and locations assigned to 365 groups, you can assign users to local only and remote access.
  • Monitor remote connections of suspicious activity. Most of your connections will be coming from your local network. Except for users travailing far and wide, most remote connections will come from the same location for each user and will not jump around during the day.
• Splitting local AD and 365, keeps the internal account behind firewalls.
  • Use VPN for remote access.
  • Configure all user's default login for user access, never administrator. This includes your administrators.

Security is a never ending battle.

 

[bossjohnc]

• We are using Windows Authenticator.
  • Use all possible security measure available to your license on 365 accounts.
  • Limit remote access to corporate 365 accounts to only those users needing it.
    • With conditional access and locations assigned to 365 groups, you can assign users to local only and remote access.
  • Monitor remote connections of suspicious activity. Most of your connections will be coming from your local network. Except for users travailing far and wide, most remote connections will come from the same location for each user and will not jump around during the day.
• Splitting local AD and 365, keeps the internal account behind firewalls.
  • Use VPN for remote access.
  • Configure all user's default login for user access, never administrator. This includes your administrators.

Security is a never ending battle.

I'd add - remove the SQL admin login account as soon as you're ready to - to that list. No MFA protection on that one, go Entra only.

 

[tvanstiphout]

> go Entra only
This can be enforced in Azure Portal > your SQL server resource > Settings > Microsoft Entra ID > check the box for Support only Microsoft Entra authentication for this server.