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

[techsaau]

I am trying to find who owns that library.

is there any update on this? Could you find the fix or owner, who can fix this.

[nohwnd]

@MichelZ do you see what could be the problem? Does the setup look correct? I am not sure how or if this is caused by running inside of tests.

@techsaau I am still a bit confused where this is failing and where it is not failing? Are you able to successfully run this in tests in local, but it fails on CI? or it always fails in tests, and always succeeds in non-test project (e.g. console) that does the same thing?

[techsaau]

@nohwnd It failed on CI only when I use vstest. I was able to do successful tests in my local.

[nohwnd]

In that case this points rather to an issue with environment / authentication than vstest itself.

[techsaau]

In that case this points rather to an issue with environment / authentication than vstest itself.

it is pity that I did not see your last comment in this. But my main question would be, Did you test with "Active Directory Service Principal" using vstest and did it work?

[nohwnd]

I did not test, the stack trace shows that this happens in sql connection code, and not inside of vstest, and because it works locally it is most likely a problem caused by setup / environment. I am in a different environment, so even if I reproduce I have no idea if I am seeing the same problem.