SQL Server 2014 Networking Lab – Configuration of Site-to-Site Link

Background and Overview

This technical guidance is for Cloud Solution Provider (CSP) partners, who would like to leverage this documented installation and configuration of the site-to-site connectivity needed to connect to Tenant subscriptions from an on-premises environment. This document covers both the manual installation and configuration of a site-to-site link using a provided script that creates the Gateway needed and configures the on-premises RRAS server.


This document assumes that a CSP partner already has an existing Partner Center portal and can manage their existing tenant Azure subscriptions. Some of the critical components and assumptions this procedure depends on have been identified below:

Key Infrastructure Dependencies:

  • Domain Controller with all appropriate accounts needed exist
    • Domain Name Identified
    • DC operational
  • A single RRAS server has been deployed on Windows 2012R2 and joined to an on-premises domain
  • The partner has an existing CSP subscription
  • Partner Center Portal access is setup
    • Access to the Partner Center Portal works
    • Administrators who will run the solutions have been added in the Partner Center Portal
  • CSP partner has identified an account that will be used for the scripts that has access to the Tenant subscription that will be used

Setup on-premises environment

This section covers setting up the on-premises environment to configure site-to-site VPN connection for a unique CSP tenant subscription.


This guidance document walks participants through the creation of site-to-site VPN connection between an existing on-premises CSP provisioned dedicated customer environment and a CSP customer provisioned tenant site in Azure. This process can be leveraged for connecting the CSP environment to a CSP provisioned tenant as well. To ensure success the following preliminary preconditions must be ensured.


  • VLAN is provisioned for a unique customer and supports a Local Network address space of
  • The local domain controller is setup to use MASK
  • The local RRAS server is setup to use MASK
  • The identified Azure Network will be
  • At least 5 Windows 2012R2 Data Center servers have been provisioned on-site with one setup as a DC, RRAS, File Server and 2 servers with SQL 2014 installed.
  • An account has been setup for log on purposes and is a member of the Domain Admin group for testing.
  • A RRAS server with one network on the internet and the other on the Local Network using
  • An Internet IP address has been identified and is mapped using NAT to the internal RRAS IP address (example: 192.168.1.x)
  • All on-premises servers are joined to the Domain
  • A persistent route is set on the all on-premises servers and point to on the backend so routing to Azure via the RRAS server is possible.

Setup and configuration with PowerShell

The following procedure will assist CSP partners with the setup and configuration using PowerShell. The details below will help partners understand each of the key phases required to successully provision a new Site-to-Site VPN for a unique CSP Tenant Site that is provisioned via the Partner Center portal. Our approach list each command needed with our recommendations to ensure success as part of the end-to-end process.


  • Defining the key parameters needed
  • Validate that RRAS is setup and configured
  • Connecting to an existing CSP subscription from an on-premises server
  • Creating a virtual network and gateway subnet
  • Adding your local site
  • Requesting a public IP address for the new gateway that will be provisioned
  • Once the public IP has been provisioned you can create the gateway
  • Configure your VPN Device (RRAS)
  • Create the VPN connection

High Level Architecture

The following high-level architecture will help you illustrate what we want to achieve with the steps below in setting up the site-2-site. We assume that you have already provisioned a CSP tenant site in Azure and have captured the details called out in the key parameters section below.

Here is some additional supporting details of what we are using in this guidance.

Defining Key Parameters

Note: Items highlighted in the table below need to be updated prior to running the script.

Defining the key parameters needed








The address prefix to use for the Azure(tm) virtual network.




The name for the gateway endpoint on Azure(tm).




The name for the Azure(tm) virtual network.




The name for the Azure(tm) public IP address assignment.




The address prefix for the Azure (tm) gateway subnet.




The name for the Azure (tm) gateway subnet.




The address prefix for the on-premises network subnet.




The name for the gateway endpoint on-premises.




The public IP address for RRAS server.



"West US"

The geographic location of the Azure (tm) datacenter hosting the remote services.




The address prefix for the on Azure (tm) network subnet.




The name of the resource group to create to manage the resources created while setting up the gateway.



"TBD by each CSP partner"

The password to use encrypt and decript site to site traffic.



Leave Blank

Do not setup the Azure (tm) resources. The resources must already be set up if this switch is used. Not really used by us today but wanted to ensure that the server does not reboot.



"Identified in CSP Portal"

The CSP tenant subscription id.



"Identified in CSP Portal"

The CSP tenant id.



GatewayDemoScripts.ps1 -Credential $Credential -SecretKey ""Identified by each CSP partner"

