Use PowerShell module in ARM deployment script

Question

I have an ARM template in which I wish to execute a powershell script. In this question it is hinted that it should be possible to install powershell modules in the script.

I am unable to import the module I want to use.

This is the ARM template deploymentScript:

        {
        "type": "Microsoft.Resources/deploymentScripts",
        "apiVersion": "2020-10-01",
        "name": "[concat('createServicePrincipalLogin-', parameters('databaseSettings').databases[copyIndex()].name)]",
        "location": "[resourceGroup().location]",
        "dependsOn": [
            "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseSettings').databases[copyIndex()].name)]",
            "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
        ],
        "kind": "AzurePowerShell",
        "properties": {
          "azPowerShellVersion": "7.4",
          "timeout": "PT30M",
          "arguments": "[format(' -serverName {0} -databaseName {1} -userName {2} -password {3}  -appId {4} ', parameters('serverName'), parameters('databaseSettings').databases[copyIndex()].name, parameters('adminUsername'), parameters('adminPassword'), parameters('databaseSettings').databases[copyIndex()].appId)]",
          "scriptContent": "
            param(
              [string] [Parameter(Mandatory=$true)] $serverName,  
              [string] [Parameter(Mandatory=$true)] $databaseName,
              [string] [Parameter(Mandatory=$true)] $userName,
              [string] [Parameter(Mandatory=$true)] $password,
              [string] [Parameter(Mandatory=$true)] $appId
            )

            $ErrorActionPreference = 'Stop'
            $DeploymentScriptOutputs = @{}
            
            Install-Module -Name SqlServer
            Import-Module -Name SqlServer

            Invoke-Sqlcmd -ServerInstance $serverName
                          -database $databaseName
                          -username $userName
                          -password $password
                          -query 'CREATE USER $appId FROM EXTERNAL PROVIDER'
          ",
          "cleanupPreference": "OnSuccess",
          "retentionInterval": "P1D"
        },
        "copy": {
            "name": "dbgeobackupcopy",
            "count": "[length(parameters('databaseSettings').databases)]"
        }
    }

This is the error:

{"status":"Failed","error":{"code":"DeploymentFailed","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/DeployOperations for usage details.","details":[{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"},{"code":"BadRequest","message":"{\r
  \"code\": \"40822\",\r
  \"message\": \"This feature is not available for the selected database's edition (Standard).\",\r
  \"target\": null,\r
  \"details\": [\r
    {\r
      \"code\": \"40822\",\r
      \"message\": \"This feature is not available for the selected database's edition (Standard).\",\r
      \"target\": null,\r
      \"severity\": \"16\"\r
    }\r
  ],\r
  \"innererror\": []\r
}"},{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.IO.FileNotFoundException: The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 14\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"}]}}

If I remove the import statement and simply use the install I get an error that indicates the command is not recognised.

{"status":"Failed","error":{"code":"DeploymentFailed","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/DeployOperations for usage details.","details":[{"code":"Conflict","message":"{\r
  \"status\": \"failed\",\r
  \"error\": {\r
    \"code\": \"ResourceDeploymentFailure\",\r
    \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r
    \"details\": [\r
      {\r
        \"code\": \"DeploymentScriptError\",\r
        \"message\": \"The provided script failed with the following error:\\r\
System.Management.Automation.CommandNotFoundException: The term 'Invoke-Sqlcmd' is not recognized as a name of a cmdlet, function, script file, or executable program.\
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.\
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)\
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)\
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)\
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)\
   at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)\
   at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)\
   at System.Management.Automation.PSScriptCmdlet.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess)\
   at System.Management.Automation.PSScriptCmdlet.DoEndProcessing()\
   at System.Management.Automation.CommandProcessorBase.Complete()\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/userscript.ps1: line 15\\r\
at <ScriptBlock>, <No file>: line 1\\r\
at <ScriptBlock>, /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 239. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for more deployment script information.\"\r
      }\r
    ]\r
  }\r
}"}

How can I use the sql module in the deployment script?

Answer 1

Try this for your script resource - note some of the params are different than your original (no copy loop). Inline PS scripts can be challenging to debug, esp with args that have quotes, spaces, dollar signs (in pwsh).

Notable differences from yours:

• the serverName needs to be fully qualified
• line breaks in the invoke-sqlcmd need to have a backtick (I believe)
• quote the password and query string
• using env vars instead of args (to save more quoting headaches)
• use -Force to install the module (unless the gallery is trusted)

and of course make sure the firewall rules all connections to sql

Aside, it would be faster to loop inside the script if passing the params was reasonable.

        {
            "type": "Microsoft.Resources/deploymentScripts",
            "apiVersion": "2020-10-01",
            "name": "[concat('createServicePrincipalLogin-', parameters('databaseName'))]",
            "location": "[resourceGroup().location]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseName'))]",
                "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
            ],
            "kind": "AzurePowerShell",
            "properties": {
                "azPowerShellVersion": "7.4",
                "forceUpdateTag": "[parameters('alwaysRun')]",
                "timeout": "PT30M",
                "environmentVariables": [
                    {
                        "name": "SERVER_NAME",
                        "value": "[reference(resourceId('Microsoft.Sql/servers', parameters('serverName')), '2014-04-01').fullyQualifiedDomainName]"
                    },
                    {
                        "name": "DATABASE_NAME",
                        "value": "[parameters('databaseName')]"
                    },
                    {
                        "name": "USER_NAME",
                        "value": "[parameters('adminUsername')]"
                    },
                    {
                        "name": "PASSWORD",
                        "secureValue": "[parameters('adminPassword')]"
                    },
                                        {
                        "name": "QUERY",
                        "value": "[format('CREATE USER \"{0}\" FROM EXTERNAL PROVIDER', guid(resourceGroup().id))]"
                    }
                ],
                "scriptContent": "

            $ErrorActionPreference = 'Stop'
            $DeploymentScriptOutputs = @{}
            
            Install-Module -Name SqlServer -Force
            Import-Module -Name SqlServer

            Invoke-Sqlcmd -ServerInstance $ENV:SERVER_NAME `
                          -database $ENV:DATABASE_NAME `
                          -username $ENV:USER_NAME `
                          -password \"$ENV:PASSWORD\" `
                          -query $ENV:QUERY
          ",
                "cleanupPreference": "OnSuccess",
                "retentionInterval": "P1D"
            }
        }
 

That help?