"The 'Query' and the 'InputFile' options are mutually exclusive" error running SqlAzureDacpacDeployment with sql file

Question

I'm trying to apply an Entity Framework Core migration script on my Azure SQL database. I get my access token and then try to run the script.

- task: AzureCLI@2
  inputs:
    azureSubscription: '<service connection name>'
    scriptType: 'ps'
    scriptLocation: 'inlineScript'
    inlineScript: |
      $token= & az account get-access-token --resource=https://database.windows.net --query accessToken
      Write-Output("##vso[task.setvariable variable=sqlToken;]$token")

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: '<service connection name>'
    AuthenticationType: 'connectionString'
    ConnectionString: 'Server=tcp:<servername>.database.windows.net,1433;Initial Catalog=hrchangemanagement;Connection Timeout=30;'
    deployType: 'SqlTask'
    SqlFile: $(build.artifactStagingDirectory)\script.sql
    sqlAdditionalArguments: /AccessToken:$(sqlToken)
    IpDetectionMethod: 'AutoDetect'

In the output of the SqlAzureDacpacDeployment task, I get this:

Starting: SqlAzureDacpacDeployment
==============================================================================
Task         : Azure SQL Database deployment
Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
Version      : 1.234.0
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment
==============================================================================
Added TLS 1.2 in session.
Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp94B2.tmp
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp94B2.tmp" 
Sql file: D:\a\1\a\script.sql
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "D:\a\1\a\script.sql" /AccessToken:"<access token value>"
##[error]The 'Query' and the 'InputFile' options are mutually exclusive.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
Finishing: SqlAzureDacpacDeployment

In the output I see Invoke-Sqlcmd ran twice. I don't know if that's expected or not?

I've read through the MS article on SqlAzureDacpacDeployment at least three times: https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/reference/sql-azure-dacpac-deployment-v1?view=azure-pipelines

From discussion boards, it appears this error is more a syntax error than anything else.

• I checked for extra single quotes inside double
• I checked for extra spaces
• I tried using AdditionalArguments instead of sqlAdditionalArguments and that produced a login error Login failed for user '' which tells me it didn't pick up the access token.
• One MS discussion said there may be values cached after editing the task so it said to delete the task and rerun the pipeline. I recreated the tasks and then ran again with no difference.

Answer 1

In the output I see Invoke-Sqlcmd ran twice. I don't know if that's expected or not?

It's correct behavior. The SqlAzureDacpacDeployment task first creates a temporary SQL file and executes it. This is done to set up the SQLCMD variables. Then it executes the actual SQL file you provided.

##[error]The 'Query' and the 'InputFile' options are mutually exclusive.

The error means you can’t use both at the same time. You have SqlFile(-inputfile) specified and /AccessToken:$(sqlToken) in sqlAdditionalArguments(-query). If you’re passing a query via -Query parameter and an input file via -InputFile parameter at the same time, you’ll need to choose one.

As per your steps, you're using service principal from service connection to run the sql script. It will fail as the service principal doesn't grant proper permission on the database.

To grant the permission for SP:

1. you need to connect the azure SQL with Entra user:

2. Create user and assign role, the SPN is the existing service principal name which comes from service connection.

CREATE USER [SPN] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [SPN];
ALTER ROLE db_datawriter ADD MEMBER [SPN];
ALTER ROLE db_ddladmin ADD MEMBER [SPN];

3. You can change servicePrincipal for AuthenticationType of the task.

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'ARMConn4'
    AuthenticationType: 'servicePrincipal'
    ServerName: 'mysqlserver.database.windows.net'
    DatabaseName: 'db1'
    deployType: 'SqlTask'
    SqlFile: '**\script.sql'
    IpDetectionMethod: 'AutoDetect'

My pipeline result:

Created the new table from the script:

you can also check the link for your reference.