Using User Assigned Managed Identity with Federated Credentials in Azure DevOps Pipeline for SQL Server Management (Microsfot Hosted Agent)

Hello,

I am currently using an Azure DevOps pipeline that employs a Service Principal Service Connection to manage my Ressources including SQL Server, specifically for user creation within the SQL Database. This setup works well, but I am looking to eliminate the use of the service principal with its associated secret and transition to a User Assigned Managed Identity (UAMI) using Federated Credentials.

Current Setup:

• Azure DevOps Pipeline: Utilizing a Service Principal Service Connection for managing SQL Server and SQL Database user creation.
• Objective: Transition from Service Principal with secret to a more secure UAMI with Federated Credentials.

Actions Taken:

1. Created UAMI: I have successfully created a User Assigned Managed Identity in Azure.
2. Role Assignments: The UAMI has been assigned the necessary roles and permissions for various resources, which are working fine. The UAMI has also been assigned as Entra ID Admin on SQL Server, I also added Contributor role.
3. Issue: The SQL Database connection fails when using UAMI in my DevOps Pipeline (Terrafrom and CLI). ERROR: Failed to connect to MSI, check your managed service identity id or Timeout after 30 sec.
4. I also enabled the Agent Ip in the Firewall to avoid network issues.

Specific Challenges:

• Despite the UAMI working correctly for other resources, the connection to the SQL Database fails.
• I am unsure of the exact configuration steps required to replace the Service Principal with UAMI in the Azure DevOps pipeline, especially for SQL Database management