Tutorial: Connect to an Azure SQL server using an Azure Private Endpoint using Azure PowerShell

Article
05/11/2023

Azure Private endpoint is the fundamental building block for Private Link in Azure. It enables Azure resources, like virtual machines (VMs), to communicate with Private Link resources privately.

In this tutorial, you learn how to:

Create a virtual network and bastion host.
Create a virtual machine.
Create an Azure SQL server and private endpoint.
Test connectivity to the SQL server private endpoint.

Prerequisites

An Azure account with an active subscription. Create an account for free.
If you choose to install and use PowerShell locally, this article requires the Azure PowerShell module version 5.4.1 or later. Run Get-Module -ListAvailable Az to find the installed version. If you need to upgrade, see Install Azure PowerShell module. If you're running PowerShell locally, you also need to run Connect-AzAccount to create a connection with Azure.

Create a resource group

An Azure resource group is a logical container into which Azure resources are deployed and managed.

Create a resource group with New-AzResourceGroup:

New-AzResourceGroup -Name 'CreateSQLEndpointTutorial-rg' -Location 'eastus'

Create a virtual network and bastion host

In this section, you'll create a virtual network, subnet, and bastion host.

The bastion host will be used to connect securely to the virtual machine for testing the private endpoint.

Create a virtual network and bastion host with:

## Create backend subnet config. ##
$subnetConfig = New-AzVirtualNetworkSubnetConfig -Name myBackendSubnet -AddressPrefix 10.0.0.0/24

## Create Azure Bastion subnet. ##
$bastsubnetConfig = New-AzVirtualNetworkSubnetConfig -Name AzureBastionSubnet -AddressPrefix 10.0.1.0/24

## Create the virtual network. ##
$parameters1 = @{
    Name = 'MyVNet'
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    Location = 'eastus'
    AddressPrefix = '10.0.0.0/16'
    Subnet = $subnetConfig, $bastsubnetConfig
}
$vnet = New-AzVirtualNetwork @parameters1

## Create public IP address for bastion host. ##
$parameters2 = @{
    Name = 'myBastionIP'
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    Location = 'eastus'
    Sku = 'Standard'
    AllocationMethod = 'Static'
}
$publicip = New-AzPublicIpAddress @parameters2

## Create bastion host ##
$parameters3 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    Name = 'myBastion'
    PublicIpAddress = $publicip
    VirtualNetwork = $vnet
}
New-AzBastion @parameters3

It can take a few minutes for the Azure Bastion host to deploy.

Create test virtual machine

In this section, you'll create a virtual machine that will be used to test the private endpoint.

Create the virtual machine with:

## Set credentials for server admin and password. ##
$cred = Get-Credential

## Command to get virtual network configuration. ##
$vnet = Get-AzVirtualNetwork -Name myVNet -ResourceGroupName CreateSQLEndpointTutorial-rg

## Command to create network interface for VM ##
$parameters1 = @{
    Name = 'myNicVM'
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    Location = 'eastus'
    Subnet = $vnet.Subnets[0]
}
$nicVM = New-AzNetworkInterface @parameters1

## Create a virtual machine configuration.##
$parameters2 = @{
    VMName = 'myVM'
    VMSize = 'Standard_DS1_v2'
}
$parameters3 = @{
    ComputerName = 'myVM'
    Credential = $cred
}
$parameters4 = @{
    PublisherName = 'MicrosoftWindowsServer'
    Offer = 'WindowsServer'
    Skus = '2019-Datacenter'
    Version = 'latest'
}
$vmConfig = 
New-AzVMConfig @parameters2 | Set-AzVMOperatingSystem -Windows @parameters3 | Set-AzVMSourceImage @parameters4 | Add-AzVMNetworkInterface -Id $nicVM.Id

## Create the virtual machine ##
New-AzVM -ResourceGroupName 'CreateSQLEndpointTutorial-rg' -Location 'eastus' -VM $vmConfig

Note

Azure provides a default outbound access IP for VMs that either aren't assigned a public IP address or are in the backend pool of an internal basic Azure load balancer. The default outbound access IP mechanism provides an outbound IP address that isn't configurable.

The default outbound access IP is disabled when one of the following events happens:

A public IP address is assigned to the VM.
The VM is placed in the backend pool of a standard load balancer, with or without outbound rules.
An Azure NAT Gateway resource is assigned to the subnet of the VM.

VMs that you create by using virtual machine scale sets in flexible orchestration mode don't have default outbound access.

For more information about outbound connections in Azure, see Default outbound access in Azure and Use Source Network Address Translation (SNAT) for outbound connections.

Create an Azure SQL server

In this section, you'll create a SQL server and database using:

Create SQL server and database. Replace <sql-server-name> with your unique server name:

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

## Create SQL server ##
$parameters1 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    ServerName = '<sql-server-name>'
    SqlAdministratorCredentials = $cred
    Location = 'eastus'
}
New-AzSqlServer @parameters1

