Related: 1|2
Problem Statement :
Is it possible to publish DACPAC on Azure SQL Database or Azure SQL Data Warehouse via Service Principal rather than SQL Account for better Security Standards.
Prerequisites :
- Service Principal
How to Create a Service Principal
2. Azure SQL Server with Active Directory enabled
Solution :
- Provide the Service Principal “Reader” role assignment via Access Control (IAM) on the Azure SQL server.
2. Once the Reader role is assigned , Create a Azure Service Connection of type “Azure Resource Manager using Service Principal” in the Azure Devops Project Settings.
How to Set Up a Service Connection
In case if the Service Principal is initially not given Reader role access on SQL Server, one would get the below error while creation of the Service Connection :
Failed to query service connection API: ‘https://management.azure.com/subscriptions/<<SubscriptionId>>?api-version=2016-06-01’. Status Code: ‘Forbidden’, Response from server: ‘{“error”:{“code”:”AuthorizationFailed”,”message”:”The client ‘<>’ with object id ‘<>’ does not have authorization to perform action ‘Microsoft.Resources/subscriptions/read’ over scope ‘/subscriptions/<>’ or the scope is invalid. If access was recently granted, please refresh your credentials.”}}’
3. Login into the Azure SQL Server via an AD admin account and execute the below set of queries in the Master database.
CREATE USER [<>] FROM EXTERNAL PROVIDER ;
exec sp_addrolemember ‘dbmanager’, ‘<>’ ;
exec sp_addrolemember ‘loginmanager’, ‘<>’ ;
Note : The above queries can be executed only when logged in via an AD admin account and not via any SQL account logged in session.
4. Create a Release Pipeline in Azure Pipelines and add the Azure SQL Database deployment task with the below configurations:
YAML :
steps:
- task: SqlAzureDacpacDeployment@1
displayName: ‘Deploy database’
inputs:
azureSubscription: ‘$(ServiceConnection)’
AuthenticationType: servicePrincipal
ServerName: ‘$(Server)’
DatabaseName: ‘$(DatabaseName)’
DacpacFile: ‘$(DacpacPath)’
where $(ServiceConnection) is the variable that represents the name of the Service Connection created as a part of step 2.
There are multiple Authentication types supported for Azure SQL Database deployment but Service Principal is a much secure way as compared to some other authentication types.
DataSharkX 
Greetings! Very helpful advice within this article! It is the little changes that produce the biggest changes. Many thanks for sharing!
LikeLike
Wonderful article! We are linking to this great article on our website. Keep up the good writing.
LikeLike
Dude these articles have been really helpful to me. They really helped me out.
LikeLike
Thanks for your post, it helped me a lot. It helped me in my situation and hopefully it can help others too.
LikeLike
Thank you for your post. I really enjoyed reading it, especially because it addressed my issue. It helped me a lot and I hope it will help others too.
LikeLike
@Nandan, I tried implementing this solution.Followed the steps except the YAML part. Where I have to add the yaml code ?
LikeLike
The YAML part is part of azure devops release pipeline:
https://learn.microsoft.com/en-us/azure/devops/pipelines/get-started/pipelines-get-started?view=azure-devops#define-pipelines-using-yaml-syntax
https://learn.microsoft.com/en-us/azure/devops/pipelines/get-started/yaml-pipeline-editor?view=azure-devops
LikeLike
Could you provide an explanation of what the 2 queries in step 3 does?
CREATE USER [] FROM EXTERNAL PROVIDER ;
exec sp_addrolemember ‘dbmanager’, ‘’ ;
exec sp_addrolemember ‘loginmanager’, ‘’ ;
———————–
Also what is the difference between this and the solution in part 2?
Can we run the queries in part 2 instead when using an ARM Service Connection?
CREATE USER [] FROM EXTERNAL PROVIDER ;
exec sp_addrolemember ‘dbmanager’, ‘’ ;
exec sp_addrolemember ‘loginmanager’, ‘’ ;
LikeLike