The credentials to use to authenticate with Azure(tm) services.

Identify CSP Tenant

Let's first determine what CSP Tenant Site will be used in the CSP Portal.

  1. The HSP should log into the appropriate Partner Center Environment.

  1. Now select "View Customers" to see the already provisioned customers that can be used.

  1. Select the customer you want to use and open up the details by clicking on the item the green arrow points to.

  1. Now capture the $TenantId by coping the following item into note pad.

  1. Now let's determine what the $SubscriptionId is by selecting View Subscription and then selecting the arrow to view on the right.

  1. Now you will want to update the script and identify a unique $SecretKey that will be used to encrypt communications.

  1. You're now ready to begin to build your site-to-site as outlined in the next section.

Setting up Site-to-Site

The following procedures in this section assume that you have already provisioned the both the on-premises environment as well as provisioned the CSP tenant site for a unique customer. This procedure further assumes that it is being implemented for a customer that has a dedicated CSP partner-provisioned environment in the CSP partner environment and will be setting up a site-to-site to a CSP partner-provisioned tenant site.

  1. Log into the RRAS server that will be used on-premises.

  1. Now we need to bring up the installed "Windows PowerShell ISE" application as an Administrator (simply right click on app and select run-as administrator).

  1. Now select to open a new code window as follows.

  1. You will now see the split window as follows.

  1. Now go ahead and either paste the updated code you have created using the code sample in "Appendix A" or your updated version of it.

  1. Update the appropriate parameter details section based on your unique setup as shown.

  1. Finally, review your $Secret key that you plan on using and ensure it is unique for all subscriptions. Note: Store it in a safe place for later use.

  1. You're now ready to begin execution of the scripts outlined next.
  2. Now we will run the entire script after you log into and pass your credentials for the Partner Center account needed that has rights for all tenants. Log in using the CSP Tenant Account (example: BBBUser@BBAndrea111.onmicrosoft.com and provide the password). Below you can see the details and notice this account is an owner.

  1. Once you begin running the script using Windows PowerShell ISE you will be prompted for Azure creds to run the script.

  1. Once the script completes you will see the following components created in Azure.

  1. Request a public IP address for the gateway starts. Note: You will noticed that this takes a long time to get assigned so please be patient as this gets provisioned and shown in the tenant site.

  1. You will then see the created gateway completed and you can review the IP addressing configuration.

  1. You will then be able to review the create gateway that is contained in the resource group created.

  1. Finally, the on-premises RRAS VPN is configured and will then restart.

    Note: If needed, go ahead and right click on the new Network Interface created and select Connect.

If needed select to connect as shown.

  1. Verify a VPN Connect was created.

  1. You have successfully created a Site-to-Site Gateway. Now, follow the next section to test that you can actually use it.

Add Compute Resource

This covers adding a new Compute Resource and validate that you can join it to your on-premises domain. In the following steps will setup a new VM in the CSP tenant site that will actually use the new Site-to-Site environment. We assume that you have already setup the on-premises environment so that we can use to test communications using ping once the new VM gets built in the CSP Tenant subscription, which will use the recently created Site-to-Site procedure. Once we configure the new VM we will then join it to the on-premises domain.

  1. Modify the DNS settings to point to the on-premises Domain as shown.

  1. Click on the "DNS Servers" settings and change it to point to "" as shown.

  1. Now let us create a new VM in the CSP Tenant Subscription by selecting Virtua Machine and then select the +Add sign.

  1. Now search for "Windows Server 2012 R2 Data Center".

  1. Select Create to continue.

  1. Identify the Server Name, UserName, Password, Resource Group to use – Note: We will put all VM using the Site-to-Site in the same Resource Group we already created. Verify the Location and then click OK to continue.

  1. Now select the Server Size Needed. We use the D1 Standard Server configuration for our testing and then click on Select to continue.

  1. Now noticed that the network it wants to put the VM on is defaulted to the GW Subnet which is wrong. You need to change it to a subnet that is created for servers. An example is shown in the next step (subnet1).

  1. You can see that we have identified a Subnet called VSUB01 which we will be using for all VMs.

  1. Review the details for the VM that will be provisioned once you hit OK. We recommend you verify that the VM will be put on the right Subnet which we recently changed.

  1. The New VM is now being created.

  1. Once it is created you want to connect to it and log on using the credentials you already identified.

  1. Select Connect and log onto the VM.

  1. Select "Yes" to continue.

  1. Once you connect to the new VM verify that it was issued an IP address that points to the on-premises DNS of

  1. Join it to the on-premises Domain as shown in this example.

  1. Now let's configure the Firewall settings on the new VM to allow SMB Traffic as follows.

  1. Reboot VM - Reboot server so that it joins to the new Domain
  2. Once the VM has been rebooted continue to test. Now let's simply ping the RRAS Server and DC as a final check. From the new VM Server created in the CSP Tenant Subscription ping both the RRAS Server and DC Note: If you are unable to ping either from on-premises setup to Azure Server or back check the following:
  • On the RRAS Server, ensure "Enable IPv4 Forwarding" is selected.
  • On the RRAS Server, under the General Tab, ensure that the "IPv4 Router is selected and "LAN and demand-dial routing" is selected.

  1. You have successfully deployed a new Site-to-Site connection and tested it.

