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

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’.

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.

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

@YoungGah

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?

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

@gabarach

@gabarach would that be a reasonable workaround for you?

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.

@kevcunnane

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

@karelz

@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.

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.

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.

@divega

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

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

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?

@srinuece60

@srinuece60

Have you tested with latest preview version of Microsoft.Data.SqlClient ?

@cheenamalhotra

@cheenamalhotra FWIW, I just tested this with https://www.nuget.org/packages/Microsoft.Data.SqlClient/1.0.19189.1-Preview and I still see the exception when targeting .NET Core:

System.ArgumentException: Keyword not supported: 'authentication'.

Is this supposed to be addressed?

@srinuece60

@srinuece60

Have you tested with latest preview version of Microsoft.Data.SqlClient ?

Yes. I tested with that, still same issue

@divega

@divega

I get it now. The work is in progress for .NET Core support, we only have it working for applications targeting .NET Framework as of now. Likewise the issue is addressed in our backlog and planned for GA. I will mark it accordingly in Kanban board to avoid any confusions.

It worked for me doing this from an Azure App Service using MSI.

string token = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;           

using (SqlConnection con = new SqlConnection("Server=tcp:<servername>.database.windows.net,1433;Database=<dbname>;"))
{
    con.AccessToken = token;
    con.Open();

I got these packages added to my project:

<PackageReference Include="Microsoft.Azure.Services.AppAuthentication" Version="1.3.0" />
<PackageReference Include="System.Data.SqlClient" Version="4.7.0-preview7.19362.9" />

Is there an ETA for this issue?

@srinuece60

@srinuece60

Have you tested with latest preview version of Microsoft.Data.SqlClient ?

I taken the latest version of SQlclient. but my issue is not solved.
Again I tried with the below way now It's working for me.

I created one Azure App with the name of 'CoreApp'. then I added user with the same name of Azure App name 'CoreApp' in Azure SQL Server and granted the permissions for that user by using the below commands.

Then I used the below code in Web API under Startup.CS file with in the method of 'services.AddDbContext'

Now I am able to access the Azure SQL with AD passwords authentication with in .net core 2.0

@adria3a3

@adria3a3 @divega @gabarach

Active Directory Password Authentication support has been added to Microsoft.Data.SqlClient (NetCore) in the latest preview version 1.0.19221.1-Preview | Release Notes.

Closing the issue.

@adria3a3

@adria3a3 @divega @gabarach

Active Directory Password Authentication support has been added to Microsoft.Data.SqlClient (NetCore) in the latest preview version 1.0.19221.1-Preview | Release Notes.

Closing the issue.

@cheenamalhotra, could you point a fellow developer to either the source code/merge request that implemented the change to support the Active Directory Password Authentication method.

I've made sure to read through this entire thread and there were no examples specifically geared to this approach using .NET Core, specifically I referring to configuring the SqlConnection in .NET Core to use (what I assume ) the implemented provider for Active Directory Password Authentication.

Additionally, are there any plans to add Active Directory Interactive Authentication method to .NET core where the application will be obtain a token automatically when required?

Please correct me if i'm wrong and provide guidance otherwise.

@abjoseph

Hi @abjoseph

Usage in NetCore for AAD Password Authentication is identical to .Net Framework, and same connection string works for both targets. Existing Documentation: Using an Azure AD identity to connect from a client application

We are currently working on gathering documents for all existing and new features for M.D.SqlClient which will provide more clarity. We'll also add more details for Interactive Authentication (which is currently supported in .Net Framework), but for now you can use the same connection string as Integrated Authentication connection string with Active Directory Interactive as value for Authentication connection property, without the need to provide accessToken or credentials.

For .NET Core to support Interactive authentication, it is in our backlog and shall be picked up in future iterations, we were hitting some roadblocks from MSAL.NET hence its disabled for now. Whenever we touchbase again, Integrated and Interactive modes need to be re-investigated and hopefully will be supported for .NET Core in future driver versions.

Thanks for checking in!

I too would like this in .net core 2.2,
And would expect it to be available in .net core 3.x right out of the box,
Else i'm scrapping any applications using .net core and reverting back to full framework.

And no, I don't want to supply pwds either.

Would you have a link to the work task on getting "Authentication=Active Directory Integrated" working in .net core?

Address PR #4336 review feedback (pipeline version computation)

Navigation Menu

Repository navigation

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

Original issue text

65 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Original issue text

Activity

karelz commented on Nov 10, 2016

gabarach commented on Nov 11, 2016

karelz commented on Nov 11, 2016

saurabh500 commented on Nov 11, 2016

karelz commented on Nov 11, 2016

kevcunnane commented on Dec 1, 2016

karelz commented on Dec 1, 2016

kevcunnane commented on Dec 1, 2016

developermj commented on Jul 31, 2017

movna commented on Sep 25, 2017

karelz commented on Sep 25, 2017

m1nkeh commented on Oct 30, 2017

saurabh500 commented on Oct 31, 2017

65 remaining items

cheenamalhotra commented on Jul 23, 2019

divega commented on Jul 24, 2019

srinuece60 commented on Jul 24, 2019

cheenamalhotra commented on Jul 24, 2019

crgarcia12 commented on Aug 6, 2019

adria3a3 commented on Aug 7, 2019

srinuece60 commented on Aug 7, 2019

cheenamalhotra commented on Aug 10, 2019

abjoseph commented on Aug 21, 2019

cheenamalhotra commented on Aug 21, 2019

multco-malan commented on Sep 11, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions