Programmatically add domain user to Azure SQL with Azure DevOps pipeline

Question

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

AFAIK I can't connect with a domain user to the database from the pipeline
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.

Nandan · Accepted Answer · 2021-01-06 16:26:07Z

2

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 :)

answered Jan 6, 2021 at 16:26

Nandan

5,2252 gold badges12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PedroC88 Over a year ago

I'll take a look at the Azure SQL database deployment task. I already use it to deploy dacpacs in other pipelines but didn't know I could use it to run queries. Can you expand on the authentication as service principal? We updated about 3-4 months ago to the "new" method but AFAIK, the task can't impersonate the account under which the service principal is configured.

PedroC88 Over a year ago

Well... this was a very good solution! I couldn't get it working with Service Principal, kept getting an error about the account not being able to read AD, even after I added the Azure App to the Domain Readers. I then used Active Directory - Password with a service account we already use in all our pipelines, and it worked like a charm after adding the service account to the Domain Readers

Collectives™ on Stack Overflow

Programmatically add domain user to Azure SQL with Azure DevOps pipeline

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related