Configuring Linked Servers Between Two Azure SQL VMs in Different Virtual Networks

Written by Julio Ayapan | Sep 19, 2022 8:03:34 PM

Linked Server is a very useful feature that allows you to connect to another database instance from the SQL Server Database Engine. It enables the database instance to read data and execute commands against the remote database using an OLE DB provider.

The remote server can be supported by an OLE DB provider: SQL Server, Oracle, MySQL, etc.

Linked Server is also available in Azure, but there are certain restrictions depending on the source and target database:

SQL Managed Instances can only connect to MS SQL Server databases (On Premise or any other cloud provider).
Linked Servers in Azure SQL VMs are fully supported. It only requires specific network rules to connect two databases successfully.
Azure SQL DB does not support linked servers at all.
All three Azure SQL options can be the target of a Linked Server, assuming the target is visible from the source.

In this article, I will show you how to successfully set up a linked server between two Azure SQL VMs in different virtual networks.

I’m using the following environment in Azure:

SQL Virtual Machine	SQL VM IP Address	Resource Group	Virtual Network	VM Subnet	Address Space
VNA-SQL1	10.0.0.4	VNARG	VNARG-vnet	10.0.0.0/24	10.0.0.0/16
VNA-SQL2	10.1.0.4	VNARG	VNARG-vnet1	10.1.0.0/24	10.1.0.0/16

Configuring VNET Peering

Virtual Network Peering allows connections between virtual networks within the same Azure region via Azure backbone infrastructure. In this architecture, I will use it to allow communication between VNA-SQL1 and VNA-SQL2.

To create the Virtual Network Peering, I will use Azure Cloud Shell, the interactive environment activated from the Azure portal through a web browser.

To start a Cloud Shell console, select the Cloud Shell button on the menu bar at the upper right in the Azure portal:

Select PowerShell in the left menu, and you will be granted console access:

The process is straightforward. I will create a Virtual Network Peering for each route in the connection. In this case, one VNet Peering for communication from VNARG-vnet to VNARG-vnet1 and one VNet Peering for communication from VNARG-vnet1 to VNARG-vnet.

Let’s start capturing the current VNet information:

$vnet1 = Get-AzVirtualNetwork -Name VNARG-vnet -ResourceGroupName VNARG

$vnet2 = Get-AzVirtualNetwork -Name VNARG-vnet1 -ResourceGroupName VNARG

Then, I will create the two Virtual Network Peering’s defining the Source (VirtualNetwork) and the Destination (RemoteVirtualNetwork):

Add-AzVirtualNetworkPeering -Name SQL1toSQL2 -VirtualNetwork $vnet1 -RemoteVirtualNetworkId $vnet2.Id

Add-AzVirtualNetworkPeering -Name SQL2toSQL1 -VirtualNetwork $vnet2 -RemoteVirtualNetworkId $vnet1.Id

After configuring both Virtual Network Peering’s, VNA-SQL1 and VNA-SQL2 can communicate with each other. Below is a ping test before and after configuration.

From VNA-SQL1 to VNA-SQL2:

From VNA-SQL2 to VNA-SQL1:

Note: By default, Ping (ICMP) is not allowed by Windows Firewall in an Azure SQL VM; you must enable the firewall rule before testing a ping command.

Configuring Linked Server

Once the communication is set up between both SQL VMs, we can configure the Linked Server.

I will configure VNA-SQL2 as a linked server within VNA-SQL1.

In the first step, I must define a network inbound security rule to allow traffic between VNA-SQL1 and VNA-SQL2.

On the Azure portal, I go to VNA-SQL1 Virtual Machine, Networking, and Add the inbound security rule:

Use the following data:

Source: Any
Source port ranges: *
Destination: IP Addresses
Destination IP addresses/CIDR ranges: 10.1.0.0/24 ß VNARG-vnet1, where VNA-SQL2 is located.
Service: MS SQL
Destination port ranges: 1433
Protocol: TCP
Action: Allow
Name and Priority: may be any.

Next, I can configure a linked server by using SQL Server Management Studio or the sp_addlinkedserver (Transact-SQL) statement to create the linked server. If you want to use SSMS in your DB connection, go to Server Objects, Linked Servers. Right click on Linked Servers and “New Linked Server.” Fill the wizard with the desired options to create the connection to the target DB.

I’m using a script to create the linked server; I will briefly explain the used server options. The same options are available from the New Linked Server wizard. From VNA-SQL1, I execute:

USE [master]

EXEC master.dbo.sp_addlinkedserver @server = N'10.1.0.4', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'rpc', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'rpc out', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'10.1.0.4', @optname=N'remote proc transaction promotion', @optvalue=N'true'

USE [master]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'10.1.0.4', @locallogin = N'vna', @useself = N'True'

USE [master]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'10.1.0.4', @locallogin = N'VNA-SQL1\vnaadmin', @useself = N'False', @rmtuser = N'vna', @rmtpassword = N'k90#iU834X1y!'

USE [master]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'10.1.0.4', @locallogin = NULL , @useself = N'False'

In the first line, I’m adding the SQL VM VNA-SQL2 (10.1.0.4) as SQL Server Remote Server.

Then, I define the options:

Collation Compatible: instruct SQL Server to evaluate comparisons on character columns locally.
Data Access: allows access to the remote server.
Dist, Pub, and Sub: Distributor, Publisher, and Subscriber are not defined. They help us to define a Distributor and Publisher in a Replication configuration.
RPC and RPC Out: these options allow Remote Store Procedures calls. False means that we won’t be able to execute SP on the linked server.
Connect Timeout: Defines the maximum number of seconds for a remote server timeout. If 0, the Remote Login Timeout config option of the SQL Server Instance will be used.
Collation Name: Name of the collation of the remote server.
Lazy Schema Validation: if false, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema. If this option is set to true, schema checking of remote tables is delayed until execution.
Query Timeout: time in seconds before a query expires. If 0, the query will run until it is complete.
Remote Proc Transaction Promotion: When this option is false, local transactions will not be promoted to distributed transactions.

Finally, I’m mapping the local logins as follows:

Local Login	Impersonate	Remote Login
VNA	True	VNA
SQL1\vnaadmin	False	VNA

Notes:

When setting Impersonate as true, the same login must exist in the remote instance with the same password.
Linked Server will be available only for the local logins I’m using in the mapping area. VNA and SQL1\vnaadmin.

Testing the Linked Server

The linked server will appear in the folder: Server Objects, Linked Servers, Server Name. The available databases in the linked server will appear inside “Catalogs.”

Note: The linked Server name appears as VNA-SQL2.PRIVATE.VNA.COM (instead of the IP address 10.1.0.4) because I’m using a Private DNS Resolver Service (more info on: How to Configure a Private DNS Resolver)

The VNA-SQL2 server has a database called VNADEMO, a locally executed query is shown below:

I will execute the same query from the VNA-SQL1 server to confirm that the linked server is working.

The syntax to execute statements in a linked server is:

select [columns] from [linkedserver].[database_name].[schema_Name].[object_name]

Linked server is working fine between the two VMs. On Azure, Linked servers are available in SQL Server Database Engine and Azure SQL Managed Instance. They are not enabled in Azure SQL Database singleton and elastic pools.

Additional notes:

Linked server offers the ability to access data from outside of SQL Server. From Azure, there are several methods to reach the target database: Azure Express Routes, Azure VPN solutions, Private Endpoints, etc.
Azure Manage Instances have some restrictions when we work with Azure AD Authentication and Linked Servers (for more information, check Linked Servers).

View full post