Unable to connect to SQL Server as an admin using Service Principal from a private DevOps build agent

Morten Nilsen 1 Reputation point
2022-07-29T15:30:57.323+00:00

I have set up an SQL Server in Azure, and created an AAD group which has been assigned as the sql admin for the server.
The SQL Server has managed identity enabled, and it has been granted Directory Reader access in AzureAD

I have also set up an Azure Resource Manager connection in the DevOps project.
Application ID 68c2...-...-...-...-... Object ID d6f7...-...-...-...-... Directory ID 9794...-...-...-...-...
And associated with an enterprise application with Object ID 5efb...-...-...-...-...

The group includes my user from an external AAD tenant, and the DevOps build agent's service principal, which was created by adding a service connection to AzureRM in DevOps project settings.

When I add a build pipeline step that executes az account show --query user.name I get the GUID 68c2..., which matches the application id as expected.

But when I try to connect using SqlClient, I get an exception:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '\

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Morten Nilsen 1 Reputation point
    2022-12-13T18:39:40.513+00:00

    After being in contact with Microsoft Support, the solution turned out to be simple:

    Follow the steps outlined in this article: https://techcommunity.microsoft.com/t5/azure-database-support-blog/using-managed-service-identity-msi-to-authenticate-on-azure-sql/ba-p/1288248

    I made an incorrect assumption that the service connection I set up in Azure DevOps would be used when the agent executed the build and it would automatically just work.

    0 comments