Azure Powershell: Attaching NIC & Private Endpoint to SQL Server

I currently working on a Microsoft Azure Powershell Script which would allow users to deploy/spin up a SQL Database.

---

With the creation of this SQL database I need NIC to connect to a virtual network and private endpoint -> This private endpoint will then connect with SQL server -> then the SQL server connects with SQL DB. Yet I am struggling :/ The NIC is never attached to the endpoint, the private endpoint can never actually become created which leaves a whole in my code. As I write this I wonder if its the order as I have it backwards from what I list below (the goal). I create the DB then Server then NIC, Endpoint, and at the end try and set that to virtual network. This seems likely but I'll try it out - completing this post though just in case (Im usually wrong xd)

GOAL: Virtual Network (w/ subnets) -> Network Interface Card -> Private Endpoint -> SQL Server (this will later connect with a VM but that's later) SQL Server -> SQL DB

---

I am still green to everything to be honest so if there's any feedback or something I can word better, please let me know.

---

I currently have a virtual network and a subnet setup and that functions well.

# 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

---

I have been stuck on this portion for a little bit now and just cant get by it, again I strongly consider the order may be off. The order may need to make more sense, I'll try this but if anyone knows and could confirm that'd be awesome. Any whoo... Creating the SQL Server and Database run no errors and are great. Creating the NIC doesnt have many issues

# 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

---

The NIC works but sometimes the SubnetID parameter isnt wanted/needed, It works else where but it doesn't like it when I use it with my private endpoint. Towards the end here you can see the commented out solutions I have attempted which were unsuccessful

# 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

Everything has consistently brought me errors and I feel as I am going in a circle. Any guidance would be amazing

---

Errors that give me headaches:

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.

Again any guidance or advice for this please let me know it'd be a great help. Even if it's redoing this post to make more coherent sense. Sorry if there's any issues. PLease let me know what yall think, If i need to answer any questions, or should structure this post better. Thanks everyone in advance

I tried the same in my environment got same error like below:

This error occurs New-AzPrivateLinkServiceConnection cmdlet failed to create the connection and PrivateEndpointNetworkPolicies parameter is being specified multiple times. The parameter -SubnetId is not valid for the New-AzPrivateEndpoint cmdlet.

To resolve this issue ensure you are using valid subnetId using below command.

$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"

Created SQL server and database like below:

# 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

Note that while creating private endpoint its automatically create new Nic with same virtual network and subnet like below:

You can make use of below modified Script:

# 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

Output:

To check the GroupId use below command:

$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

In portal private endpoint created with same virtual network and subnet like below:

Make sure to disable public access in sql server like below:

Created virtual machine and bastion now when I check the connectivity to private endpoint IP address returned the private IP address of the private endpoint successfully like below:

In bastion Install SQL Server Management Studio and connected like below:

Reference:

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