top of page
  • Ben Watt
    • Aug 14, 2022

Creating a SQL Virtual Network Rule across Azure tenants

Recently a customer provided access to an Azure SQL Database on their tenant. They had it secured using firewall rules and so requested any IP addresses from me to add to the rules.


Given this was part of a Power BI Solution, this forced the need for an Azure Virtual Machine with a fixed IP and the Power BI on-premises gateway That immediately means cost in running the VM and time/effort securing/patching/updating the OS and the Gateway software. They lost me at hello!



The Virtual Network Data Gateway is a more cost effective & frictionless way to create connectivity between services, so I asked if we could go down that route, answer was yes.


To do this, I needed to create a Virtual Network in my Azure tenant and have it added to my customer's Azure SQL Virtual Network rules. When adding a virtual network rule within your own Azure tenant, this can be done in the Azure Portal in few simple clicks, or the below PowerShell script. 


Connect-AzAccount
 
$rgName = "[resource-group-sql-server]"
$sqlServerName = "[customer-sql-server]"
$ruleName = "Let-Datalineo-In"
$SqlServerSubscription = "[customer-sql-subscription-guid]"
$vNetSubnetId = "/subscriptions/[datalineo-tenant-guid]/resourceGroups/[datalineo-subscription-guid]/providers/Microsoft.Network/virtualNetworks/[vnet-name]/subnets/[subnet-name]"

Set-AzContext $SqlServerSubscription
 
$Parameters = @{
ResourceGroupName = $rgName
ServerName = $sqlServerName 
VirtualNetworkRuleName = $ruleName
VirtualNetworkSubnetId = $vNetSubnetId
}
 
New-AzSqlServerVirtualNetworkRule @Parameters

However, cross-tenant configuration is a little more complex. The customer's Azure tenant is not going to be aware of VNet's in my tenant and furthermore won't be able to authenticate to set it up. But it's possible, so here's what we did to set up the rule on their Azure SQL Server.


Along with using the Azure Portal or the above PowerShell script, VNet rules can be added using the Azure SQL Database REST API. The most important part is that the user needs to authenticate to both tenants using the same Azure AD account, i.e. email address.


This means the customer needed to be added to my tenant as a Azure AD Guest user. Adding to that, the guest user needs to have permissions on the Virtual Network I created in my tenant. In my laziness I added the user as an owner, but I assume a lesser privelege permission is required. And if you're thinking the same way I was, yes that user can have VNet permissions & Azure B2B guest account removed after the Virtual Network rule is created. Therefore the access I was granting in my tenant was temporary.


Here is the PowerShell script. It's designed to pass to a customer to create the rule on their tenant to authorize my Virtual Network. You'd have to switch things around if you're adding a rule to your SQL Server for your customer's VNet.


In the script, you will see that the user will be prompted for Authentication two times. One is to their own tenant, then to my tenant. The same credentials must be used for both challenges. It creates two tokens and the REST API passes the Authorization Bearer token and an X-ms-authorization-auxiliary Bearer token. 


# All variable/config are set here. All subsequent functions will be run using these
$customerTenantId = "[customer-tenant-guid]"
$customerSubscriptionId = "[customer-subscription-guid]"
$customerResourceGroup = "[resource-group-sql-server]"
$customerSqlServer = "[customer-sql-server]"
$customerRuleName = "Let-Datalineo-In"

$datalineoResourceGroup = "[datalineo-resource-group]"
$datalineoTentantId = "[datalineo-tenant-guid]"
$datalineoSubscriptionId = "[datalineo-subscription-guid]"
$datalineoVnetName = "[datalineo-vnet-name]"
$datalineoVnetSubnetName = "[datalineo-vnetsubnet-name]"
$datalineoVnetSubnet = "/subscriptions/$datalineoSubscriptionId/resourceGroups/$datalineoResourceGroup/providers/Microsoft.Network/virtualNetworks/$datalineoVNetName/subnets/$datalineoVNetSubnetName"

# The customer with Azure AD account of mary@customer.com would need to run this script on their tenant. 
# Their Azure AD account would need permissions to add the rule to the SQL Server
# Interactively authenticate with mary@customer.com credentials to their own tenant
Connect-AzAccount -Tenant $customerTenantId
$customerAccessToken = Get-AzAccessToken -TenantId $customerTenantId -ResourceUrl "https://management.azure.com/"
$customerToken = $customerAccessToken.Token

# Interactively authenticate with mary@customer.com credentials to datalineo tenant
Connect-AzAccount -Tenant $datalineoTentantId
Set-AzContext $datalineoSubscriptionId
$datalineoAccessToken = Get-AzAccessToken -TenantId $datalineoTentantId -ResourceUrl "https://management.azure.com/"
$datalineoToken = $datalineoAccessToken.Token

$authHeader = @{
    'Content-Type'  = 'application/json'
    'Authorization' = 'Bearer ' + $customerToken
    'X-ms-authorization-auxiliary' = 'Bearer ' + $datalineoToken
}

$body = @{'properties' = @{
    'ignoreMissingVnetServiceEndpoint'='true'
    'virtualNetworkSubnetId'=$datalineoVnetSubnet
  }
} | ConvertTo-Json


$uri = "https://management.azure.com/subscriptions/$customerSubscriptionId/resourceGroups/$customerResourceGroup/providers/Microsoft.Sql/servers/$customerSqlServer/virtualNetworkRules/$customerRuleName" + "?api-version=2021-08-01-preview"

$respone = Invoke-RestMethod -Method Put -Headers $authHeader -Uri $uri -Body $body

$response


Once the Customer added the rule, I was able to use that Virtual Network as a Power Platform VNet Data Gateway. My Power BI dataset could be published to my tenant and refresh from my customer's secured SQL Database! The ultimate result being a secured database and low cost & maintenance way to establish connectivity. One limitation is that Power BI Dataflows do not support VNet Data Gateways, but Power Platform dataflows do!


It took some time to get this working, so I hope this saves you time too!

bottom of page