Add Active Directory Universal Authentication support for Azure SQL database
We are migrating password authentication to Azure AD authentication for Azure SQL databases. And the Azure AD is not connected to the AD in corpnet. So we can't use Azure AD Integrated Authentication to access DB on Azure. Also the Azure AD requires certificate authentication, so we can't use Azure AD Password Authentication either. Active Directory Universal Authentication is the only choice.
As always, one has to be good in choosing the right keywords for google to come to this page. And, here starts the best part, where I started finding the nuances to get going until I could see the message "Connection Successful!". Anyways I broke my head so much that I decided to spend some time to write this up, so that folks coming to this page can stop looking around further and take it from here.
1. Unless one scrolls up after clicking the link above and reads that .net Framework 4.6+ is required, Azure AD auth option won't work after you click Test/Connect button. In my case I never cared to scroll up since the download link was below the change log write-up. I had .net Framework 4.5.2 and it didn't obviously work with an error like "unable to load the ADALSQL.DLL". So I installed the "Active Directory Authentication Library for SQL Server" instead of the full framework since I didn't find the need for it yet and is the only one needed for the Azure AD auth to work.
2. I'm not sure if it is a bug or expected behavior, but option to specify the database name to connect to, doesn't show up all the time in the add connection dialog after selecting "Azure username & password" option. "Initial Catalog=<db_name>" had to be added in the "Advanced->Additional parameters in Connection String", if one has no access to "master" db to list all the databases.
3. Some links one gets from Google searches says that one has to mention the user name as user@<azure ad domain> or user@<server name> etc,. which was not needed in my case. I was using native AD with email id as user anyways and not the external ones like *.outlook.com or *.gmail.com user ids (or something like that, coz I'm not entirely sure about the external stuff)
Hope this helps someone who's so picky not to setup SSMS (a 800+MB download) for just querying the DB!
Token expiry looks to be handled smoothly as well. Thanks again for building this in Joe, slick job as always.
This is looking really good so far Joe. I'll check the expired token behaviour overnight, everything else looks polished. Thanks a lot!
Thanks for the tips, Eric
A better UX can be achieved in the ADAL approach by specifying the string "common" as the tenantId for the initial token request. This kicks off a flow that allows the true tenantId to be discovered.
We're in the same boat of trying to deprecate database credentials and moving towards AD authentication. As a result We also would like to see support for connecting with a MFA protected user. We're currently unable to configure the linqpad data context with an AzureAD user that is protected by MFA. I've included examples for two different flows that could be used to support this scenario;
1) via AppAuthentication library: http://share.linqpad.net/7lod3e.linq
Leverages credentials that are cached within visual studio or azure CLI.
2) via full ADAL: http://share.linqpad.net/g82eqe.linq
Requires AD tenant to be specified and then prompts for credentials on demand.
Happy to lend any assistance I can to make this happen.