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

Article
02/12/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.

Diagram of resources created in private endpoint quickstart.

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.
Sign in to the Azure portal and check that your subscription is active by running az login.
Check your version of the Azure CLI in a terminal or command window by running az --version. For the latest version, see the latest release notes.
- If you don't have the latest version, update your installation by following the installation guide for your operating system or platform.

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 az group create:

Named CreateSQLEndpointTutorial-rg.
In the eastus location.

az group create \
    --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 with az network vnet create

Named myVNet.
Address prefix of 10.0.0.0/16.
Subnet named myBackendSubnet.
Subnet prefix of 10.0.0.0/24.
In the CreateSQLEndpointTutorial-rg resource group.
Location of eastus.

az network vnet create \
    --resource-group CreateSQLEndpointTutorial-rg\
    --location eastus \
    --name myVNet \
    --address-prefixes 10.0.0.0/16 \
    --subnet-name myBackendSubnet \
    --subnet-prefixes 10.0.0.0/24

Update the subnet to disable private endpoint network policies for the private endpoint with az network vnet subnet update:

az network vnet subnet update \
    --name myBackendSubnet \
    --resource-group CreateSQLEndpointTutorial-rg \
    --vnet-name myVNet \
    --disable-private-endpoint-network-policies true

Use az network public-ip create to create a public ip address for the bastion host:

Create a standard zone redundant public IP address named myBastionIP.
In CreateSQLEndpointTutorial-rg.

az network public-ip create \
    --resource-group CreateSQLEndpointTutorial-rg \
    --name myBastionIP \
    --sku Standard

Use az network vnet subnet create to create a bastion subnet:

Named AzureBastionSubnet.
Address prefix of 10.0.1.0/24.
In virtual network myVNet.
In resource group CreateSQLEndpointTutorial-rg.

az network vnet subnet create \
    --resource-group CreateSQLEndpointTutorial-rg \
    --name AzureBastionSubnet \
    --vnet-name myVNet \
    --address-prefixes 10.0.1.0/24

Use az network bastion create to create a bastion host:

Named myBastionHost.
In CreateSQLEndpointTutorial-rg.
Associated with public IP myBastionIP.
Associated with virtual network myVNet.
In eastus location.

az network bastion create \
    --resource-group CreateSQLEndpointTutorial-rg \
    --name myBastionHost \
    --public-ip-address myBastionIP \
    --vnet-name myVNet \
    --location eastus

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 a VM with az vm create. When prompted, provide a password to be used as the credentials for the VM:

Named myVM.
In CreateSQLEndpointTutorial-rg.
In network myVNet.
In subnet myBackendSubnet.
Server image Win2019Datacenter.

az vm create \
    --resource-group CreateSQLEndpointTutorial-rg \
    --name myVM \
    --image Win2019Datacenter \
    --public-ip-address "" \
    --vnet-name myVNet \
    --subnet myBackendSubnet \
    --admin-username azureuser

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.

Use az sql server create to create a SQL server:

Replace <sql-server-name> with your unique server name.
Replace <your-password> with your password.
In CreateSQLEndpointTutorial-rg.
In eastus region.

az sql server create \
    --name <sql-server-name> \
    --resource-group CreateSQLEndpointTutorial-rg \
    --location eastus \
    --admin-user sqladmin \
    --admin-password <your-password>

Use az sql db create to create a database:

Named myDataBase.
In CreateSQLEndpointTutorial-rg.
Replace <sql-server-name> with your unique server name.

az sql db create \
    --resource-group CreateSQLEndpointTutorial-rg  \
    --server <sql-server-name> \
    --name myDataBase \
    --sample-name AdventureWorksLT

Create private endpoint

In this section, you'll create the private endpoint.

Use az sql server list to place the resource ID of the SQL server into a shell variable.

Use az network private-endpoint create to create the endpoint and connection:

Named myPrivateEndpoint.
In resource group CreateSQLEndpointTutorial-rg.
In virtual network myVNet.
In subnet myBackendSubnet.
Connection named myConnection.

id=$(az sql server list \
    --resource-group CreateSQLEndpointTutorial-rg \
    --query '[].[id]' \
    --output tsv)

az network private-endpoint create \
    --name myPrivateEndpoint \
    --resource-group CreateSQLEndpointTutorial-rg \
    --vnet-name myVNet --subnet myBackendSubnet \
    --private-connection-resource-id $id \
    --group-ids sqlServer \
    --connection-name myConnection

Configure the private DNS zone

In this section, you'll create and configure the private DNS zone using az network private-dns zone create.

You'll use az network private-dns link vnet create to create the virtual network link to the dns zone.

You'll create a dns zone group with az network private-endpoint dns-zone-group create.

Zone named privatelink.database.windows.net
In virtual network myVNet.
In resource group CreateSQLEndpointTutorial-rg.
DNS link named myDNSLink.
Associated with myPrivateEndpoint.
Zone group named MyZoneGroup.

az network private-dns zone create \
    --resource-group CreateSQLEndpointTutorial-rg \
    --name "privatelink.database.windows.net"

az network private-dns link vnet create \
    --resource-group CreateSQLEndpointTutorial-rg \
    --zone-name "privatelink.database.windows.net" \
    --name MyDNSLink \
    --virtual-network myVNet \
    --registration-enabled false

az network private-endpoint dns-zone-group create \
   --resource-group CreateSQLEndpointTutorial-rg \
   --endpoint-name myPrivateEndpoint \
   --name MyZoneGroup \
   --private-dns-zone "privatelink.database.windows.net" \
   --zone-name sql

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:

az group delete \
    --name CreateSQLEndpointTutorial-rg

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