I am quite new to Octopus. And try to deploy our application and database to Azure and Azure Database with octopus.
I tried to use “Run an Azure PowerShell Script” tempalte to update azure database. But I got error as:
“Invoke-Expression : The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file”. Do you have any idea about this? Or do you have a better solution to run sql script for a Azure Sql Server database?
Under the covers, Azure SQL Server is no different than the self-hosted SQL Server you have used in the past. What I typically use to call out to SQL is regular PowerShell and ADO.NET. The example script below will create a user on an Azure SQL Database. I had to add some try/catch logic because of how I am running the script (I want it to run multiple times, but only create the user once).
Write-Host "Username found, using SQL Authentication"
$connectionString = "Server=$createSqlServer;Database=master;User ID=$createSqlLoginUserWhoHasCreateUserRights;Password=$createSqlLoginPasswordWhoHasRights;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString
$command = $sqlConnection.CreateCommand()
$command.CommandType = [System.Data.CommandType]'Text'
Write-Host "Opening the connection to $createSqlServer"
$escapedLogin = $createSqlLogin.Replace("'", "''")
Write-Host "Running the if not exists then create user command on the server for $escapedLogin"
$success = $true
Write-Host "A password was sent in, creating account as SQL Login"
$escapedPassword = $createSqlPassword.Replace("'", "''")
$command.CommandText = "CREATE LOGIN [$createSqlLogin] with Password='$escapedPassword'"
Write-Host "Successfully created the account $createSqlLogin"
if ($_.Exception.Message -like "*The server principal '$createSqlLogin' already exists*")
$success = $true
$success = $false
Write-Host "Closing the connection to $createSqlServer"
if ($success -eq $true)
In the Azure Portal it will give you a connection string you can use.
Let me try your solution. Thanks for help.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.