azurepowershellazure-powershellazure-virtual-network

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


Solution

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

    enter image description here

    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"
    

    enter image description here

    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:

    enter image description here

    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
    

    enter image description here

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

    enter image description here

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

    enter image description here

    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:

    enter image description here

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

    enter image description here

    enter image description here

    Reference:

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