CODEWITHSUNDEEP
jsonazure.net-coreazure-active-directoryazure-sql-database
Fike Rehman
Fike Rehman

Reputation: 805

Invalid value for key 'authentication'

I have a .NET Core 3.0 app where am trying to connect to a Azure SQL database using EF Core and Active directory integrated authentication.
I have verified that I have access to this database from my machine as I can connect to it just fine using SQL server management studio and 'Azure Active Directory-Integrated' authentication.

However, when I try to read data in my app (using EF Core), I always get a System.Argument exception with the following statement:

Invalid value for key 'authentication'

Exception details point to the Db connection string.

So, here is my connection string from my dev appsettings.json file:

"ConnectionStrings": { "MCDB": "Server=tcp:dev-media-center-sql.database.windows.net,1433;Initial Catalog=MediaCenter;Persist Security Info=False;User ID={my User ID};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated;" },

I have tried hard coding the connection string directly in my code, thinking that there might be a problem with my JSON but I still get the same exception.

Is "Active Directory Integrated" not a valid value for the 'Authentication' keyword? If not, what is it then? I have already tried "ActiveDirectoryIntegrated" (with no spaces) and /"Active Directory Integrated"/ (escaping double quotes) but to no avail.

Upvotes: 19

Views: 19981

Answers (6)

Scott Semyan
Scott Semyan

Reputation: 225

You can use "Authentication=Active Directory Managed Identity" and be sure to set the User ID to the Object(principal)ID of the identity.

Example:

Data Source=dev-westeurope-001.database.windows.net;
     Initial Catalog=dev-westeurope-001;
     Authentication=Active Directory Managed Identity;
     User ID=[PrincipalId];
     TrustServerCertificate=True;

Upvotes: 0

Raghava Akula
Raghava Akula

Reputation: 111

I saw that I was referring to System.Data.SqlClient for DB connectivity in my legacy application. I replaced it with using Microsoft.Data.SqlClient and it resolved the exception.

Upvotes: 2

Francisco Vilches
Francisco Vilches

Reputation: 3738

If you're using EF Core with the Microsoft.EntityFrameworkCore.SqlServer package, then be aware of this tip from the Microsoft Learn page on the EF Core Database provider:

The Microsoft.Data.SqlClient package ships more frequently than the EF Core provider. If you would like to take advantage of new features and bug fixes, you can add a direct package reference to the latest version of Microsoft.Data.SqlClient.

That being said, the fix is to add the package to your project:

cropped screenshot showing the "Microsoft.Data.SqlClient NuGet" package selected for installation in a user's Solution file in Visual Studio.

Upvotes: 25

Lesair Valmont
Lesair Valmont

Reputation: 902

If your only option for connecting to the Azure SQL Database is through Active Directory authentication, and your ADO.NET SqlConnection object is having problems trying to recognize the "Active Directory Integrated" value as the Authentication, you can still use the "Active Directory Password" value if you know the credentials of the user you're using to try to connect to the database. The connection string will be something like this:

"Server=tcp:yourservername.database.windows.net,1433;Initial Catalog=yourdatabasename;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password";"

That worked for me.

Upvotes: -3

roney
roney

Reputation: 1082

Upgrading to the latest version of Microsoft.Data.SqlClient resolved the issue for me.

Upvotes: 4

Search4Sound
Search4Sound

Reputation: 186

This is essentially the same problem discussed in relation to a newer .NET Core version, which was answered as currently unsupported in that version, however I have added a comment where I note that it now works - see EF Core 3.1 using Authentication=Active Directory Integrated

Upvotes: 1

Related Questions