How-to set up point-to-site peering with Azure SQL Managed Instance and self-signed certificates

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

First published on MSDN on Nov 01, 2018
This tutorial assumes the Managed Instance is already created and that connections from the jumpbox also work.

If you have a Managed Instance and would like to connect directly from a place which is outside of Azure, you may have noticed that Managed Instances are only accessible via private IPs. Once a Managed Instance is created, we offer a Getting Started guide to deploy a Virtual Machine inside another subnet on the same vnet the MI is part of, but you may want to skip having a VM provisioned in Azure and connecting as you usually do with Azure SQL Database. This means that you would need a direct connection from your laptop, for example, to the vnet where the MI was deployed and this can be done using Point-to-Site peering. Instructions below:

1. On the Getting Started page, run the script (via local PowerShell) under the P2S tab to set up the VPN gateway, configure it for P2S and attach it to the vnet (this all happens automatically with the script)

1. Fire up your computer and create a self-signed root certificate via PowerShell (that we will later upload to Azure) with the below script (you can change the name in the -Subject variable, the certificate will be automatically installed on your local Cert Store):

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

1. Using the same PowerShell session, run the following command to create and install a client certificate from the root certificate created in the step above:

$cert = 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")

1. Next, lets Export the root certificate from the Windows Certificate Store:
   
   
   
   • Search in the Start menu for Manage user certificates
   
   
   • 
   
   
   • In the Export Wizard, do not export the private key and use Base-64 encoding.
   
   
   • (Optional) If the client certificate needs to be in another computer, export it using the following options:
     
     
     
     • Yes to the private key.
     
     
     • Leave the default options in Export File Format .
     
     
     • Make sure to protect it with a password.
     
     
     
     
   
   
   
   


2. Next, look in the Portal for Virtual Network Gateways (you can write network gateways in the search bar for faster access) and there should be one created under the Managed Instance vnet, open it and click the Point-to-site configuration blade

1. Open the previously exported Root certificate in Notepad and copy everything shown in blue:

1. Copy that string to a new certificate line in the blade we opened in the gateway and assign it a name then save the settings.

1. On that same blade click the Download VPN client button, extract it and run the setup version for your indows version.

1. Next, open the VPN settings in your Windows client machine and connect to the recently installed VPN client. If the client certificate was successfully installed on the machine, the connection should go through and connecting via SSMS to your Managed Instance should work.