Its all about Database: Connect to SQL Managed Instance from an on-premises client computer by using a point-to-site connection

In this section, you will learn how to connect SQL managed instance from an on-premises client computer by using a "point-to-site" connection. Point-to-Site VPN connections are useful when you want to connect to your VNet from a remote location.

Prerequisites:

Requires PowerShell 5.1 or later on your on-premises client machine.
Requires the latest version of SQL Server Management Studio on your on-premises client machine.
Create a SQL managed instance first. You can refer the below blog for creating a Azure SQL managed Instance.
https://clotat.blogspot.com/2021/07/provisioning-sql-managed-instance-using.html

Follow below sequence steps by step to connect SQL Managed Instance from an on-premises client machine (wherein will generate both root and clients certificate) by using a point-to-site connection:

Create VPN gateway.
Generate a root certificate: Create public key (.cer file) for a root certificate, which needs to be uploaded to Azure. Once the certificate is uploaded, it is considered a trusted certificate and is used for authentication.
Generate a client certificate: Create client certificate which is generated from the root certificate. Install the client certificate on each client machine that will connect to the VNet. This certificate is used for client authentication.
Export the root certificate public key (.cer)
Upload root certificate public key information to Azure
Connect to Azure SQL Managed Instance from a Windows VPN client

Important : If you have multiple client machine and you want to connect same Azure SQL managed instance, then you don't need to generated a root certificate in each machine. You need to export client certificate only from the machine where both root and client certificate are existing and install an exported client certificate to each client machine.

Follow below sequence steps by step to connect SQL Managed Instance from an on-premises client machine (wherein will install only an exported client certificate) by using a point-to-site connection:

Export the client certificate
Install an exported client certificate to other machine
Connect to Azure SQL Managed Instance from a Windows VPN client. (same step as above)

Create VPN Gateway:

1. Log in to https://portal.azure.com using your Azure credentials.

Type Virtual network gateway in the search box and click on it..

2. On the Virtual network gateway page, select +Create. This opens the Create virtual network gateway page.

Fill out all the information:

Instance Detail: fill any valid name

Region: Select the region in which you want to create this resource. The region for the gateway must be the same as the virtual network. Go To, Resource group and open the Virtual Network and check location.

Virtual network: From the dropdown, select the virtual network to which you want to add this gateway.

Public IP address Name: any valid name

Select Review + create to deploy the VPN gateway.

A gateway can take up to 45 minutes for deployment. You can see the deployment status on the Overview page for your gateway. After the gateway is created, you can view the IP address that has been assigned to it by looking at the virtual network in the portal.

Generate a root certificate:

Create a self-signed root certificate

1. Open a Windows PowerShell console (run as administrator), run the below script and do the necessary modification like certificate name (in my case certificate name is : CN=P2SRootCert).

