Azure Powershell : Attaching NIC & Private Endpoint to SQL Server

Question

我目前正在开发 Microsoft Azure Powershell 脚本，该脚本允许用户部署/启动 SQL 数据库。

<小时/>

创建此 SQL 数据库后，我需要 NIC 连接到虚拟网络和专用端点 -> 该专用端点将与 SQL 服务器连接 -> 然后 SQL 服务器与 SQL DB 连接。然而我正在挣扎:/网卡永远不会连接到端点，私有(private)端点永远不会真正被创建，这会在我的代码中留下一个整体。当我写这篇文章时，我想知道它的顺序是否与我在下面列出的(目标)相反。我创建数据库，然后创建服务器，然后创建网卡、端点，最后尝试将其设置为虚拟网络。这似乎是可能的，但我会尝试一下 - 完成这篇文章，以防万一(我通常是错的 xd)

目标:虚拟网络(带子网)-> 网络接口(interface)卡 -> 专用端点 -> SQL Server(稍后将与虚拟机连接，但那是稍后的事) SQL Server -> SQL 数据库

<小时/>

说实话，我对一切都还很陌生，所以如果有任何反馈或我可以更好地表达的内容，请告诉我。

<小时/>

我目前有一个虚拟网络和子网设置，并且运行良好。

# Create a Virtual Network
$virtualNetwork1 = New-AzVirtualNetwork `
    -ResourceGroupName $resourceGroupNameSPOKE `
    -Location $location `
    -Name $vnetName1 `
    -AddressPrefix $vnetAddressSpace1 

