EF Core 3.1 using Authentication=Active Directory Integrated

Question

[Update 1]

I could make it work using the following connection string

Server=tcp:mydatabaseserver.database.windows.net,1433;Initial Catalog=mydbname

and implementing an interceptor as mentioned in this article.

This proves that Azure is correctly configured, and the problem is somewhere in the application (maybe a missing package?).

Anyway, I would still like to be able to change the connection string and switch between AAD authentication and sql authentication, without additional logic in the application.

[/Update 1]

I'm using EF Core 3.1.4 on an Azure WebApp, and I would like to use the Azure AD identity assigned to the application for authentication, but I run into the following exception:

ArgumentException: Invalid value for key 'authentication'.
Microsoft.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(string keyword, object value)

This is the connection string:

{
    "ConnectionStrings": {
        "Admin": "Server=tcp:mydatabaseserver.database.windows.net,1433;Initial Catalog=mydbname;Authentication=Active Directory Integrated"
    }
}

I initialize the context using the following code:

var connectionString = this.Configuration.GetConnectionString("Admin");
services.AddDbContext<NetCoreDataContext>(builder => builder.UseSqlServer(connectionString));

The Microsoft.Azure.Services.AppAuthentication package is also imported (version 1.5.0)

Answer 1

Welcome to the Net frameworks/runtimes hell.

Currently ActiveDirectoryIntegrated and ActiveDirectoryInteractiveauthentication options are not supported for NetCore apps.

The reason is that starting with v3.0, EF Core uses Microsoft.Data.SqlClient instead of System.Data.SqlClient. And the most recent at this time version of Microsoft.Data.SqlClient (also the preview versions) supports these two options only for NET Framework.

You can see similar question in their issue tracker Why does SqlClient for .Net Core not allow an authentication method 'Active Directory Interactive'? #374, as well as the documentation of the SqlAuthenticationMethod enum - ActiveDirectoryIntegrated (emphasis is mine):

The authentication method uses Active Directory Integrated. Use Active Directory Integrated to connect to a SQL Database using integrated Windows authentication. Available for .NET Framework applications only.

With that being said, use the Authentication workaround, or wait this option to be eventually implemented for Net Core.

Answer 2

Active Directory Integrated wasn't working for me in .NET Core 3.1 but it works now ever since I installed the NuGet package Microsoft.Data.SqlClient (I installed version v2.0.1). It now works with the following connection string:

"MyDbConnStr": "Server=tcp:mydbserver.database.windows.net,1433;Database=MyDb;Authentication=ActiveDirectoryIntegrated"

Note: it also works if I have spaces between the words like this:

"MyDbConnStr": "Server=tcp:mydbserver.database.windows.net,1433;Database=MyDb;Authentication=Active Directory Integrated"

And it also works if I include escaped quotes like this:

"MyDbConnStr": "Server=tcp:mydbserver.database.windows.net,1433;Database=MyDb;Authentication="Active Directory Integrated""

Finally, note that there are additional properties which can also be used in the connection string:

;User [email protected];Persist Security Info=true;Encrypt=true;TrustServerCertificate=true;MultipleActiveResultSets=true