$cert = New-SelfSignedCertificate -Type Custom -KeySpec Signature `
-Subject "CN=P2SRootCert" -KeyExportPolicy Exportable `
-HashAlgorithm sha256 -KeyLength 2048 `
-CertStoreLocation "Cert:\CurrentUser\My" -KeyUsageProperty Sign -KeyUsage CertSign

2. Leave the PowerShell console open and proceed with the next steps to generate a client certificate.

Generate a client certificate:

1. Run the following script on same PowerShell Console. Here client certificate name is 'P2SChildCert'.

New-SelfSignedCertificate -Type Custom -DnsName P2SChildCert -KeySpec Signature `
-Subject "CN=P2SChildCert" -KeyExportPolicy Exportable `
-HashAlgorithm sha256 -KeyLength 2048 `
-CertStoreLocation "Cert:\CurrentUser\My" `
-Signer $cert -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.2")

2. Open Manage user certificates.

Type : certmgr.msc in run command and you will find the both certificates which were generated through above steps.

Export the root certificate public key (.cer) :

1. Open Manage user certificates. Go to -> Certificate-Current User\Personal\Certificates', right click on root certificate -> All Task->Export

2. Select No, do not export the private key, and then click Next.

3. On the Export File Format page, select Base-64 encoded X.509 (.CER)., and then click Next.

4. For File to Export, Browse the location to which you want to export the certificate. Then, click Next.

5. Click Finish to export the certificate.

6. Your certificate is successfully exported on given location.

Upload root certificate public key information to Azure:

In this section, upload public root certificate data to Azure. Once the public certificate data is uploaded, Azure can use it to authenticate clients that have installed a client certificate generated from the trusted root certificate.

1. Go to Virtual network gateway -> Point-to-site configuration page.

2. Click on Configure now.

3. You will be landed on Virtual Network Gateway page.

Fill out all the information:

Address Pool: 172.168.0.0/24

The client address pool is a range of private IP addresses that you specify. The clients that connect over a Point-to-Site VPN dynamically receive an IP address from this range.

Tunnel Type: IKEv2

Authentication Type: Azure certificate

Root Certificate:

Name: P2SRootCert

Public Certificate Data: Go to the location where root certificate exported and open the certificate in with a text editor (Notepad). Copy only the following section as one continuous line and past.

And save this configuration. Click on Save.

4. Wait till get the notification: “Successfully saved virtual network gateway” as below.

Connect to Azure SQL Managed Instance from a Windows VPN client:

1. Go to resource group, click on Virtual Network Gateway and Download VPN Client.

2. Extract the downloaded Zip file.

3. Open a WindowsAmd64 folder.

4. Double click on executable file i.e. “VpnClientSetupAmd64” and install it on machine.

Click on Run and complete the installation.

5. Go to windows search and type VPN and click.

6. Click on Connect.

7. Click on Connect.

8. Now Open the SSMS in on-premises machine (wherein, both Root and Client Certificate were Generated).

Server Name: Host (go to azure portal. Open the SQL managed instance and copy the host)

Login: sqladmin

Password: enter the password

And click on Connect; now you will be able to connect from on-premise SQL SSMS to Azure SQL Managed Instance.

After you connect, you can view your system and user databases in the Databases node. You can also view various objects in the Security, Server Objects, Replication, Management, SQL Server Agent, and XEvent Profiler nodes.

execute "sp_heldb" tto check the default database name.

Important : If you have multiple client machine and you want to connect same Azure SQL managed instance from each client machine, then you don't need to generated a root certificate in each machine. You can export only client certificate from the machine where both root and client certificate are exist.

Export the client certificate:

To export a client certificate, open Manage user certificates. The client certificates that you generated are, by default, located in 'Certificates - Current User\Personal\Certificates'. Right-click the client certificate that you want to export, click all tasks, and then click Export to open the Certificate Export Wizard.

In the Certificate Export Wizard, click Next to continue.

Select Yes, export the private key, and then click Next.

On the Export File Format page, leave the defaults selected. Make sure that Include all certificates in the certification path if possible is selected. This setting additionally exports the root certificate information that is required for successful client authentication. Without it, client authentication fails because the client doesn't have the trusted root certificate. Then, click Next.

On the Security page, you must protect the private key. If you select to use a password, make sure to record or remember the password that you set for this certificate. Then, click Next.

On the File to Export, Browse to the location to which you want to export the certificate. For File name, name the certificate file. Then, click Next.

Click Finish to export the certificate.

Install an exported client certificate to other machine:

When a P2S VPN gateway is configured to require certificate authentication, each client must have a client certificate installed locally.

1. Once the client certificate is exported, locate and copy the .pfx file to the client computer.

2.On the client computer, double-click the .pfx file to install. Leave the Store Location as Current User, and then select Next.

3.On the File to import page, don't make any changes. Select Next.

4.On the Private key protection page, input the password for the certificate, or verify that the security principal is correct, then select Next.

5. On the Certificate Store page, leave the default location, and then select Next.

6.Select Finish. On the Security Warning for the certificate installation, select Yes. You can comfortably select 'Yes' for this security warning because you generated the certificate.