Runbook Powershell Invoke-Sqlcmd SQL Server MI connection

Question

I have a some Powershell code to drop a database on a SQL Server Managed Instance that I would like to schedule via Azure Automation. The code runs fine in the Windows Powershell ISE.

#Type the Managed instance admin login
$username =  "uname"
#Type the Managed instance admin password
$password =  'pwd'
#Type the Full Managed instance name
$managedInstance =  "sql-srvr_mangedinstance.database.windows.net"
#Leave this parameter as is
$database =  "master"
#Drop Database before restore
Invoke-Sqlcmd -ServerInstance $managedInstance -Database $database -Username $username -Password $password -Query "DROP DATABASE [DB_NAME]"
Write "Dropped Database DB_NAME"

Unfortunately it gives me this error when I run it via the Runbooks on Azure.

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) At line:32 char:1 + Invoke-Sqlcmd -ServerInstance $managedInstance -Database $database -U ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand Invoke-Sqlcmd : At line:32 char:1 + Invoke-Sqlcmd -ServerInstance $managedInstance -Database $database -U ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I have seen resolutions to this by changing the Server connection to "Server=tcp:managedinstancename.database.windows.net:1433" but this seems to be more towards a Sql Server DB on a vm. I have also included the Import-Module Sqlserver command at the top of my code even though this module is already in the runbooks module blade. Using SSMS to connect to the Instance I can confirm the Allow Remote connections box is ticked.

bharathn-msft bharathn-msft · Accepted Answer · 2019-06-17T17:36:52

You can use the latest Azure Rm SQL commandlets and remove the database.

   # Get the current context of the account which can perform operations on the database server
    $Conn = Get-AutomationConnection -Name AzureRunAsConnection
    Add-AzureRMAccount -ServicePrincipal -Tenant $Conn.TenantID -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint
    Set-AzureRmContext -SubscriptionId $conn.SubscriptionId -ErrorAction Stop | Write-Verbose

    # assign resourcegroup, database server and database names
    $rgname = "rgname"
    $dbservername = "servername"
    $dbname = "dbname"

    if(Get-AzureRmSqlDatabase -ResourceGroupName $rgname -ServerName $dbservername -DatabaseName $dbname)
        {
        Try{
            Remove-AzureRmSqlDatabase -ResourceGroupName $rgname -ServerName $dbservername -DatabaseName $dbname -Force
            Write-Output "Dropped Database $database"
        }Catch{
            $errorMessage = $_.Exception.Message
            Write-Output $errorMessage
            Write-Output 'Failed to delete database'
        }
    }

Hope this helps.

Runbook Powershell Invoke-Sqlcmd SQL Server MI connection

1 Answers