Azure SQL Database Deployment task
Azure DevOps Services
Use this task to deploy to Azure SQL DB using a DACPAC or run scripts using SQLCMD.
Important
This task is supported only in a Windows environment. If you are trying to use Azure Active Directory (Azure AD) integrated authentication, you must create a private agent. Azure AD integrated authentication is not supported for hosted agents.
YAML snippet
# Azure SQL Database deployment
# Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
- task: SqlAzureDacpacDeployment@1
inputs:
#azureConnectionType: 'ConnectedServiceNameARM' # Optional. Options: connectedServiceName, connectedServiceNameARM
#azureClassicSubscription: # Required when azureConnectionType == ConnectedServiceName
#azureSubscription: # Required when azureConnectionType == ConnectedServiceNameARM
#authenticationType: 'server' # Options: server, aadAuthenticationPassword, aadAuthenticationIntegrated, connectionString, servicePrincipal
#serverName: # Required when authenticationType == Server || AuthenticationType == AadAuthenticationPassword || AuthenticationType == AadAuthenticationIntegrated || AuthenticationType == servicePrincipal
#databaseName: # Required when authenticationType == Server || AuthenticationType == AadAuthenticationPassword || AuthenticationType == AadAuthenticationIntegrated || AuthenticationType == servicePrincipal
#sqlUsername: # Required when authenticationType == Server
#sqlPassword: # Required when authenticationType == Server
#aadSqlUsername: # Required when authenticationType == AadAuthenticationPassword
#aadSqlPassword: # Required when authenticationType == AadAuthenticationPassword
#connectionString: # Required when authenticationType == ConnectionString
#deployType: 'DacpacTask' # Options: dacpacTask, sqlTask, inlineSqlTask
#deploymentAction: 'Publish' # Required when deployType == DacpacTask. Options: publish, extract, export, import, script, driftReport, deployReport
#dacpacFile: # Required when deploymentAction == Publish || DeploymentAction == Script || DeploymentAction == DeployReport
#bacpacFile: # Required when deploymentAction == Import
#sqlFile: # Required when deployType == SqlTask
#sqlInline: # Required when deployType == InlineSqlTask
#publishProfile: # Optional
#additionalArguments: # Optional
#sqlAdditionalArguments: # Optional
#inlineAdditionalArguments: # Optional
#ipDetectionMethod: 'AutoDetect' # Options: autoDetect, iPAddressRange
#startIpAddress: # Required when ipDetectionMethod == IPAddressRange
#endIpAddress: # Required when ipDetectionMethod == IPAddressRange
#deleteFirewallRule: true # Optional
Arguments
Argument | Description |
---|---|
ConnectedServiceNameSelector Azure Connection Type |
(Optional) Argument alias: azureConnectionType Default value: ConnectedServiceNameARM |
ConnectedServiceName Azure Classic Subscription |
(Required) Target Azure Classic subscription for deploying SQL files Argument alias: azureClassicSubscription |
ConnectedServiceNameARM Azure Subscription |
(Required) Target Azure Resource Manager subscription for deploying SQL files Argument alias: azureSubscription |
AuthenticationType Authentication Type |
(Required) Type of database authentication, can be SQL Server Authentication, Active Directory - Integrated, Active Directory - Password, Connection String, or Service Principal. Integrated authentication means that the agent will access the database using its current Active Directory account context. Default value: server |
ServerName Azure SQL Server |
(Required except when Authentication Type is Connection String) Azure SQL Server name, like Fabrikam.database.windows.net,1433 or Fabrikam.database.windows.net. |
DatabaseName Database |
(Required) Name of the Azure SQL Database, where the files will be deployed. |
SqlUsername Login |
(Required when Authentication Type is SQL Server Authentication or Active Directory - Password) Specify the Azure SQL Server administrator login or Active Directory user name. |
SqlPassword Password |
(Required when Authentication Type is SQL Server Authentication or Active Directory - Password) Password for the Azure SQL Server administrator or Active Directory user. It can accept variables defined in build/release pipelines as '$(passwordVariable)'.You may mark the variable type as 'secret' to secure it. |
ConnectionString Connection String |
(Required when Authentication Type is Connection String) The connection string, including authentication information, for the Azure SQL Server. |
TaskNameSelector Deploy Type |
(Optional) Specify the type of artifact, SQL DACPAC file, SQL Script file, or Inline SQL Script. Argument alias: deployType Default value: DacpacTask |
DeploymentAction Action |
(Required) Choose one of the SQL Actions from the list. For more details refer link Default value: Publish |
DacpacFile DACPAC File |
(Required when Deploy Type is SQL DACPAC file) Location of the DACPAC file on the automation agent or on a UNC path accessible to the automation agent like, \BudgetIT\Web\Deploy\FabrikamDB.dacpac . Predefined system variables like, $(agent.releaseDirectory) can also be used here. |
BacpacFile BACPAC File |
(Required) Location of the BACPAC file on the automation agent or on a UNC path accessible to the automation agent like \BudgetIT\Web\Deploy\FabrikamDB.bacpac . Predefined system variables like, $(agent.releaseDirectory) can also be used here |
SqlFile SQL Script |
(Required when Deploy Type is SQL Script file) Location of the SQL script file on the automation agent or on a UNC path accessible to the automation agent like, \BudgetIT\Web\Deploy\FabrikamDB.sql . Predefined system variables like, $(agent.releaseDirectory) can also be used here. |
SqlInline Inline SQL Script |
(Required when Deploy Type is Inline SQL Script) Enter the SQL script to execute on the Database selected above. |
PublishProfile Publish Profile |
(Optional) Publish profile provides fine-grained control over Azure SQL Database creation or upgrades. Specify the path to the publish profile XML file on the agent machine or a UNC share. If the publish profile contains secrets like credentials, upload it to the secure files library where it is securely stored with encryption. Then use the Download secure file task at the start of your pipeline to download it to the agent machine when the pipeline runs and delete it when the pipeline is complete. Predefined system variables like $(agent.buildDirectory) or $(agent.releaseDirectory) can also be used in this field. |
AdditionalArguments Additional SqlPackage.exe Arguments |
(Optional) Additional SqlPackage.exe arguments that will be applied when deploying the Azure SQL Database, in case DACPAC option is selected like, /p:IgnoreAnsiNulls=True /p:IgnoreComments=True . These arguments will override the settings in the publish profile XML file (if provided). |
SqlAdditionalArguments Additional Invoke-Sqlcmd Arguments |
(Optional) Additional Invoke-Sqlcmd arguments that will be applied when executing the given SQL query on the Azure SQL Database like, -ConnectionTimeout 100 -OutputSqlErrors |
InlineAdditionalArguments Additional Invoke-Sqlcmd Arguments |
(Optional) Additional Invoke-Sqlcmd arguments that will be applied when executing the given SQL query on the Azure SQL Database like, -ConnectionTimeout 100 -OutputSqlErrors |
IpDetectionMethod Specify Firewall Rules Using |
(Required) For the task to run, the IP Address of the automation agent has to be added to the 'Allowed IP Addresses' in the Azure SQL Server's Firewall. Select auto-detect to automatically add firewall exception for range of possible IP Address of automation agent or specify the range explicitly. Default value: AutoDetect |
StartIpAddress Start IP Address |
(Required) The starting IP Address of the automation agent machine pool like 196.21.30.50. |
EndIpAddress End IP Address |
(Required) The ending IP Address of the automation agent machine pool like 196.21.30.65. |
DeleteFirewallRule Delete Rule After Task Ends |
(Optional) If selected, then after the task ends, the IP Addresses specified here are deleted from the 'Allowed IP Addresses' list of the Azure SQL Server's Firewall. |
Open source
This task is open source on GitHub. Feedback and contributions are welcome.
Feedback
Submit and view feedback for