Appendix: Site-to-Site Script

The following is the script that you can use to create a site-to-site link. You will need the change the highlighted items below to ensure you are setting it up using the right CSP provisioned Azure Tenant Subscription: Note: This script should be run from the RRAS server that is on-premises since it will update the RRAS connection settings as well as create a new Azure Gateway. Ensure you update it for your implementation where needed.

Creates a VPN gateway connection between Azure (tm) and the on-premises environments.

$credential = Get-Credential
.\GW.ps1 -Credential $Credential -SecretKey "thequickgreycatjumpsoverthelazyf0x"

.PARAMETER Credential
The credentials to use to authenticate with Azure(tm) services.

.PARAMETER AzureGateway
The name for the gateway endpoint on Azure(tm).

.PARAMETER AzureNetworkName
The name for the Azure(tm) virtual network.

.PARAMETER AzurePublicIpName
The name for the Azure(tm) public IP address assignment.

.PARAMETER GatewaySubnetName
The name for the Azure (tm) gateway subnet.

.PARAMETER LocalAddressPrefix
The address prefix for the on-premises network subnet.

.PARAMETER LocalGateway
The name for the gateway endpoint on-premises.

.PARAMETER LocalGatewayIpAddress
The public IP address for RRAS server.

The geographic location of the Azure (tm) datacenter hosting the remote services.

.PARAMETER RemoteAddressPrefix
The address prefix for the on Azure (tm) network subnet.

.PARAMETER ResourceGroupName
The name of the resource group to create to manage the resources created while setting up the gateway.

The password to use encrypt and decript site to site traffic.

.PARAMETER SubscriptionId
The CSP tenant subscription id.

The CSP tenant id.
#Target "CSPTERADEMO2.onmicrosoft.com
  [Parameter()][string]$AzureGateway = "gate1", #Name of GW
  [Parameter()][string]$AzureNetworkName = "VNET01", #Name of Network ** update required **
  [Parameter()][string]$AzurePublicIpName = "Gateip", # Public IP
  [Parameter()][string]$GatewaySubnetName = "GatewaySubnet", #Subnet Name for GW ** update required **
  [Parameter()][string]$LocalAddressPrefix = "", #Onpremises Network Range ** update required **
  [Parameter()][string]$LocalGateway = "gate2", #Name of Local GW
  [Parameter()][string]$LocalGatewayIpAddress = "", #IP Onpremises GW ** update required **
  [Parameter()][string]$Location = "West US", #Region
  [Parameter()][string]$RemoteAddressPrefix = "", #Address for the Azure network ** update required **
  [Parameter()][string]$ResourceGroupName = "VNET01", #Resource Group name in Azure you defined ** update required **
  [Parameter()][string]$Secretkey = "thequickgreycatjumpsoverthelazyf0x", #Secret Key is whatever you make it. ** update required **
  [Parameter()][string]$SubscriptionId = "Enter Subscription ID HERE", #** update required **
  [Parameter()][string]$TenantId = "Enter Tenant ID HERE",) #** update required **
Import-Module RemoteAccess
Function Invoke-WindowsApi
([string] $dllName,
      [Type] $returnType,
      [string] $methodName,
      [Type[]] $parameterTypes,
      [Object[]] $parameters)

  ## Begin to build the dynamic assembly
  $domain =
[AppDomain]::CurrentDomain  $name
= New-Object Reflection.AssemblyName 'PInvokeAssembly'

  $assembly = $domain.DefineDynamicAssembly($name, 'Run')
  $module = $assembly.DefineDynamicModule('PInvokeModule')
  $type = $module.DefineType('PInvokeType', "Public,BeforeFieldInit")

  $inputParameters = @()

  for($counter = 1; $counter –le $parameterTypes.Length; $counter++)

    $inputParameters += $parameters[$counter - 1]

  $method =
