SqlClient: Support SqlAuthenticationProvider and authentication connection string keyword for AAD auth

[gabarach]

AAD authentication, including MFA, is already supported on SqlClient for .NET Core from version 2.2, by first obtaining the AccessToken using the ADAL library or some other method against Azure AD, and then setting it on the AccessToken property of the SqlConnection.

This issue is about enabling a different pattern for AAD authentication that is supported in .NET Framework, in which SqlClient takes care of obtaining the AccesToken automatically based on an authentication keyword in the connection string and SqlAuthenticationProvider implemetnations.

Original issue text

Please add AAD auth support for Azure SQL Database the way how that' s supported in .NET framework 4.6. We should be able to use Authentication modes like "Active Directory Integrated" and "Active Directory Password" in the connecting string.
Currently, including aforementioned authentication modes result into following error:
“Keyword not supported: ‘authentication’.

[karelz]

We need to port over code from full .NET Framework.
It's a significant amount of work. There are also dependencies on other component which haven't been open sourced yet, so it's tricky.

[gabarach]

We need to port over code from full .NET Framework.

So does it mean the AAD auth support for Azure SQL Database may not be available anytime soon?

Thanks & Regards,
Gajendra Barachha

[karelz]

Yes, priority-wise it is not too high up given the cost/benefit ratio (as understood today).
If there is significant customer need and feedback, we are always opened to change our point of view. So far this issue has 1 vote.

@YoungGah @saurabh500 are there other ways to authenticate Azure SQL Database from .NET Core?

[saurabh500]

Right now connections to Azure SQL DB can be accomplished by specifying the Used Id and Password to the SqlConnection via the connection string

[karelz]

@gabarach would that be a reasonable workaround for you?

[kevcunnane]

We have a request for the MSSQL extension in VSCode to support Azure AD authentication - see microsoft/vscode-mssql#435. This is dependent on this enhancement since we use the SqlClient stack in .Net Core to connect. Adding this comment to help track this ask.

[karelz]

@kevcunnane why is the workaround above not sufficient for you?

[kevcunnane]

@karelz - the workaround is viable for most cases (in my opinion). I'm trying to add our user's voice to the conversation so that the team can take this on board when prioritizing. I understand this is an enhancement request - for the same reasons that Azure AD support was added to .Net and to ODBC drivers, it would be good to have symmetry here and allow users the benefits that come with Azure AD integration (e.g. no longer manage at a server level, but instead using AD-managed credentials).

As to why I'm raising this: It's something a user of our new MSSQL extension to VSCode raised as an issue. For many users they might not have permissions to create new users and only have an Azure AD login permission, or their company might mandate use of AD authentication. This is lower priority to our team than a number of other .Net Core related issues but it's likely that as Azure AD authentication increases in usage, this may become higher priority.

[developermj]

Has there been any traction on this? My employer is moving on-prem applications to the cloud and our current standard is to use Active Directory Integrated authentication. We would really like to use this in our applications. Thanks.

[movna]

Any movement on this? Using AAD for Azure SQL is the most secure way available now and having support for this is a must. Thanks.

[karelz]

@divega @saurabh500 @corivera can comment on progress / prioritization / workarounds, given we have 10 votes now.
AFAIK we didn't start any work yet.

[m1nkeh]

does me giving this issue the thumbs-up count as a "vote" ?

[saurabh500]

We are working with the SQL Security team for a AAD auth solution which can work cross platform for .Net Core and which can have a decoupled design for SqlClient in .Net core so that the users who are not using AAD Auth don't need a dependency on all the AAD packages.
For AAD Auth on Windows, the design was very tightly coupled with a dependency on an additional DLL written in Native Code (C++) which worked on Windows only. The redesign is going to take some time and collaboration and coordination with multiple internal teams.

Meanwhile, are folks on this thread looking to using AAD Azure SQL DB auth using only Windows or are there users who intend to take this capability on Linux as well?