CODEWITHSUNDEEP
azure-devopsazure-sql-databaseazure-sql-serverinvoke-sqlcmd
Nayden Van
Nayden Van

Reputation: 1569

Azure DevOps pipeline run sql script against Database AAD

I am trying to wrap up my mind around this process.

I have a SQL Server in azure. This server has a Azure Active Directory Admin enable using an azure group to authenticate using MFA. Further more, in the same blade, I have enabled the Support only Azure Active Directory Authentication for this server.

Everything works just fine, and I am able to connect to my server by using MFA as I am part of the Active directory.

Now, I am a disaster and most of the time I forget to update my database schema, and when I deploy some tests, everything explodes.

I have been looking around for an automation process to authenticate with azure DevOps and run a sql script every time my release pipeline is triggered.

I came across this documentation

https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

Which seems to be just what a need as it has the --InputFile.

but I am having some problems to understand how I can authenticate my release pipeline and perform those changes using an AAD to access the DB without having to expose username and password.

If anyone can help me to understand what its the best approach here iw ill be grateful.

And please if my question is not 100% clear, just let me know and I will explain better

Upvotes: 0

Views: 3200

Answers (1)

Mohit Ganorkar
Mohit Ganorkar

Reputation: 2069

  • An alternative would be to write a script which will do all the sql queries instead of doing it in pipeline. This way we can using azure ad to authenticate.

  • Register the sql service to the azure ad then we can get tokens to authenticate the sql queries.

  • finally run the script in pipeline using command line task

Reference:

how to run script

authenticate using azure ad

Upvotes: 1

Related Questions