AzureSQL Multiple Invoke-SQLCmd Loop and Connection Errors

You can try making each connection as a powershell job. This solved a very similar issue I had some time ago. Send-MailMessage closes every 2nd connection when using attachments If you want to read an explanation. Basically, if you're unable to use a .Close() method, you can force connections to close by terminating the entire session for each run. In an ideal world the cmdlet would handle all this for you, but not everything was created perfectly.

# Loop through the datatable using the values per column $table | ForEach-Object {# Set loop variables as these are easier to pass then $_.$azureSQLDatabaseName = $_.dbname# Execute SQL Query Against Azure SQL$azureSQLServerName = $azureSQLServerName + ".database.windows.net"$Cred = Get-AutomationPSCredential -Name $azureSQLCred# Pass in the needed parameters via -ArgumentList and start the job.Start-Job -ScriptBlock { Write-Output $(Invoke-Sqlcmd -ServerInstance $args[0] -Username $args[1].UserName -Password $args[1].GetNetworkCredential().Password -Database $args[0] -Query "SELECT * FROM INFORMATION_SCHEMA.TABLES " -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 } -ArgumentList $azureSQLServerName, $Cred | Wait-Job | Receive-Job}

This is untested since I do not have a server to connect to, but perhaps with a bit of work you can make something out of it.

I faced the same issue previously while doing something with the database of azure sql. You can try this

1. Create Automation Account

New-AzureRmAutomationAccount -ResourceGroupName $resourceGroupName -Name $automationAccountName -Location $location

2. Set the Automation account to work with

Set-AzureRmAutomationAccount -Name $automationAccountName -ResourceGroupName $resourceGroupName

3. Create / Import a Runbook

Here we already have a runbook ready so we import it. Here's the runbook code

workflow runbookNameValue{    inlinescript    {        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection        $MasterDatabaseConnection.ConnectionString = "ConnectionStringValue"        # Open connection to Master DB        $MasterDatabaseConnection.Open()        # Create command        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection        $MasterDatabaseCommand.CommandText = "Exec stored procedure"        # Execute the query        $MasterDatabaseCommand.ExecuteNonQuery()        # Close connection to Master DB        $MasterDatabaseConnection.Close()     }}

4. Importing

Import-AzureRMAutomationRunbook -Name $runBookName -Path $scriptPath -ResourceGroupName $resourceGroupName -AutomationAccountName $automationAccountName -Type PowerShell

I hope this helps. Instead of using Invoke-Sqlcmd use the $MasterDatabaseCommand.ExecuteNonQuery() like i've provided in the runbook. It will work

It seems that you append .database.windows.net to the server name inside the loop. I guess that's why it works for the first iteration only.

Just move this line:

$azureSQLServerName = $azureSQLServerName + ".database.windows.net"

before this line:

$table | ForEach-Object {