Azure Automation中Invoke-SqlCmd循环访问多Azure SQL数据库连接错误

先直接说核心问题：你的代码里有个致命的逻辑错误导致后续连接全部失败——在ForEach-Object循环内，你每次都会执行$azureSQLServerName = $azureSQLServerName + ".database.windows.net"。第一次循环后，服务器名称变成了myazuresql.database.windows.net，第二次循环就会变成myazuresql.database.windows.net.database.windows.net，这个无效的地址自然连不上数据库。

当然，关于连接未关闭的担忧也有道理，虽然Invoke-SqlCmd默认会自动释放连接，但显式控制连接生命周期能避免潜在的连接池问题。下面是修正后的完整代码，同时优化了资源管理：

修正后的代码 #

param( 
    # Parameters to Pass to PowerShell Scripts 
    [parameter(Mandatory=$true)][String] $azureSQLServerName = "myazuresql", 
    [parameter(Mandatory=$true)][String] $azureSQLCred = "myazureautosqlcred" 
) 

# 提前拼接完整服务器地址，避免循环内重复拼接出错
$fullSQLServerName = "$azureSQLServerName.database.windows.net"
# DB Name Array 
$dbnamearray = @("database1","database2","database3") 
$dbnamearray 

# Datatable Name 
$tabName = "RunbookTable" 
#Create Table object 
$table = New-Object system.Data.DataTable "$tabName" 
#Define Columns 
$col1 = New-Object system.Data.DataColumn dbname,([string]) 
#Add the Columns 
$table.columns.add($col1) 

# Add Row and Values for dname Column 
ForEach ($db in $dbnamearray) { 
    $row = $table.NewRow() 
    $row.dbname = $db 
    #Add the row to the table 
    $table.Rows.Add($row) 
} 
#Display the table 
$table | format-table -AutoSize 

# 提前获取凭据，避免循环内重复调用
$Cred = Get-AutomationPSCredential -Name $azureSQLCred 
$username = $Cred.UserName
$password = $Cred.GetNetworkCredential().Password

# Loop through the datatable using the values per column 
$table | ForEach-Object { 
    $azureSQLDatabaseName = $_.dbname 

    # 显式创建连接对象，精准控制连接生命周期
    $connectionString = "Server=$fullSQLServerName;Database=$azureSQLDatabaseName;User ID=$username;Password=$password;Connect Timeout=60;"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    
    try {
        $connection.Open()
        $command = $connection.CreateCommand()
        $command.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES "
        $command.CommandTimeout = 65535

        # 执行查询并返回结果
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataset)
        $SQLOutput = $dataset.Tables[0]
        Write-Output $SQLOutput
    }
    catch {
        Write-Error "访问数据库 $azureSQLDatabaseName 失败: $_"
    }
    finally {
        # 确保连接被关闭并释放资源
        if ($connection.State -ne [System.Data.ConnectionState]::Closed) {
            $connection.Close()
            $connection.Dispose()
        }
    }
}

关键优化点说明 #

• 修复服务器地址拼接问题：把完整服务器地址的拼接移到循环外，确保每次循环使用的都是正确的地址。
• 提前获取凭据：将Get-AutomationPSCredential移到循环外，减少不必要的调用，提升执行效率。
• 显式管理数据库连接：使用SqlConnection和SqlCommand对象替代直接调用Invoke-SqlCmd，通过finally块强制关闭并释放连接，彻底避免连接泄漏问题。
• 增加错误处理：通过try-catch捕获异常并输出详细错误信息，方便后续排查问题。

内容的提问来源于stack exchange，提问作者Kode