How can I run sql script for Azure Sql Server

Dear Support,
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?



Hi Max,

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"
catch {
	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)
	exit 0
else {
	exit 1

In the Azure Portal it will give you a connection string you can use.

Hi Bob,

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.