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?

[saurabh500]

Also what kind of auth satisfy your requirements more? Fedauth username and password or integrated auth?

[m1nkeh]

hi @saurabh500 , my requirement is AAD that differs from the log on to my machine (i think this is fedauth ye?) the reason for this is that i work with many clients, and need to log in to multiple Azure AD instances from time to time... so this is my primary requirement.

Regarding platform, i would say that Windows would satisfy my requirement very well, however i am contemplating moving over to work on macOS, and would very much like to be able to use AAD against SQL Svr in Visual Studio Code, but it would be a bonus

[rgbattersby]

OSX support would be very well received in my business.

[saurabh500]

Thanks @m1nkeh and @rgbattersby

@m1nken Yes, I am talking about FedAuth which is used to authenticate to Azure SQL using your domain account.
I want to know if you folks actively use AAD Integrated Auth or is it AAD Username and password in your connection string? Or is it a good mix of both?

[m1nkeh]

AAD Username and Password, yes.

[rgbattersby]

Ideally both, to support users who generally belong to other AD environments such as contractors etc and our own employees.

[mkushakov]

Since SQL Operations Studio goes to Preview stage, original request of supporting AAD username and password on MacOS and Linux needs to be upvoted and uplifted with higher priority.

[rlarno]

Came here from SQL Operation Studio (Preview). As we only allow ad-hoc query to our Azure SQL using Azure AD (username/password), not via SQL Login (username/password).
People are using Windows & MacOS (but mostly Windows)

[erik-mileiq]

This is critical for Mac-based teams that are moving to Azure. Please prioritize this.

[hiramegl]

AAD Username and Password and windows is what we use in our projects.

[carbritomcm]

Came here from SQL Operations Studio (Preview) as well and it would be very important to have AAD authorization working (in both username\password and Integrated).

[erick-thompson]

As more and more Azure resources support MSI, we are actively converting all possible resources to use MSI. Why? If there isn't a secret, you can't leak it.

So insofar as this issue relates to MSI support in .NET Core, it is quite important.

[cleverguy25]

Is this the right place to up-vote this? Or is there another place like a user voice site?

[danielearwicker]

Workaround is to put the SQL password in a key vault and use MSI to authorise the app to access the key vault, which does work from dotnet core.

[dougiejay]

We absolutely want this feature to remove the hassle of keeping passwords in keyvault and then pulling them from .NET core code. The workaround is ok from a security point of view, but adds an extra layer of maintenance that isnt ideal.

[zvrba]

I have the same problem as @AndersMalmgren I tried specifying UserId in the connection string, but then the SqlConnection constructor fails saying that UserId and AccessToken cannot be used together.

[zvrba]

@AndersMalmgren I have solved it by using two slashes in the scope: "https://database.windows.net//.default" It is vaguely documented here: https://github.com/AzureAD/microsoft-authentication-library-for-dotnet/wiki/Adal-to-Msal#warning-should-you-have-one-or-two-slashes-in-the-scope-corresponding-to-a-v10-web-api

[AndersMalmgren]

Yeah, I got it working too some while ago.

 services.AddScoped<DbContext>(p =>
                    {
                        var ctx = new AuthenticationContext("https://login.microsoftonline.com/xxx");

                        var result = ctx.AcquireTokenAsync("https://database.windows.net/", new ClientCredential("xxx", "xxx"))
                            .ConfigureAwait(false)
                            .GetAwaiter()
                            .GetResult();

                        var db = p.GetService<MyDbContext>();

                        ((SqlConnection)db.Database.GetDbConnection()).AccessToken = result.AccessToken;

                        return db;

                    });

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[nimitad]

Yeah, I got it working too some while ago.

 services.AddScoped<DbContext>(p =>
                    {
                        var ctx = new AuthenticationContext("https://login.microsoftonline.com/xxx");

                        var result = ctx.AcquireTokenAsync("https://database.windows.net/", new ClientCredential("xxx", "xxx"))
                            .ConfigureAwait(false)
                            .GetAwaiter()
                            .GetResult();

                        var db = p.GetService<MyDbContext>();

                        ((SqlConnection)db.Database.GetDbConnection()).AccessToken = result.AccessToken;

                        return db;

                    });

Hi - What is "xxx" supposed to be in "var ctx = new AuthenticationContext("https://login.microsoftonline.com/xxx");"

[David-Engel]

@nimitad It's the authority you are authenticating against:
https://github.com/AzureAD/azure-activedirectory-library-for-dotnet/wiki/AuthenticationContext-the-connection-to-Azure-AD#authority-validation

[srinuece60]

Is .net core 2.2 web api can work for SQL with Azure AD Password Authentication.
(but I am facing the error "Exception: Keyword not supported: 'authentication'")

[cheenamalhotra]

@srinuece60

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

[divega]

@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

[cheenamalhotra]

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

[crgarcia12]

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" />

[adria3a3]

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

[cheenamalhotra]

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

[abjoseph]

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

[cheenamalhotra]

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!

[multco-malan]

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?