I have an Azure Sql server and a specific database where I want to create a contained user representing my Azure Function App.
I have already done the following:
- Enabled the system-assigned managed identity (SMI) of both the Azure Sql Server and the Azure Function App resources.
- Created an AAD user group and assign it the AAD role Directory Readers and added as a member the service principal of the managed identity of my Azure Sql Server resource.
- Created an Azure release pipeline to test that I can run a SQL script to create a db contained user for my Azure Function App. The Azure Pipeline runs with a service connection which has been associated with another Entra ID service principal with access to my resource groups. Inside the Azure Pipeline, I used an Azure SQL Database deployment task setup as follows:
| Option | Value |
|---|---|
| Azure Service Connection Type | Azure Resource Manager |
| Azure Subscription | I selected a service connection associated with an Entra ID service principal for deploying resources to my selected resource groups |
| SQL Database | |
| Authentication Type | Service Principal |
| Azure SQL Server | ******.database.windows.net |
| Database | ****** |
| Deployment Package | |
| Deploy type | Inline SQL Script |
| Inline SQL Script | CREATE USER [my-func-dev-weu] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo; GO;ALTER ROLE [db_owner] ADD MEMBER [my-func-dev-weu];GO |
| Firewall | |
| Specify Firewall Rules Using | AutoDetect |
| Delete Rule After Task Ends | Checked |
When I execute my Azure Pipeline I get back the following error:
Login failed for user '<token-identified principal>'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting
Unfortunately, the troubleshooting guide was not much of help for my issue.
I am a bit confused because although I created the system-assigned managed identity of my Azure Sql server, I have no way of using it (the service principal) to execute my Azure SQL database deployment task. I was under the impression that the service principal authentication will check that the Azure Sql server’s SMI is enabled and it will carry on creating the new contained user.
Next, I tried to manually add the service principal associated with the service connection I use to deploy my resources to Azure and it worked (I had already added the service principal of the service connection to my AAD user group with Directory Readers role), but I don’t like this solution, because it means I shall always have to execute the manual task to add the azure pipeline’s service principal as a contained user to every Azure Sql database I need to be able to create contained users via Azure release Pipeline.
How can I make it work by only using the SMI of the Azure SQL Server when executing the db script within an Azure release Pipeline ?
Panayotis Tzedakis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.