vstest with workload identity federation

Question

I'm using DevOps Pipelines to run my sql unit tests. This works well with the vstest task and a service connection with ActiveDirectoryServicePrincipal.

But our platforming guy changed the authentification mode in the service connection to workload identity federation.

Since this change, it is not longer possible to connect to our azure cloud database. If I change the authentification in the app.config of the test app, the error is: Microsoft.Data.SqlClient.SqlException: Microsoft.Data.SqlClient.SqlException: WorkloadIdentityCredential authentication unavailable. The workload options are not fully configured.

I tried different settings in the connection string in the app.config. I also tried to replace the vstest task to an azureCLI task where I call the test via vstest.console.exe. Here it is possible to generate an accesstoken but I don't know how I pass this to the tests. Till now I can't find a solution for my connection problem.

Does anyone have the same setup or knows how to solve this?

Added: The yaml of the old VSTEST task:

steps:
- task: VSTest@3
  displayName: 'run DB Unittest'
  inputs:
    testAssemblyVer2: |
     **\*Tests.dll
     !**\*TestAdapter.dll
     !**\obj\**
    codeCoverageEnabled: true

Added: The yaml of the new AzureCLI task:

steps:
- task: AzureCLI@2
  displayName: 'Azure CLI '
  inputs:
    azureSubscription: 'ServiceConnection'
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
     $Result      = "$(Agent.TempDirectory)\TestResults" 
     $TestDLL     = ".\...\Tests.dll"
     $TestAdapter = ".\...\TestAdapter.dll"
     $TetPlatform = "...\vstest.console.exe"
     
     &$TetPlatform $TestDLL /TestAdapterPath:$TestAdapter /ResultsDirectory:$Result
    addSpnToEnvironment: true
  enabled: false

Added: The connection part of the OLD app.config:

<configSections>
  <section name="SqlUnitTesting" type="Microsoft.Data.Tools.Schema.Sql.UnitTesting.Configuration.SqlUnitTestingSection, Microsoft.Data.Tools.Schema.Sql.UnitTesting, Version=19.0.0.0, Culture=neutral, PublicKeyToken=***" />
</configSections>
    <SqlUnitTesting>
<DataGeneration ClearDatabase="true" />
<ExecutionContext Provider="Microsoft.Data.SqlClient"
                  ConnectionString="Authentication=ActiveDirectoryServicePrincipal;
                                    Persist Security Info=False;
                                    Pooling=False;
                                    Multiple Active Result Sets=False;
                                    Connect Timeout=60;
                                    Encrypt=True;
                                    Trust Server Certificate=False;
                                    Data Source=#{UTConnectionDataSource}#;
                                    Initial Catalog=#{UTConnectionInitialCatalog}#;
                                    User ID=#{UTConnectionUserID}#;
                                    Password=#{UTConnectionPassword}#;"
                  CommandTimeout="30" />
<PrivilegedContext Provider="Microsoft.Data.SqlClient"
                  **same as above**
 />
    </SqlUnitTesting>
<system.data>
  <DbProviderFactories>
    <add name="Microsoft SqlClient Data Provider" invariant="Microsoft.Data.SqlClient" description="Microsoft SqlClient Data Provider for SqlServer" type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
  </DbProviderFactories>
</system.data>

Connection string of the NEW app.config

ConnectionString="Authentication=Active Directory Workload Identity;
                                            Persist Security Info=False;
                                            Pooling=False;
                                            Multiple Active Result Sets=False;
                                            Connect Timeout=60;
                                            Encrypt=True;
                                            Trust Server Certificate=False;
                                            Server=#{UTConnectionDataSource}#;
                                            Database=#{UTConnectionInitialCatalog}#;
                                            User ID=#{UTConnectionUserID}#;
        "
                          CommandTimeout="30" />

the error message when I use the OLD connection string with workload identity: Initialization method ModulverbinderTests.CreateDMC.TestInitialize threw exception. Microsoft.Data.SqlClient.SqlException: Microsoft.Data.SqlClient.SqlException: ClientSecretCredential authentication failed: Application with identifier '' was not found in the directory ''. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant.

Answer 1

According to your app.config, you are using ActiveDirectoryServicePrincipal as the authentication.

But according to Connect to Azure SQL with Microsoft Entra authentication and SqlClient, you should use Active Directory Workload Identity.

Available starting in version 5.2, like with managed identities, workload identity authentication mode uses the value of the User Id parameter in the connection string for its Client Id if specified. But unlike managed identity, WorkloadIdentityCredentialOptions defaults its value from environment variables: AZURE_TENANT_ID, AZURE_CLIENT_ID, and AZURE_FEDERATED_TOKEN_FILE. However, only the Client Id may be overridden by the connection string.

The following example demonstrates Active Directory Workload Identity authentication with a user-assigned managed identity with Microsoft.Data.SqlClient v5.2 onwards.

// Use your own values for Server, Database, and User Id.
// With Microsoft.Data.SqlClient v5.2+
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Workload Identity; Encrypt=True; User Id=ClientIdOfManagedIdentity; Database=testdb";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
    conn.Open();
}