Azure SQL: Microsoft.Data.SqlClient - Invalid value for key 'authentication'

[techsaau]

Description

I'm trying to connect to Azure SQL Database with "Active Directory Service Principal", It failed with below error:

But Same code works fine with "Active Directory Password" mode.

It looks like, by default it only uses System.Data.SqlClient and we are not able to use Microsoft.Data.SqlClient

Steps to reproduce

Try to do auth with AAD using SPN:

<PrivilegedContext Provider="Microsoft.Data.SqlClient" ConnectionString="Server=.database.windows.net;Authentication=Active Directory Service Principal; Encrypt=True; Database=-dbname; User Id={appID}; Password={secret}" CommandTimeout="3000" />

Expected behavior

Authentication should work and it is working in local Visual Studio.

Actual behavior

Authentication should be successful with SPN AppID & Secret

Diagnostic logs

Error Message:
   Assembly Initialization method XXXX.UnitTest.SqlDatabaseSetup.InitializeAssembly threw exception. System.ArgumentException: System.ArgumentException: Invalid value for key 'authentication'.. Aborting test execution.
  Stack Trace:
      at System.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value)
   at System.Data.SqlClient.SqlConnectionStringBuilder.ConvertToAuthenticationType(String keyword, Object value)
   at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)
   at Microsoft.Data.Tools.Schema.Sql.UnitTesting.Common.SqlConnectionStringSecurer.RestoreConnectionString(String partialConnection)
   at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.DecryptConnectionString(String connectionString)
   at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.SetupConnectionString(String connStr)
   at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.DeployDatabaseProject()
   at XXXX.UnitTest.SqlDatabaseSetup.InitializeAssembly(TestContext ctx) in C:\Data\Agent\_work\316\s\XXXX.UnitTest\SqlDatabaseSetup.cs:line 23

Environment

Azure DevOps Pipeline VSTest@3
vsTestVersion=latest

[nohwnd]

Do you have an example of how you are connecting to the database? In the stack trace I see you are running AssemblyInitialize method, in which you can do anything. Are you using some feature provided by VSTest to do the authentication?

[techsaau]

I'm doing default code for Unit Testing and in which it has default code for the same:

[AssemblyInitialize()]
public static void InitializeAssembly(TestContext ctx)
{
    // Setup the test database based on setting in the
    // configuration file
    SqlDatabaseTestClass.TestService.DeployDatabaseProject();
    SqlDatabaseTestClass.TestService.GenerateData();
}

And below is the app.config code block which I use to connect to DB:

<PrivilegedContext Provider="Microsoft.Data.SqlClient" ConnectionString="Server=.database.windows.net;Authentication=Active Directory Service Principal; Encrypt=True; Database=-dbname; User Id={appID}; Password={secret}" CommandTimeout="3000" />

Same code is working in my local Visual Studio. I can authenticate against AAD using SPN.

[nohwnd]

I am trying to find who owns that library.

[MichelZ]

https://github.com/dotnet/SqlClient