Online MS Access Databases on Azure - Enhanced security using Azure Active Directory accounts.
HTML-код
- Опубликовано: 8 сен 2024
- In this episode, we continue our journey of moving our Microsoft Access applications online with Azure. In the last episode, we created a single user/password database that could be used anywhere on the internet. In this video, we'll see how to use any personal or business Microsoft AD login as users in our database, whether they are hotmail.com, live.com, mycompany.com or other AD logins. Using this method releases us from storing and managing passwords, and also can allow multi-factor authentication to be used (where it is enabled). As a bonus, at the end of the episode I show how to allow your app to see the username of the person who logged in, so you can display a welcome message or apply application level security for menus and other things. Let's go!
Related Videos:
Access on Azure SQL - How to create an online Access application using Azure SQL and ODBC
• Access on Azure SQL - ...
Online MS Access Databases on Azure - Enhanced security using Azure Active Directory accounts.
You are watching this video now!
Microsoft Access on Azure SQL: ODBC Overview with DAO
• Microsoft Access on Az...
How to Migrate Slow Queries to Azure for Speed in Your Azure-Enabled Microsoft Access Application
• How to Migrate Slow Qu...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess #azuresql
• Online MS Access Datab...
*If you are experiencing multiple logins* , you can fix that by refreshing your table links when your app opens. It will prompt the user one time for their AD login and that's it:
Sub LinkTables()
Dim strCnn
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "PERSON"
DoCmd.DeleteObject acTable, "PROJECT"
DoCmd.DeleteObject acTable, "PROJECT_TIME"
DoCmd.DeleteObject acTable, "MyCurrentUser"
DoCmd.SetWarnings True
strCnn = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=tcp:access-demos-server.database.windows.net,1433;Database=PROJECT-TIMESHEETS;Authentication=ActiveDirectoryInteractive;UID=username@whatever.com;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
DoCmd.TransferDatabase acLink, "ODBC", strCnn, acTable, "PERSON", "PERSON"
DoCmd.TransferDatabase acLink, "ODBC", strCnn, acTable, "PROJECT", "PROJECT"
DoCmd.TransferDatabase acLink, "ODBC", strCnn, acTable, "PROJECT_TIME", "PROJECT_TIME"
DoCmd.TransferDatabase acLink, "ODBC", strCnn, acTable, "MyCurrentUser", "MyCurrentUser"
End Sub
These videos are really helpful, really appreciate them :)
Thanks!
@@seanmackenziedataengineering Hey again Sean, I'm now at this point, I've got a lot of views I'm wanting to bring in, I'm guessing the answer will be no on this one, but at 16:40 where it asks for the unique identifier, is there anyway around this, for the most part they wont need to select one, but for a couple they would, I think that might be a bit too much for my users
Pls show us how to link ms access to dataverse in power app
Great idea! Actually, this topic is on my list for future videos!
very cool! Where is the documentation on this functionality? I am making a VSTO addin and wondering how to incorporate Azure AD as the login function. Do you have sourcecode for this project that maybe I can modify for a VSTO addin?
This functionality is included in the ODBC driver that I used for the demo. It is SQL Server Version 17, which includes support for Azure Active Directory logins. So, in VSTO (or .Net in general), you can create a SqlConnection or OdbcConnection object and the AAD browser authentication should start automatically to complete the process. There isn't really any source code to show, but you can see a SqlClient connection here ruclips.net/video/atYsEBsATqw/видео.html
Good luck and let me know how it goes!
thanks
You're welcome!
Thank you very much for posting this informative video, Sean! I have a problem...whenever my users open my access database (with linked Azure SQL tables) , it demands that the user authenticate over and over for every single Azure SQL table... How can I make it so that users only have to authenticate once?
That's an interesting problem. Let me take a look and see. Stay tuned.
@@seanmackenziedataengineering , I'm not sure but it may have something to do with Multi-Factor Authentication.
@@seanmackenziedataengineering , have you had any insights into this problem? Thanks!
@@gioargentati7802 I was able to recreate the problem. I wonder if it is a driver issue (you can try Driver 18 as it is newer, or a newer version of 17). However, there is a workaround to get all tables authenticated:
Program starts > odbc prompt > choose Active Directory Interactive > put in username > OK > online AD asks password, put in > second ODBC prompt > Select Active Directory Interactive > put in proper username again > OK > all tables are now authenticated > no additional prompts
This is shorter, but a hassle for users. I'll test Driver 18 to see if it is better.
Thank you very much for researching this, @@seanmackenziedataengineering. I tried the second approach you outlined. It worked okay for a database containing only linked tables but it did not work when I tried to open a form that used many tables. I will try ODBC Driver 18. Thanks again for your help with this! :)
I will appreciate it pls post simple approach to Link ms access to azure. With less stress and code. Pls
This is a more simple approach Access on Azure SQL - How to create an online Access application using Azure SQL and ODBC
ruclips.net/video/vnlqZcgRLm8/видео.html
@@seanmackenziedataengineering thanks for your kind responds. how do I share the front end to other user and still Link to azure
@@akokohjoel9854 just give them the Access file and tell them to open it on their computer. Make sure you set a rule on the database in Azure to allow their IP address, or open a IP range .
@@seanmackenziedataengineering pls I am your student can you create a video on how it is done. Plssss