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)

I checked your connection string, which is different from the connection string format of my ad verification. It is recommended that you obtain the connection string according to my answer prompt and write the Connection strings directly into the code for debugging — Jason Pan, Jun 03 '20 at 06:48
E.g: `services.AddDbContext(options =>options.UseSqlServer("Server=tcp:*.database.windows.net,1433;Initial Catalog=p**t;Persist Security Info=False;User ID=*.microsoft.com;Password=**;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';"));` — Jason Pan, Jun 03 '20 at 06:49
@Jason I don't want to specify any user, I want to use the identity assigned to the web application — fra, Jun 03 '20 at 08:33
From your code, I saw that your sql server is connected using the ad authentication method. To use the ad authentication connection, you must use `Azure SQL Managed Instance`. Then you must specify the admin user authorization according to the documentation. — Jason Pan, Jun 03 '20 at 08:46
Do you want ad authentication for your web application or do you use ad authentication for sql connection? These are two different issues. — Jason Pan, Jun 03 '20 at 08:48
My solution is based on your code to solve the problem of your database using ad authentication to connect. — Jason Pan, Jun 03 '20 at 08:50
my application, deployed to an Azure WebApp, should work using the identity assigned to the app. BTW, the exception happens when the connection string is parsed, so before trying to connect — fra, Jun 03 '20 at 08:54
https://learn.microsoft.com/en-us/azure/azure-app-configuration/howto-integrate-azure-managed-service-identity?tabs=core2x — Jason Pan, Jun 04 '20 at 05:51
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/215276/discussion-between-jason-and-fra). — Jason Pan, Jun 04 '20 at 05:52

score 5 · Answer 1 · answered Sep 25 '20 at 23:37

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 ID=myruntimeuser@mydomain.com;Persist Security Info=true;Encrypt=true;TrustServerCertificate=true;MultipleActiveResultSets=true

score 4 · Accepted Answer · answered Jun 12 '20 at 15:38

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.

score 1 · Answer 3 · answered Feb 08 '22 at 07:34

1

Upgrading the Nuget packages: Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer to 6.0.1 and using Authentication=Active Directory Managed Identity in the connection string helped me resolve the issue.

answered Feb 08 '22 at 07:34

Harshit Bhandari

11
1

1

Be sure to use the Object(Principal)ID and not the ClientID for the User ID. E.g. Data Source= dev-westeurope-001.database.windows.net;Initial Catalog= dev-westeurope-001;Authentication=Active Directory Managed Identity;User ID=[PrincipalId];TrustServerCertificate=True; – Scott Semyan Jun 10 '22 at 20:12
Do you have a link or guidance please on how to setup the managed identity? Been going crazy trying to get it to work – rumblefx0 Jun 23 '23 at 16:37

Jason Pan · Answer 4 · 2020-06-03T09:08:42.290

0

UPDATE

If you use azure msi, pls read this document.

https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

PRIVIOUS

Your problems maybe not configure in portal. You can follow the offical document to finished it, then try again.

First, you need to create SQL managed instances which maybe cost your long time. Then u need to configure Active Directory admin and your db. When you finished it, you will find ADO.NET(Active Directory password authentication) in your SQL database ->Connection strings in portal. You can copy and paste it in your code to solve the issue.

I have tried it by myself, and it works for me. For more detail, you can see this post.

edited Jun 03 '20 at 09:08

answered Jun 03 '20 at 06:43

Jason Pan

15,263
1
14
29

1

Whilst this may theoretically answer the question, [it would be preferable](//meta.stackexchange.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. Alternatively flag as "duplicate". – janw Jun 03 '20 at 07:50
In fact, it's better to suggest the original answer as duplicate. – Gert Arnold Jun 03 '20 at 10:43

EF Core 3.1 using Authentication=Active Directory Integrated

4 Answers4

Linked