UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multi-factor Authorisation, Office 365 And Azure SQL, Access 2016    
 
   
alancossey
post Dec 6 2018, 02:12 PM
Post#1



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


I have been playing with MFA. I have an Access front end linked to a couple of Sharepoint lists in Office 365. When I turn on MFA the data in those linked tables/lists is still updatable from Access. Surely it shouldn't be possible. I update it in Access and I can see the changes in Office 365. I update it in Office 365 and I can see the changes in Access. Access appears to bypass MFA. This means I can carry on using my Sharepoint lists from Office 365, but doesn't say much about MFA. So that is good news and bad news.

However, when I apply MFA to Office 365 where I have been using my Office 365 ID to log into an Azure SQL database from Access, I can find no way of getting to that data. It seems to mean that MFA works here and shuts me out. So here it is good news and bad news (mainly bad). Outlook is meant to work with MFA by means of an "app password". That doesn't seem to work with Access with Azure SQL/Office 365.

Two questions:

1) What's going on with the Sharepoint lists?
2) More importantly, what on earth do I do if a customer decides to switch on MFA in their Office 365 if I have been using Office 365 (Azure AD) IDs and credentials to log in. Give up and do a totally different security set up?


--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
alancossey
post Dec 11 2018, 03:07 PM
Post#2



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


Have I missed something or is MFA not a real show-stopper with Azure AD authentication using Access?

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
alancossey
post Dec 28 2018, 05:07 AM
Post#3



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


Anyone?

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
isladogs
post Dec 28 2018, 07:18 AM
Post#4


UtterAccess VIP
Posts: 1,926
Joined: 4-June 18
From: Somerset, UK


I remember reading this a few weeks ago and not having a clue what it was about.
The fact that it has been viewed almost 130 times without a response suggests I'm not alone in thinking that.

Suggest additional explanation might help readers.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
PhilS
post Dec 28 2018, 08:13 AM
Post#5



Posts: 653
Joined: 26-May 15
From: The middle of Germany


@Colin, I'm not sure whether you ask for clarification on MFA in General (MFA = Multi-factor authentication (not authorisation)) or on the specific questions regarding Office 365/Access/AzureSQL.

@alancossey, I've got no experience with Office 365 and MFA, yet. However, from just scanning the guide to set up multi-factor authentication for Office 365, I got the impression that you have to do a bit more than just "turn on MFA". Maybe you should elaborate some more on what exactly you did.

--------------------
Go to the top of the page
 
isladogs
post Dec 28 2018, 09:39 AM
Post#6


UtterAccess VIP
Posts: 1,926
Joined: 4-June 18
From: Somerset, UK


Hi Phil
I realised it was authentication but also have no experience with using that with Office and no SharePoint experience either.
So I'm certainly not the person to assist here!
I was just trying to encourage the OP to explain the various points more clearly

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
alancossey
post Dec 31 2018, 10:18 AM
Post#7



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


Please bear in mind that I am primarily an Access developer and not a network/security man.

Office 365 uses Azure Active Directory (Azure AD) authentication to identify people logging in. You can do it in various ways, including supplying an ID and password. You can use Azure AD working with Office 365 to use Office 365 logins as the basis for authentication to an Azure SQL database. I tried doing it using groups, but failed so I have had to set up Office 365 users directly as users in my Azure SQL databases. Since they only have a small number of users it has not been a major problem, but it is something I ought to revisit fairly soon.

This is quite handy as you can set up linked tables from an Access front end and not have to save the password as part of the link, whether using a DSN or using DSNless connection. Instead, when you open the Access front end you can create a cached connection to the Azure SQL database each time, e.g. by opening a temporary pass-through query programmatically. It is all very neat. If you do that, Access seems to cache the connection details and you can then open linked tables (and queries based on them) without having to supply any more credentials. This idea is based on the idea datAdrenaline (Brent Spaulding) brought to my attention about 10 years ago when Microsoft dropped workgroup security in Access and which we had a very, very long discussion about back then.

However, with both Office 365 and Azure SQL it is possible (and recommended?) that IT departments turn on Multi-Factor Authentication (MFA). Thus, as well as supplying a user ID and a user password, you then need to supply a third credential, supplied to you either by email or SMS each time you log in. This works nicely with newer apps like PowerApps, but does not work directly with older apps like Outlook. When MFA is switched on by an Office 365 administrator for an Office 365/Azure AD user, the next time the user logs into Office 365 itself they are prompted to say whether they want to receive this third credential via email each time or via SMS and are then additionally supplied with a 16 character "App Password" to use with applications like Outlook where you now have to supply your ID (as before) and the 16 character password instead of your "proper" password. Just using your "normal" Office 365 password with your Office 365 ID with Outlook no longer works - you have to use the 16 character password they supply you with. I thought that this would work for Access, but I've not been able to get that 16 character password to work, though I didn't spend too long trying, to be honest.

However, back on MFA with Access and linked tables with Azure SQL, by changing the Connect property of my DSN-linked tables to be "ODBC;DSN=MyDSNName;Description=My DSN Name;APP=Microsoft Office;DATABASE=MyDatabaseName;Authentication=ActiveDirectoryInteractive;" (note the "ActiveDirectoryInteractive" bit on the end), I can get it to sort of work. Sometimes. Partially. Not properly. I think I also managed it the other day with DSNless connections as well, but can't reproduce it at the moment.

