Programmatically add domain user to Azure SQL with Azure DevOps pipeline

1
votes

TLDR;

How do I add AD users to an Azure database created in a DevOps pipeline?

Our DBA has a process to create daily bacpacs from production and store them in an azure blob container, I then provided the developers with a DevOps pipeline that restores a specified bacpac into the development server. The issue is that the developers can't connect to these databases using their domain account. I don't know how to give them access because

  1. AFAIK I can't connect with a domain user to the database from the pipeline
  2. Azure requires that the connected user is an AD user to be able to create other AD users

I could work around it by creating an sql user and grant that user permissions in the pipeline, however the company is actually moving away from sql users and relying more on AD security and MFA, so this isn't really a solution for me.

1
azureazure-devopsactive-directoryazure-sql-database

1 Answers

2
votes

For one to add domin account to the database, one must be logged in to the database via a domain account that has adminstrative privileges. So there is a Azure SQL database deployment task in Azure devops wherein you have the property to execute SQL queries. So you can use that task to automate the user access through SQL tasks by connecting through the AD account.

Note: Recently a new authentication has been added of service principal

Hope this is what you are expecting :)