☰

Azure 自动化运行手册访问 Azure SQL 数据库

tag-icon • tag-icon azure azure-sql
Azure 自动化运行手册访问 Azure SQL 数据库

我正在尝试使用 Azure 自动化运行手册针对我的 Azure SQL 数据库运行一些 SQL 语句。在所有示例中，我都可以找到https://docs.microsoft.com他们使用自动化凭证和连接字符串，如下面的代码所示：

 $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset 

# Get the username and password from the SQL Credential 
$SqlUsername = $SqlCredential.UserName 
$SqlPass = $SqlCredential.GetNetworkCredential().Password 

# Define the connection to the SQL Database 
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;")

很简单，但是该请求将在我的 Azure SQL 防火墙中被阻止，因为我不知道该请求来自哪个 IP 地址！

如何允许我的 powershell 运行手册对 Azure SQL 数据库进行身份验证并运行 SQL 命令，而无需启用“允许访问 Azure 服务”（您在下面的屏幕截图中看到的复选框（这将允许 Azure 上的所有资源，而不仅仅是我的订阅内的资源）

在此处输入图片描述

答案1

MS 网站上提供的示例假设您已启用“允许访问 Azure 服务”选项。如果您不想这样做，则需要配置脚本以确定运行时的 IP 地址，并将其作为脚本的一部分添加到 SQL 防火墙规则中，或者您需要考虑使用混合自动化工人您可以将其放置在您自己的虚拟网络中并分配一个静态 IP。

如果您想在脚本中添加您的 IP，您可以执行以下操作：

 $response = Invoke-WebRequest ifconfig.co/ip
    $ip = $response.Content.Trim()
    New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $ip -EndIPAddress $ip -RuleName <Name of Rule> -ServerName <your database server name here>

答案2

遇到同样的问题并听取了 Sam 的意见后，我通过以下实施方式详细阐述了他的建议：

param(
[parameter(Mandatory=$true)]
[string] $resourceGroupName,

[parameter(Mandatory=$true)]
[string] $azureRunAsConnectionName,

[parameter(Mandatory=$true)]
[string] $serverName,

[parameter(Mandatory=$true)]
[string] $firewallRuleName
)

filter timestamp {"[$(Get-Date -Format G)]: $_"} 

$runAsConnectionProfile = Get-AutomationConnection -Name $azureRunAsConnectionName
Add-AzureRmAccount -ServicePrincipal -TenantId $runAsConnectionProfile.TenantId `
    -ApplicationId $runAsConnectionProfile.ApplicationId `
    -CertificateThumbprint $runAsConnectionProfile.CertificateThumbprint | Out-Null
Write-Output "Authenticated with Automation Run As Account."  | timestamp

#$ipResponse = Invoke-WebRequest ifconfig.co/ip -UseBasicParsing
#$ip = $ipResponse.Content.Trim()
$ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content


Write-Output "Automation IP Address: $ip" | timestamp

Write-Output "Check for Firewall Rule For Server $serverName" | timestamp
$fwResponse = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -FirewallRuleName $firewallRuleName `
    -ErrorAction SilentlyContinue

if ($fwResponse -ne $null -and $fwResponse.StartIpAddress -ne $ip)
{
    $removeResponse = Remove-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
        -ServerName $serverName -FirewallRuleName $firewallRuleName
    if ($removeResponse -ne $null)
    {
        Write-Output "Removed FW Rule For IP Address: $($removeResponse.StartIpAddress)" | timestamp
    }
    else
    {
        throw "Unable to Remove FW Rule For IP Address: $($removeResponse.StartIpAddress)" | timestamp
    }
}
elseif ($fwResponse -ne $null)
{
    Write-Output "FW Rule Already in Place for IP Address: $($fwResponse.StartIpAddress)" | timestamp
    return
}


Write-Output "Adding Firewall Rule For IP Address $ip" | timestamp
$newResponse = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -FirewallRuleName $firewallRuleName `
    -StartIpAddress $ip -EndIpAddress $ip
if ($newResponse -ne $null)
{
    Write-Output "Added FW Rule For IP Address: $($newResponse.StartIpAddress)" | timestamp
}
else
{
    throw "Unable to Add FW Rule For IP Address: $($removeResponse.StartIpAddress)" | timestamp
}

相关内容