Setting up a cache using a SQL pass-through query doesn't seem to work with this MFA-enforced setup. However, if I double-click on a linked table, I first get an error message and then get shown the ugly login screen you get with SQL Server-type databases if you normally get if you don't supply credentials properly (it asks for the user ID, the method of authentication, server and so on, i.e. stuff a user should not have to see (and freak out on)). If I ensure the user ID is correct and then press OK, I then get prompted (neatly) for the "proper" password in a webbrowser window. If I enter that correctly I then get told (neatly) via a second webbrowser screen that my third credential (for me using SMS) has been sent to my phone. If I get that from my phone then enter that into the webbrowser screen, Access then starts working OK. Sort of. Sometimes. Partially. Not properly.

At this point the first time I double-click on a linked table I get that same error message as before, but if I try a second time, the linked table opens. Woopee. The only problem is that some queries based on those linked tables don't work. Some do; some don't. Then I try again and eventually all of them may work. However, recordsets based on them don't necessarily work.

It's a real mess.

Do note that if you want to try this, an admin user in Office 365 will need to enable MFA for the user(s). At this point it is enabled only. When the user(s) logs into Office 365 they will need to go through the routine of giving email or mobile phone details, at which point MFA gets enforced (rather than just enabled).

The big worry for me is that an IT department will switch on MFA for some of my Office 365/Azure AD users at which time my Access applications will stop working. It might be possible to get them working, but as you can see from the above palaver, it is hugely hit and miss and the logging in is ugly, ugly, ugly (well, the first screen is, though the second and third are OK).

MFA has been used in Azure SQL since, I think, 2013, yet I've never seen anything from anyone anywhere, let alone Microsoft, saying how to use it with Microsoft Access.

What am I meant to do if the IT department turn on MFA for users using Azure SQL?

As for the Sharepoint thing bypassing MFA altogether, I have no idea what is going on there. I only use it for a very minor thing in-house, so am not worried for myself, but thought I ought to point out the problem.

Why is Microsoft ignoring Access users (or have I missed something)?

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
alancossey
post Dec 31 2018, 11:03 AM
Post#8



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


I forgot to mention I am using version 17 of the ODBC driver for SQL Server. I can also confirm that when I use DSNless linked tables it all works (or not) the same way as with DSNs (at least in my experience).

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
isladogs
post Dec 31 2018, 12:17 PM
Post#9


UtterAccess VIP
Posts: 1,926
Joined: 4-June 18
From: Somerset, UK


As I mentioned in my previous reply I have no experience of using MFA with Office 365.
However one thing stood out for me in your very detailed account:

QUOTE
When MFA is switched on by an Office 365 administrator for an Office 365/Azure AD user, the next time the user logs into Office 365 itself they are prompted to say whether they want to receive this third credential via email each time or via SMS and are then additionally supplied with a 16 character "App Password" to use with applications like Outlook where you now have to supply your ID (as before) and the 16 character password instead of your "proper" password. Just using your "normal" Office 365 password with your Office 365 ID with Outlook no longer works - you have to use the 16 character password they supply you with. I thought that this would work for Access, but I've not been able to get that 16 character password to work, though I didn't spend too long trying, to be honest.


If the MFA password is 16 characters, I don't see how this can be made to work in Access.
The maximum length of an Access password is 14 characters (the exception is 20 in Access 2007) - see Access specifications

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
alancossey
post Jan 1 2019, 03:49 AM
Post#10



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


Hi Isladogs (love the name, by the way - for anyone interested, see https://en.wikipedia.org/wiki/Isle_of_Dogs),
I don't think that length limit would be a problem as the 14 characters limit you speak of is the limit for opening an Access database itself, i.e. a database password. The Azure AD app password is for accessing Azure-related resources (in this scenario an Azure SQL database).

I've tried using an Azure App Password with Access once more and it just errors out.

Happy New Year!


--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
isladogs
post Jan 1 2019, 07:07 AM
Post#11


UtterAccess VIP
Posts: 1,926
Joined: 4-June 18
From: Somerset, UK


Hi Alan
Doh! Well I clearly got the wrong end of the stick there!!!

Is there any fundamental difference between just authenticating a password against active directory and what you are describing?

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
alancossey
post Jan 1 2019, 09:06 AM
Post#12



Posts: 560
Joined: 26-September 05
From: Norfolk, UK


Hiya,
Authenticating against (Azure) AD is what we have been using so far, but with MFA it requires that difference in the connection string. With AD you can pass the ID and password into a connection of some sort, e.g. via a SQL Pass Through Query to create a cache which the linked tables then use. With MFA I can't find a way of passing the ID programmatically and the password and 3rd item (for me a code sent to me on my phone) both have to be entered via a webbrowswer window which pops us. So, yes, somewhat different.

If no-one has any working answers here, I think I will raise a service request in Office 365 to try to get Microsoft to come up with something concrete. I can't afford for my Access apps to stop if an IT department introduces MFA for Azure AD / Office 365 authentication.

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th December 2019 - 05:39 AM