## Create database. ##
$parameters2 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    ServerName = '<sql-server-name>'
    DatabaseName = 'mysqldatabase'
    RequestedServiceObjectiveName = 'S0'
    SampleName = 'AdventureWorksLT'
}
New-AzSqlDatabase @parameters2

Create private endpoint

In this section, you'll create the private endpoint and connection using:

## Place SQL server into variable. Replace <sql-server-name> with your server name ##
$server = Get-AzSqlServer -ResourceGroupName CreateSQLEndpointTutorial-rg -ServerName <sql-server-name>

## Create private endpoint connection. ##
$parameters1 = @{
    Name = 'myConnection'
    PrivateLinkServiceId = $server.ResourceID
    GroupID = 'sqlserver'
}
$privateEndpointConnection = New-AzPrivateLinkServiceConnection @parameters1

## Place virtual network into variable. ##
$vnet = Get-AzVirtualNetwork -ResourceGroupName 'CreateSQLEndpointTutorial-rg' -Name 'myVNet'

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

## Create private endpoint
$parameters2 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    Name = 'myPrivateEndpoint'
    Location = 'eastus'
    Subnet = $vnet.Subnets[0]
    PrivateLinkServiceConnection = $privateEndpointConnection
}
New-AzPrivateEndpoint @parameters2

Configure the private DNS zone

In this section you'll create and configure the private DNS zone using:

## Place virtual network into variable. ##
$vnet = Get-AzVirtualNetwork -ResourceGroupName 'CreateSQLEndpointTutorial-rg' -Name 'myVNet'

## Create private dns zone. ##
$parameters1 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    Name = 'privatelink.database.windows.net'
}
$zone = New-AzPrivateDnsZone @parameters1

## Create dns network link. ##
$parameters2 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    ZoneName = 'privatelink.database.windows.net'
    Name = 'myLink'
    VirtualNetworkId = $vnet.Id
}
$link = New-AzPrivateDnsVirtualNetworkLink @parameters2

## Create DNS configuration ##
$parameters3 = @{
    Name = 'privatelink.database.windows.net'
    PrivateDnsZoneId = $zone.ResourceId
}
$config = New-AzPrivateDnsZoneConfig @parameters3

## Create DNS zone group. ##
$parameters4 = @{
    ResourceGroupName = 'CreateSQLEndpointTutorial-rg'
    PrivateEndpointName = 'myPrivateEndpoint'
    Name = 'myZoneGroup'
    PrivateDnsZoneConfig = $config
}
New-AzPrivateDnsZoneGroup @parameters4

Test connectivity to private endpoint

In this section, you'll use the virtual machine you created in the previous step to connect to the SQL server across the private endpoint.

Sign in to the Azure portal.
Select Resource groups in the left-hand navigation pane.
Select CreateSQLEndpointTutorial-rg.
Select myVM.
On the overview page for myVM, select Connect then Bastion.
Select the blue Use Bastion button.
Enter the username and password that you entered during the virtual machine creation.
Open Windows PowerShell on the server after you connect.
Enter nslookup <sqlserver-name>.database.windows.net. Replace <sqlserver-name> with the name of the SQL server you created in the previous steps. You'll receive a message similar to what is displayed below:
```
Server:  UnKnown
Address:  168.63.129.16

Non-authoritative answer:
Name:    mysqlserver8675.privatelink.database.windows.net
Address:  10.0.0.5
Aliases:  mysqlserver8675.database.windows.net
```
A private IP address of 10.0.0.5 is returned for the SQL server name. This address is in the subnet of the virtual network you created previously.
Install SQL Server Management Studio on myVM.
Open SQL Server Management Studio.

In Connect to server, enter or select this information:

Setting	Value
Server type	Select Database Engine.
Server name	Enter <sql-server-name>.database.windows.net
Authentication	Select SQL Server Authentication.
User name	Enter the username you entered during server creation
Password	Enter the password you entered during server creation
Remember password	Select Yes.

Select Connect.
Browse databases from the left menu.
(Optionally) Create or query information from mysqldatabase.
Close the bastion connection to myVM.

Clean up resources

When you're done using the private endpoint, SQL server, and the VM, delete the resource group and all of the resources it contains:

Enter CreateSQLEndpointTutorial-rg in the Search box at the top of the portal and select CreateSQLEndpointTutorial-rg from the search results.
Select Delete resource group.
Enter CreateSQLEndpointTutorial-rg for TYPE THE RESOURCE GROUP NAME and select Delete.

Next steps

In this tutorial, you created a:

Virtual network and bastion host.
Virtual machine.
Azure SQL server with private endpoint.

You used the virtual machine to test connectivity securely to the SQL server across the private endpoint.

As a next step, you may also be interested in the Web app with private connectivity to Azure SQL database architecture scenario, which connects a web application outside of the virtual network to the private endpoint of a database.

Web app with private connectivity to Azure SQL database

Share via