6

I am trying to give an application access to a database. One of the steps require that a script that create a user must be run on the database. I am doing this through the pipeline with azureSqlAzureDacpacDeployment@1 task.

Secure Azure SQL Database connection from App Service using a managed identity

- task: SqlAzureDacpacDeployment@1
      inputs:
        azureSubscription: 'xxxxxxxx (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)'
        AuthenticationType: 'aadAuthenticationIntegrated'
        ServerName: '$(SqlServerName)'
        DatabaseName: '$(SqlDatabaseName)'
        deployType: 'InlineSqlTask'
        SqlInline: |
          CREATE USER [$(AppName)] FROM EXTERNAL PROVIDER;
          ALTER ROLE db_datareader ADD MEMBER [$(AppName)];
          ALTER ROLE db_datawriter ADD MEMBER [$(AppName)];
          GO
        InlineAdditionalArguments: '-v $(ApiAppName)'
        IpDetectionMethod: 'AutoDetect'

ApiAppName = 'AppName=MyApplication'

##[error]The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. 
Please use the 'var=value' format for defining a new variable.Check out how to troubleshoot 
failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2cool4school
  • 239
  • 4
  • 11
  • Can you use something like Powershell to execute ad hoc commands against the SQL server after this task completes? – Rob Reagan Aug 10 '20 at 18:19
  • The task works fine parameterize the appname portion is the issue. If I ran the invoke-sqlcmd from powershell I will still face the challenge of parameterization. – 2cool4school Aug 10 '20 at 18:32
  • One more idea: does the error message change if you use $(AppName) instead of [$(AppName)]? I did consult the documentation, and $(variable) macros should be expanded before the task is run, which is what you want. – Rob Reagan Aug 10 '20 at 18:42
  • Got a new error 'AppName' scripting variable not definedCheck out how to troubleshoot failures at – 2cool4school Aug 10 '20 at 19:17

1 Answers1

-1

It seems you are trying to define variables in SQL Script. You may try DECLARE statement and SET statement as the following link states:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-ver15

-- Declare two variables.
DECLARE @AppName nvarchar(50);

-- Set their values.
SET @AppName = 'MyApplication';
Cece Dong - MSFT
  • 29,631
  • 1
  • 24
  • 39
  • how am I suppose to get the my 'MyApplication' into the sql script." i am trying to pass a variable from my pipeline to the sql script. the task use invoke-sqlcmd – 2cool4school Aug 12 '20 at 17:29
  • You may try to add a script step before task `SqlAzureDacpacDeployment@1`, and set a variable with `##vso[task.setvariable]value`: https://learn.microsoft.com/en-us/azure/devops/pipelines/scripts/logging-commands?view=azure-devops&tabs=bash#setvariable-initialize-or-modify-the-value-of-a-variable. – Cece Dong - MSFT Aug 13 '20 at 03:24
  • the answer you are providing have nothing to do with the question. I am trying to pass a variables from the pipeline to the inline sql script. the error message I am getting state that something is wrong with the format I am using. this is format 'AppName=MyApplication' and this the format the error message advice me to use 'var=value'. does that make it clear because the answer you provided have nothing to do with the question. – 2cool4school Aug 13 '20 at 14:29
  • It seemed I misunderstood your query. Please try the following format to define the variable `$ApiAppName = "AppName='MyApplication'"`, and use `$(AppName)` in the sql. https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps – Cece Dong - MSFT Aug 14 '20 at 08:38