16

Getting Keyword not supported: 'authentication' error while trying to connect an azure DB through 'Active Directory Integrated' option in .NET core 2.1 project.

Note: I am using EF core to connect the Data source.

suresh
  • 329
  • 2
  • 4
  • 8
  • can you add you code snippet for better clarity... – Md Farid Uddin Kiron Mar 07 '19 at 06:33
  • This is my local.settings connection string "ConnectionString": "Data Source=tcp:********.windows.net,1433;Initial Catalog=********;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated", and when creating context with EF core i'm getting the error in below line of code WriteLogMessage($"Database Connection set to {Database.GetDbConnection().Database} and data source {Database.GetDbConnection().Database}") – suresh Mar 07 '19 at 07:25
  • @suresh could you post the error message and full call stack. it's diffcult to help without sufficient information – axfd Mar 13 '19 at 06:49

3 Answers3

24

If you're still having the issue, make sure you have Microsoft.Data.SqlClient package installed, not System.Data.SqlClient. They both contain SqlConnection class, switching the package for the first one fixed the issue for me.

Maciej Wanat
  • 1,527
  • 1
  • 11
  • 23
18

TL;DR As called out by @Aamir Mulla in the comments, this has officially been added since Version 2.0.0

UPDATE - 16/08/2019
Active Directory Password Authentication has now been added for .NET Core in Microsoft.Data.SqlClient 1.0.19221.1-Preview

Unfortunately, the authentication keyword is not yet fully supported in .NET Core. Here is an issue which discusses this.

But .NET Core 2.2 has added some support for this use case as mentioned in this comment. The basic idea is to get the access token by any means (ADAL, REST, etc.) and set SqlConnection.AccessToken to it.

As for using this with EF Core, there's a good discussion about this in this github issue and in particular the comment by mgolois provides a simple implementation to the solution that cbriaball mentions in the thread.

Here is the same for reference

Note that this sample is using the Microsoft.Azure.Services.AppAuthentication library

// DB Context Class
public class SampleDbContext : DbContext
{
  public SampleDbContext(DbContextOptions<TeamsDbContext> options) : base(options)
  {
    var conn = (System.Data.SqlClient.SqlConnection)this.Database.GetDbConnection();
    conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;
  }
}

// Startup.cs
services.AddDbContext<SampleDbContext>(options =>
{
  options.UseSqlServer(<Connection String>);
});

The connection string would be something like this
Server=tcp:<server_name>.database.windows.net,1433;Database=<db_name>;

PramodValavala
  • 6,026
  • 1
  • 11
  • 30
3

As of today 7/18/2022 , I am still getting the issue from Azure when trying to use it through ManagedIdentity.

The microsoft doc at https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cefcore%2Cdotnetcore

to use managed identity we need to use the connection string in this format!

"Server=tcp:.database.windows.net;Authentication=Active Directory Default; Database=;"

But looks like Azure is not liking it! enter image description here

However, adding the access token helped!

var connectionString =
                "Server=tcp:yourazuresqlservername.database.windows.net; Database=yourazuresqldbname;"; 
var con = new SqlConnection(connectionString);
//And then
 con.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

 con.Open();
  //Do sql tasks
 con.Close();
Arulvel
  • 69
  • 3