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

[techsaau]

Describe the bug

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.

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

To reproduce

Below is the app.config file:

<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

A clear and concise description of what you expected to happen.

Further technical details

Microsoft.Data.SqlClient version: 5.2.2 .NET target: 4.7.2 SQL Server version: Azure SQL Server

Additional context Add any other context about the problem here.

[ErikEJ]

The call stack is from System.Data.SqlClient so this is expected.

[techsaau]

I removed System.Data.SqlClient from my project, not sure why it is still pointing to this.

I'm trying to Unit Testing here. Can someone help me out

[cheenamalhotra]

Does your using include 'System.Data.SqlClient' namespace? Have you tried converting them to 'Microsoft.Data.SqlClient' instead?

[techsaau]

@cheenamalhotra yes, I have replaced all the references from 'System.Data.SqlClient' to 'Microsoft.Data.SqlClient'. That's why it's bothering me.

But in project, primarily I use it in app.config only:

[cheenamalhotra]

The error you're receiving is due to System.Data.SqlClient being in use, so you need to figure out why is that happening.

[edwardneal]

To expand on the point above: the stack trace indicates that it's being called directly by the SqlDatabaseTestService class, which is part of the SQL Server unit testing functionality in MSBuild/Visual Studio. Perhaps this functionality still uses System.Data.SqlClient by default? If so, using the second overload of DeployDatabaseProject and specifying Microsoft.Data.SqlClient's provider name might help.