Search code examples
azureazure-devopsazure-sql-database

How to access Azure SQL server using MSI in Azure DevOps pipeline?

I have a Azure SQL server with a database. Azure SQL server has a Managed Service Identity. I want to insert some records into the database via a Azure Devops pipeline.

What are the steps to be followed?

Solution

  • How to access Azure SQL server using MSI in Azure DevOps pipeline?

    Based on your requirement, I suggest that you can use the Pipeline task: SqlAzureDacpacDeployment@1 - Azure SQL Database deployment v1 task.

    Here are the steps:

    Step1: You can use the Managed Service Identity to create an Azure Resource Manger Service Connection in Project Settings -> Service Connections -> Find Azure Resource Manager -> Managed identity

    For example:

    enter image description here

    Step2: You can use the Managed Service Identity ARM service connection in the Azure SQL Database deployment task to connect to the Azure SQL Database.

    At the same time, you also need to fill in the fields such as sql authentication method and sql script/file/dacpac file to complete the deployment.

    For example:

    - task: SqlAzureDacpacDeployment@1
  displayName: Execute Azure SQL : DacpacTask
  inputs:
    azureSubscription: '<Azure service connection>'
    ServerName: '<Database server name>'
    DatabaseName: '<Database name>'
    SqlUsername: '<SQL user name>'
    SqlPassword: '<SQL user password>'
    deployType: SqlTask
    SqlFile: sqlfile

    For more detailed info, you can refer to this doc: Azure SQL database deployment