# Create and add subnet to virtual network
$subnetConfig1 = Add-AzVirtualNetworkSubnetConfig `
    -Name $subnetName1 `
    -AddressPrefix $snetAddressSpace1 `
    -VirtualNetwork $virtualNetwork1
   
# Sync Vnet and subnet together
$virtualNetwork1 | Set-AzVirtualNetwork 
    
# Get Subnet ID
$subnetID1 =(Get-AzVirtualNetworkSubnetConfig -Name $subnetName1 -VirtualNetwork (Get-AzVirtualNetwork -ResourceGroupName $resourceGroupNameSPOKE -Name $vnetName1)).Id

<小时/>

我已经在这部分上停留了一段时间，只是无法完成它，我再次强烈认为订单可能已关闭。该命令可能需要更有意义，我会尝试这个，但如果有人知道并可以确认那就太棒了。任何哇...创建 SQL Server 和数据库运行时没有错误，非常棒。创建网卡没有太多问题

# Create a SQL Server
New-AzSqlServer -ResourceGroupName $resourceGroupNameSPOKE -ServerName $serverName -Location $location -SqlAdministratorCredentials (New-Object PSCredential -ArgumentList ($adminLogin, $adminPassword)) -ServerVersion "12.0"

# Create SQL Database
New-AzSqlDatabase -ResourceGroupName $resourceGroupNameSPOKE -ServerName $serverName -DatabaseName $databaseName -RequestedServiceObjectiveName "S0"

# Create NIC for SQL
$nicSQL = New-AzNetworkInterface `
    -ResourceGroupName $resourceGroupNameSPOKE `
    -Location $location `
    -Name $databaseName `
    -SubnetId $subnetID1

<小时/>

NIC 可以工作，但有时不需要 SubnetID 参数，它可以在其他地方工作，但当我将它与我的专用端点一起使用时，它不喜欢它。在此处的最后，您可以看到我尝试过但不成功的注释掉的解决方案

# Create Private Endpoint
#$privateLinkServiceConnection = New-AzPrivateLinkServiceConnection -Name $privateLinkServiceConnectionName -ServiceAlias "sql"
$privateLinkServiceConnection = New-AzPrivateLinkServiceConnection -Name $privateLinkServiceConnectionName -PrivateLinkServiceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroupNameSPOKE/providers/Microsoft.Sql/servers/$serverName"

$privateEndpoint = New-AzPrivateEndpoint -ResourceGroupName $resourceGroupNameSPOKE -Name $privateEndpointName -Location $location -SubnetId $subnetID1 -PrivateLinkServiceConnection $privateLinkServiceConnection
$privateLinkServiceConnection | Add-AzPrivateLinkServiceConnection -Name $privateLinkServiceConnectionName -NetworkInterfaceId $nicSQL.Id -PrivateEndpoint $privateEndpoint

# Associate Subnet with Private Endpoint
#Set-AzVirtualNetworkSubnetConfig -VirtualNetwork $virtualNetwork1 -Name $subnetName1 -AddressPrefix $snetAddressSpace1 -PrivateEndpointNetworkPolicies $privateEndpoint.NetworkPolicies -PrivateEndpoint $privateEndpoint


# Attach NIC to Private Endpoint
#$privateLinkServiceConnection | Add-AzPrivateLinkServiceConnection -Name $privateLinkServiceConnectionName -NetworkInterfaceId $nicSQL.Id -PrivateEndpoint $privateEndpoint

#Set-AzNetworkInterface -NetworkInterface $nicSQL

一切都不断地给我带来错误，我感觉自己在兜圈子。任何指导都会很棒

<小时/>

让我头疼的错误:

1) subnetID1 -PrivateLinkServiceConnection $privateLinkServiceConnection
     |                                            
     | Cannot bind argument to parameter 'PrivateLinkServiceConnection' because it is null.

2) $privateEndpoint.NetworkPolicies -PrivateEndpoint $privat …
     |                                                
     | Cannot bind parameter because parameter 'PrivateEndpointNetworkPoliciesFlag' is specified more than once. To provide multiple values to parameters that
     | can accept multiple values, use the array syntax. For example, "-parameter value1,value2,value3".
_____
Other Errors:

3) POKE -Name $privateEndpointName -Location $location -SubnetId $subnet …
     |                                                       
     | A parameter cannot be found that matches parameter name 'SubnetId'.


4) ateLinkServiceConnection | Add-AzPrivateLinkServiceConnection -Name $ …
     |                               
     | The term 'Add-AzPrivateLinkServiceConnection' is not recognized as a name of a cmdlet, function, script file, or
     | executable program. Check the spelling of the name, or if a path was included, verify that the path is correct
     | and try again.

再次，如果有任何指导或建议，请告诉我，这将是一个很大的帮助。即使它正在重做这篇文章以使其更加连贯。抱歉，如果有任何问题。如果我需要回答任何问题，或者应该更好地构建这篇文章，请告诉我你们的想法。先谢谢大家了

Answer 1

我在我的环境中尝试了相同的操作，得到了如下所示的相同错误:

发生此错误 New-AzPrivateLinkServiceConnection cmdlet 无法创建连接，并且多次指定 PrivateEndpointNetworkPolicies 参数。参数 -SubnetId 对于 New-AzPrivateEndpoint cmdlet 无效。

要解决此问题，请确保您使用以下命令使用有效的 subnetId。

$resourceGroupName = "<RGName>"
$virtualNetworkName = "sqlvnet" 
$subnetName = "default"

# Get the subnet ID
$subnet = Get-AzVirtualNetworkSubnetConfig -Name $subnetName -VirtualNetwork (Get-AzVirtualNetwork -ResourceGroupName $resourceGroupName -Name $virtualNetworkName)
$subnetId = $subnet.Id

# Output the subnet ID
Write-Output "Subnet ID: $subnetId"

创建了 SQL 服务器和数据库，如下所示:

# Set an administrator and password for the SQL server
$cred = Get-Credential

# Create SQL server
$parameters1 = @{
    ResourceGroupName = '<RGName>'
    ServerName = 'sqlsrver12' #SqlserverName
    SqlAdministratorCredentials = $cred
    Location = 'eastus'
}
New-AzSqlServer @parameters1

# Create database
$parameters2 = @{
    ResourceGroupName = '<RGName>'
    ServerName = 'sqlsrver12'   #SqlserverName
    DatabaseName = 'mysqldatabase'
    RequestedServiceObjectiveName = 'S0'
    SampleName = 'AdventureWorksLT'
}
New-AzSqlDatabase @parameters2

注意，在创建专用端点时，它会自动创建具有相同虚拟网络和子网的新网卡，如下所示:

您可以使用以下修改后的脚本:

# Define your variables
$resourceGroupNameSPOKE = "<RGName>"  
$serverName = "sqlsrver12"
$location = "eastus"
$subscriptionId = "209654eb-1867-49dXXXX"
$vnetName = "sqlvnet"
$subnetName = "default"
$subnetID1 = "/subscriptions/209654eb-1867-49df-9395-7f0XXXXX/resourceGroups/Aks-rg-test/providers/Microsoft.Network/virtualNetworks/sqlvnet/subnets/default"   #use your subnetID here
$privateLinkServiceConnectionName = "MyPLSConnection"
$privateEndpointName = "MyPrivateEndpoint12"
$databaseName = "mysqldatabase" 
$privateLinkServiceConnectionGroupId = "sqlServer"  # Replace with the correct Group Id
$dnsZoneName = "privatelink.database.windows.net"  #use the same dnsZoneName

# Get your virtual network and subnet
$virtualNetwork = Get-AzVirtualNetwork -ResourceGroupName $resourceGroupNameSPOKE -Name $vnetName
$subnet = Get-AzVirtualNetworkSubnetConfig -VirtualNetwork $virtualNetwork -Name $subnetName

# Create Private Link Service Connection with the correct Group Id
$plsConnection = New-AzPrivateLinkServiceConnection -Name $privateLinkServiceConnectionName -PrivateLinkServiceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroupNameSPOKE/providers/Microsoft.Sql/servers/$serverName" -GroupId $privateLinkServiceConnectionGroupId

# Create Private Endpoint
$privateEndpoint = New-AzPrivateEndpoint -Name $privateEndpointName -ResourceGroupName $resourceGroupNameSPOKE -Location $location -PrivateLinkServiceConnection $plsConnection -Subnet $subnet


$vnet = Get-AzVirtualNetwork -ResourceGroupName $resourceGroupNameSPOKE -Name $vnetName
$Zone = New-AzPrivateDnsZone -Name "$dnsZoneName" -ResourceGroupName "$resourceGroupNameSPOKE"
$Link = New-AzPrivateDnsVirtualNetworkLink -ZoneName "$dnsZoneName" -ResourceGroupName "$resourceGroupNameSPOKE" -Name "Virtualnetworklink" -VirtualNetworkId $Vnet.Id -EnableRegistration
$config = New-AzPrivateDnsZoneConfig -Name "$dnsZoneName" -PrivateDnsZoneId $Zone.ResourceId
New-AzPrivateDnsZoneGroup -ResourceGroupName "$resourceGroupNameSPOKE" -PrivateEndpointName "$privateEndpointName" -name "$dnsZoneName" -PrivateDnsZoneConfig $config -Force


## Disable private endpoint network policy ##
$vnet.Subnets[0].PrivateEndpointNetworkPolicies = "Disabled"
$vnet | Set-AzVirtualNetwork

输出:

要检查GroupId，请使用以下命令:

$subscriptionId = "your-subscription-id"
$resourceGroupName = "<RGName>"
$serverName = "<your-sql-server>"

# Define the Private Link Resource ID
$privateLinkResourceId = "/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName"

# Get the list of supported Group IDs
$groupIds = (Get-AzPrivateLinkResource -PrivateLinkResourceId $privateLinkResourceId).GroupId

# Display the list of supported Group IDs
$groupIds

在使用相同虚拟网络和子网创建的门户专用终结点中，如下所示:

确保在 sql server 中禁用公共(public)访问，如下所示:

现在创建虚拟机和堡垒，当我检查专用端点 IP 地址的连接时，成功返回专用端点的专用 IP 地址，如下所示:

在堡垒中安装 SQL Server Management Studio并如下连接:

引用:

Connect to an Azure SQL server using an Azure Private Endpoint - PowerShell | Microsoft Learn