$type.DefineMethod($methodName, 'Public,HideBySig,Static,PinvokeImpl',$returnType, $parameterTypes)

  ## Apply the P/Invoke constructor
  $ctor = [Runtime.InteropServices.DllImportAttribute].GetConstructor([string])
  $attr = New-Object Reflection.Emit.CustomAttributeBuilder $ctor, $dllName


  ## Create the temporary type, and invoke the method.
  $realType = $type.CreateType()

  $ret =
$realType.InvokeMember($methodName, 'Public,Static,InvokeMethod', $null, $null, $inputParameters)
  return $ret


  ## Prepare the parameter types and parameter values for the Invoke-WindowsApi script
  $parameterTypes =
[string], [string], [string], [string]
  $parameters = [string] $category, [string] $key, [string] $value, [string] $file

  ## Invoke the API
  [void] (Invoke-WindowsApi "kernel32.dll" ([UInt32]) "WritePrivateProfileString" $parameterTypes $parameters)

$ErrorActionPreference =
Write-Verbose -Message "Authenticating with Azure resource management"
Add-AzureRmAccount –Credential $Credential –SubscriptionId $SubscriptionId –TenantId $TenantId

Write-Verbose –
Message "Adding Local Site"
$localgate = New-AzureRmLocalNetworkGateway –Name $LocalGateway -ResourceGroupName $ResourceGroupName -Location $Location -GatewayIpAddress $LocalGatewayIpAddress -AddressPrefix $LocalAddressPrefix

Write-Verbose –
Message "Requesting IP address for Azure endpoint"
$azuregateip = New-AzureRmPublicIpAddress –Name $AzurePublicIpName –ResourceGroupName $ResourceGroupName –Location $Location –AllocationMethod Dynamic

Write-Verbose –
Message "Setting up gateway ip configuration"
$vnet = Get-AzureRmVirtualNetwork –Name $AzureNetworkName –ResourceGroupName $ResourceGroupName
= Get-AzureRmVirtualNetworkSubnetConfig –Name $GatewaySubnetName –VirtualNetwork $vnet
= New-AzureRmVirtualNetworkGatewayIpConfig –Name gatewayipconfig –SubnetId
$snet.Id -PublicIpAddressId $azuregateip.Id

-Message "Creating the gateway"
$azuregate = New-AzureRmVirtualNetworkGateway –Name $AzureGateway –ResourceGroupName $ResourceGroupName –Location $Location –IpConfigurations $ipconfig –GatewayType Vpn –VpnType RouteBased

#Import-Module RemoteAccess
Write-Verbose –Message "Creating gateway"
New-AzureRmVirtualNetworkGatewayConnection –Name gateconnect –ResourceGroupName $ResourceGroupName –Location $Location –VirtualNetworkGateway1 $azuregate –LocalNetworkGateway2 $localgate –ConnectionType IPsec –RoutingWeight 10 –SharedKey $SecretKey

Write-Verbose –
Message "Get Azure public IP address"
$azuregateip = Get-AzureRmPublicIpAddress –Name $AzurePublicIpName –ResourceGroupName $ResourceGroupName

= "Continue"
-Message "Add VPN interface to RRAS"
Add-VpnS2SInterface -Protocol IKEv2 -AuthenticationMethod PSKOnly –NumberOfTries 3 –ResponderAuthenticationMethod PSKOnly –Name $azuregateip.IpAddress -Destination $azuregateip.IpAddress -IPv4Subnet @("${RemoteAddressPrefix}:100") -SharedSecret $SecretKey

Write-Verbose –
Message "Configuring encryption in RRAS"
Set-VpnServerIPsecConfiguration –EncryptionType MaximumEncryption
Set-VpnServerIPsecConfiguration –SADataSizeForRenegotiationKilobytes 33553408
New-ItemProperty –Path HKLM:\System\CurrentControlSet\Services\RemoteAccess\Parameters\IKEV2 -Name SkipConfigPayload -PropertyType DWord -Value 1
Set-PrivateProfileString $env:windir\System32\ras\router.pbk "$($azuregateip.IpAddress)" "IdleDisconnectSeconds" "0"
Set-PrivateProfileString $env:windir\System32\ras\router.pbk "$($azuregateip.IpAddress)" "RedialOnLinkFailure" "1"

-Message "Restarting RRAS"
Restart-Service RemoteAccess

-Message "Connect to the gateway"
Connect-VpnS2SInterface -Name $($azuregateip.IpAddress)