The company I work for has an old Access database that I’d like to move the back end to Azure SQL Server.
I think the best way to do this is to migrate all the data to the new Azure db, and then link the tables in the back end file to their azure counterparts (although, if anyone has any suggestions otherwise, they’re welcome!). I have already migrated all of the data to the new azure db.
- The Access db has been split into a back end and ~10 different front ends (for different departments/roles)
- The front-ends will be stored on a mapped network drive (to a network share)
- Approximately 25 people will be using the db from their Active Directory joined computers
- We do have an Azure AD, it is NOT federated (so, no AD FS), but we do have Seamless SingleSSO enabled
The problem lies in the fact that I don’t want users to have to enter a password every time, and I’d rather not store credentials/passwords in not-secure ways. Interestingly enough, on one of the documentation pages from MS about linking Access to on-prem/Azure SQL Server, they specifically note:
If you use SQL Server Authentication, your password is stored in clear text on the linked Access tables. We recommend using Windows Authentication.
… despite the fact that Windows Authentication cannot be used with Azure SQL Databases! (At least, not that I know of). To some extent the easiest and most secure thing would be to use the “Active Directory Password” authentication method, and just have users enter their passwords every time. However, the application requires short but frequent use. So, unless we just started having users leave Access open the whole time, they’d need to enter their passwords very frequently. (Or maybe there’s another way I don’t know of to leave a connection open for a while?)
I’ve looked a bit into using an access token, and I think that’s the most promising avenue for the moment, but I don’t know how well it will work. I’m wondering if SSO enabled would allow the connection WITHOUT any additional authentication, but I don’t know if the ODBC driver (or any provider for that matter) would be able to make use of that.
I’ve also had some out-of-the-box ideas like just using SQL authentication with the password stored in a file on the network drive, but automating a task to run every night to change the password and update the file.
Have other people solved similar problems?
Am I being paranoid?
Any suggestions/advice/direction pointing is welcomed.
P.S. I apologize if it would have been to post to /r/AZURE. I think I will post something there next, but I thought I’d post here first!