Use linked server to access self-managed SQL Server databases not in the cloud - ApsaraDB RDS

RDS SQL Server's Linked Server capability enables you to set up a Linked Server between RDS instances and also supports connections with self-managed SQL Server databases outside the cloud, provided network connectivity is established. This feature is useful for cross-region data access, data merging and analysis, and data migration and synchronization. This topic explains how to create a Linked Server on RDS SQL Server via a VPN to connect to a self-managed SQL Server database not in the cloud.

Prerequisites

The RDS SQL Server instance must meet the following requirements:
- Instance type: General-purpose or dedicated (does not support shared instances)
- Billing method: Subscription or pay-as-you-go (does not support Serverless instances)
- Instance status: Running
Before deploying the VPN gateway, you need to perform network planning:
- Ensure that the private IP address segments that need to be accessed within the local mobile devices and the virtual private cloud (VPC) in the cloud are not the same. Otherwise, communication cannot occur.
- Ensure that the client can access the network.

Fee

Deploying a VPN gateway will incur fees.

1. Establish a network connection channel between the VPC where the RDS SQL Server is located and the machine not in the cloud through a VPN

1.1. Create a VPN Gateway instance

Log on to the VPN Gateway console.
In the top navigation bar, select the region in which you want to create a VPN gateway.
Make sure that the VPN gateway resides in the same region as the VPC that you want to associate with the VPN gateway.
On the VPN Gateways page, click Create VPN Gateway.

On the buy page, configure the parameters described in the following table, click Buy Now, and then complete the payment.

Parameter	Description

Parameter	Description
Instance Name	The name of the VPN gateway.
Resource Group Select the resource group to which the cluster belongs. You can use resource groups to manage resources within your Alibaba Cloud account by category and group. You can manage permissions, deploy resources, and monitor resources based on groups without the need to separately process each resource. Default Resource Group	The resource group to which the VPN gateway belongs. If you leave this parameter empty, the VPN gateway belongs to the default resource group. You can manage the resource group to which the VPN gateway belongs and resource groups to which other cloud resources belong in the Resource Management console. For more information, see What is Resource Management?
Region	The region in which you want to create the VPN gateway. Make sure that the VPN gateway and the VPC with which you want to associate the VPN gateway reside in the same region.
Gateway Type	The type of VPN gateway that you want to create. Default value: Standard.
Network Type	The network type of the VPN gateway. Valid values: Public: The VPN gateway can be used to establish VPN connections over the Internet. Private: The VPN gateway can be used to establish VPN connections over private networks. Note If you want to establish a VPN connection over a private network, you recommend that you associate a router with the private IPsec-VPN connection. For more information, see Create multiple private IPsec-VPN connections to implement load balancing.
Tunnels	The tunnel mode of the VPN gateway. The system displays the tunnel modes that are supported in this region. Valid values: Single-tunnel Dual-tunnel For more information about the tunnel mode, see [Upgrade notice] IPsec-VPN connections support the dual-tunnel mode.
VPC	The VPC with which you want to associate the VPN gateway.
vSwitch	The vSwitch with which you want to associate the VPN gateway. Select a vSwitch from the selected VPC. If you select Single-tunnel, you need to specify only one vSwitch. If you select Dual-tunnel, you need to specify two vSwitches. After the IPsec-VPN feature is enabled, the system creates an elastic network interface (ENI) for each of the two vSwitches as an interface to communicate with the VPC over an IPsec-VPN connection. Each ENI occupies one IP address in the vSwitch. Note The system selects a vSwitch by default. You can change or use the default vSwitch. After a VPN gateway is created, you cannot modify the vSwitch associated with the VPN gateway. You can view the vSwitch associated with the VPN gateway, the zone to which the vSwitch belongs, and the ENI in the vSwitch on the details page of the VPN gateway.
vSwitch 2	The other vSwitch with which you want to associate the VPN gateway in the associated VPC if you select Dual-tunnel. Specify two vSwitches in different zones in the associated VPC to implement disaster recovery across zones for IPsec-VPN connections. For a region that supports only one zone, disaster recovery across zones is not supported. We recommend that you specify two vSwitches in the zone to implement high availability of IPsec-VPN connections. You can also select the same vSwitch as the first one. Regions that support only one zone China (Nanjing - Local Region), Thailand (Bangkok), South Korea (Seoul), Philippines (Manila), and UAE (Dubai)
Peak Bandwidth	The maximum bandwidth of the VPN gateway. Unit: Mbit/s.
Traffic	The metering method of the VPN gateway. Default value: Pay-by-data-transfer.
IPsec-VPN	Specifies whether to enable the IPsec-VPN feature for the VPN gateway. Default value: Enable. You must enable this feature if you want to establish an IPsec-VPN connection.
SSL-VPN	Specifies whether to enable the SSL-VPN feature for the VPN gateway. Default value: Disable. You do not need to enable this feature for the VPN gateway to establish an IPsec-VPN connection.
Billing Cycle	The billing cycle of the VPN gateway. Default value: By Hour.
Service-linked Role	Click Create Service-linked Role and the system automatically creates the service-linked role AliyunServiceRoleForVpn. VPN Gateway assumes this role to access other cloud resources. For more information, see AliyunServiceRoleForVpn. If Created is displayed, the service-linked role is created and you do not need to create it again.

After the VPN gateway is created, the system assigns an IP address to the VPN gateway to establish an IPsec-VPN connection with the on-premises data center. 创建VPN网关

What to do next

After the VPN gateway is created, you must also create a customer gateway before you can establish an IPsec-VPN connection. The customer gateway is used to register the information about the gateway device of your data center with Alibaba Cloud. The information includes the IP address and autonomous system number (ASN) of the Border Gateway Protocol (BGP). For more information, see Create and manage a customer gateway.

1.2. Create an SSL server

Log on to the VPN Gateway console.
In the left-side navigation pane, choose Interconnections > VPN > SSL Servers.
In the top navigation bar, select the region where you want to create an SSL server.
On the SSL Servers page, click Create SSL Server.

In the Create SSL Server panel, configure the parameters that are described in the following table and click OK.

Parameter	Description

Parameter	Description
Name	The name of the SSL server.
Resource Group	The resource group to which the VPN gateway belongs. The resource group to which the SSL server belongs must be the same as the resource group to which the VPN gateway belongs.
VPN Gateway	Select the VPN gateway that you want to associate with the SSL server. Make sure that the SSL-VPN feature is enabled for the VPN gateway.
Local Network	The local CIDR block that your client needs to access by using an SSL-VPN connection. The CIDR block can be the CIDR block of a virtual private cloud (VPC), a vSwitch, a cloud service such as Object Storage Service (OSS) or ApsaraDB RDS, or a data center that is connected to a VPC over an Express Connect circuit. You can click Add Local Network to add up to five local CIDR blocks. You cannot specify the following CIDR blocks as the local CIDR blocks: 127.0.0.0~127.255.255.255 169.254.0.0~169.254.255.255 224.0.0.0~239.255.255.255 255.0.0.0~255.255.255.255 Note The subnet mask of the specified local CIDR block must be 8 to 32 bits in length.
Client CIDR Block	The CIDR block from which an IP address is assigned to the virtual network interface controller (NIC) of the client. Do not enter the private CIDR block of the client. If the client accesses the SSL server over an SSL-VPN connection, the VPN gateway assigns an IP address from the specified client CIDR block to the client. The client uses the assigned IP address to access cloud resources. Make sure that the number of IP addresses in the client CIDR block is at least four times the maximum number of SSL-VPN connections supported by the VPN gateway. Click to view the reason. For example, if you specify 192.168.0.0/24 as the client CIDR block, the system first divides a subnet CIDR block with a subnet mask that is 30 bits in length from 192.168.0.0/24, such as 192.168.0.4/30, which provides up to four IP addresses. Then, the system assigns an IP address from 192.168.0.4/30 to the client and uses the other three IP addresses to ensure network communication. In this case, one client consumes four IP addresses. Therefore, to ensure that an IP address is assigned to your client, you must make sure that the number of IP addresses in the client CIDR block is at least four times the maximum number of SSL-VPN connections supported by the VPN gateway with which the SSL server is associated. Click to view the CIDR blocks that are not supported. 100.64.0.0~100.127.255.255 127.0.0.0~127.255.255.255 169.254.0.0~169.254.255.255 224.0.0.0~239.255.255.255 255.0.0.0~255.255.255.255 Recommended client CIDR blocks for different numbers of SSL-VPN connections If the number of SSL-VPN connections is 5, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 27 bits in length. Examples: 10.0.0.0/27 and 10.0.0.0/26. If the number of SSL-VPN connections is 10, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 26 bits in length. Examples: 10.0.0.0/26 and 10.0.0.0/25. If the number of SSL-VPN connections is 20, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 25 bits in length. Examples: 10.0.0.0/25 and 10.0.0.0/24. If the number of SSL-VPN connections is 50, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 24 bits in length. Examples: 10.0.0.0/24 and 10.0.0.0/23. If the number of SSL-VPN connections is 100, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 23 bits in length. Examples: 10.0.0.0/23 and 10.0.0.0/22. If the number of SSL-VPN connections is 200, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 22 bits in length. Examples: 10.0.0.0/22 and 10.0.0.0/21. If the number of SSL-VPN connections is 500, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 21 bits in length. Examples: 10.0.0.0/21 and 10.0.0.0/20. If the number of SSL-VPN connections is 1,000, we recommend that you specify a client CIDR block with a subnet mask that is less than or equal to 20 bits in length. Examples: 10.0.0.0/20 and 10.0.0.0/19. Important The subnet mask of the client CIDR block must be 16 to 29 bits in length. Make sure that the client CIDR block does not overlap with the local CIDR block, the VPC CIDR block, or route CIDR blocks associated with the client. We recommend that you use 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16, or one of their subnets as the client CIDR block. If you want to specify a public CIDR block as the client CIDR block, you must specify the public CIDR block as the user CIDR block of a VPC. This way, the VPC can access the public CIDR block. For more information, see the What is a user CIDR block? and How do I configure a user CIDR block? sections of the "FAQ" topic. After you create an SSL server, the system automatically adds routes that point to the client CIDR block to the VPC route table. Do not add routes that point to the client CIDR block to the VPC route table again. Otherwise, SSL-VPN connections cannot work as expected.
Advanced Configuration
Protocol	The protocol that is used by the SSL-VPN connection. Default value: TCP(Recommended). Valid values: UDP TCP(Recommended)
Port	The port that is used by the SSL server. Valid values are in the range of 1 to 65535. Default value: 1194. Note The following ports are not supported: 22, 2222, 22222, 9000, 9001, 9002, 7505, 80, 443, 53, 68, 123, 4510, 4560, 500, and 4500.
Encryption Algorithm	The encryption algorithm that is used by an SSL-VPN connection. If the client uses Tunnelblick or OpenVPN V2.4.0 or later, the SSL server dynamically negotiates with the client about the encryption algorithm and uses the most secure encryption algorithm that is supported by the SSL server and the client. The encryption algorithm that you specify for the SSL server does not take effect. If the client uses OpenVPN of a version that is earlier than 2.4.0, the SSL server and the client use the encryption algorithm that you specify for the SSL server. You can specify one of the following encryption algorithms for the SSL server: AES-128-CBC AES-192-CBC AES-256-CBC none A value of none indicates that no encryption algorithm is used.
Compressed	Specifies whether to compress the data that is transmitted over the SSL-VPN connection. Default value: No. Valid values: yes No
Two-factor Authentication	Specifies whether to enable two-factor authentication for the VPN gateway. By default, two-factor authentication is disabled. If you enable two-factor authentication, you must specify an Identity as a Service (IDaaS) instance and an IDaaS application. After you enable two-factor authentication, the system performs two-factor authentication on your client when an SSL-VPN connection is created between the client and the VPN gateway. The first authentication is performed based on the default SSL client certificate. After the client passes the SSL client certificate authentication, the second authentication uses the username and password of the specified IDaaS instance to authenticate the client. The second authentication does not support the Short Message Service (SMS) authentication feature of IDaaS. The SSL-VPN connection is created only after two-factor authentication is passed. This effectively enhances the SSL-VPN connection security. For more information, see Two-factor authentication. Note If you use the two-factor authentication feature for the first time, you must first authorize VPN to access cloud resources. When you create an SSL server in the UAE (Dubai) region, we recommend that you associate the SSL server with an IDaaS EIAM 2.0 instance in Singapore to reduce latency. You can no longer purchase IDaaS EIAM 1.0 instances. If your Alibaba Cloud account has an IDaaS EIAM 1.0 instance, you can still specify the IDaaS EIAM 1.0 instance after you enable the two-factor authentication feature. If your Alibaba Cloud account has no IDaaS EIAM 1.0 instance, you can specify only an IDaaS EIAM 2.0 instance after you enable the two-factor authentication feature. You may need to update the VPN gateway to associate it with an IDaaS EIAM 2.0 instance. For more information, see Announcement on the change of supporting IDaaS EIAM 2.0 instances for two-factor authentication of SSL-VPN connections.

1.3. Create an SSL client certificate

Log on to the VPN Gateway console.
In the left-side navigation pane, choose Interconnections > VPN > SSL Clients.
In the top navigation bar, select the region of the SSL client.
On the SSL Clients page, click Create SSL Client.

In the Create SSL Client panel, configure the parameters that are described in the following table and click OK.

Parameter	Description

Parameter	Description
Name	The name of the SSL client certificate.
Resource Group	The resource group to which the SSL client certificate belongs. The resource group to which the SSL client certificate belongs must be the same as the resource group to which the SSL server belongs.
SSL Server	The SSL server with which you want to associate the SSL client certificate.

1.4. Download the SSL client certificate

Log on to the VPN Gateway console.
In the left-side navigation pane, choose Interconnections > VPN > SSL Clients.
In the top navigation bar, select the region of the SSL client.
On the SSL Clients page, find the SSL client certificate that you want to download and click Download Certificate in the Actions column.

1.5. Configure the client

Download and install the OpenVPN client.
Unzip the downloaded certificate and copy it to the config folder in the OpenVPN installation directory.
Click Connect to initiate the connection.

1.6. Test the connection

Create an ECS instance in the same VPC.
Use the ping command in the OpenVPN client to test the connectivity of the ECS instance.
Note
- Ensure that the security group rule of the ECS instance allows remote connections from the client. The authorization object is the client CIDR block specified in the SSL server configuration, and the port number of the database service to be accessed locally is specified. For more information, see Security Group Configuration Example.
- If the connection fails, it may be due to a firewall enabled on the local host. Configure the firewall to allow remote connections.

2. Create a linked server on RDS SQL Server

Connect to the RDS SQL Server instance using SQL Server Management Studio (SSMS). For detailed instructions, see Connect to a SQL Server Instance.

Execute the following command in SQL Server Management Studio (SSMS).

DECLARE
  @linked_server_name sysname = N'myTestLinkedServer',
  @data_source sysname = N'10.10.0.10,1433', --style: 10.1.10.1,1433
  @user_name sysname = N'ay15' ,
  @password nvarchar(128) = N'***',
  @source_user_name sysname = N'test',
  @source_password nvarchar(128) = N'***',
  @link_server_options xml
  = N'
        <rds_linked_server>
          <config option="data access">true</config>
          <config option="rpc">true</config>
          <config option="rpc out">true</config>
        </rds_linked_server>
  '

EXEC sp_rds_add_linked_server_for_local
  @linked_server_name,
  @data_source,
  @user_name,
  @password,
  @source_user_name,
  @source_password,
  @link_server_options

连接客户端

The parameters are as follows:

Parameter	Description

Parameter	Description
@linked_server_name	The name of the Linked Server.
@data_source	The IP address and port number of the self-managed SQL Server database, in the format: `<IP>,<port>`.
@user_name	The username of the self-managed SQL Server database.
@password	The password corresponding to the username of the self-managed SQL Server database.
@source_user_name	The username of the current RDS SQL Server database.
@source_password	The password corresponding to the username of the current RDS SQL Server database.
@link_server_options	Other options for the linked server connection.

Execute the following command to test the Linked Server:

SELECT * FROM [myTestLinkedServer].master.sys.servers

测试Linked Server

About Alibaba Cloud

Our Global Network

Quick Start

Global Offices

Olympic Games Paris 2024 New

Stade Roland Garros – Glitz from the Past New

Place de la Concorde – “Breaking” the Barriers New

Vaires-sur-Marne Nautical Stadium – Sports with Sustainability New

International Broadcast Center – Images, Sounds, and Data that Captivate Billions New

Customer Success Stories New

Trust Center

Security & Compliance Center

Cloud Compliance Resources

Security Compliance FAQs

Product & Feature Update New

Cloud Forward

Press Room

Alibaba Cloud e-Magazine New

Alibaba Cloud in Analyst Research

Notice

Go Global Service New

Go Global Alliance with Alibaba Cloud

Asia Accelerator Hot

Information Compliance

China Gateway - MLPS 2.0 Compliance New

China Gateway - Networking

China Gateway - Global Application Acceleration New

China Gateway - Security

China Gateway - Data Security New

ICP Support Hot

China Gateway - Omnichannel Data Mid-End New

China Gateway - Organizational Data Mid-End New

China Gateway - Business Mid-End New

China Gateway - AI Service for Conversational Chatbots New

China Gateway - Online Education

China Gateway - Domain Registration

Work at Alibaba Cloud

Experienced Professionals

Students and Graduates

Free Trial

Pricing

Promo Center

Price Reduction

Pay Less and Deploy More

FinOps

Elastic Compute Service (ECS)

Simple Application Server (SAS)

Elastic GPU Service

Elastic Desktop Service (EDS)

Object Storage Service (OSS)

Cloud Enterprise Network (CEN)

Web Application Firewall (WAF)

Domain Names

Container Compute Service (ACS)

Secure Access Service Edge (SASE)

Intelligent Media Services(IMS)

Edge Security Acceleration (ESA)(Original DCDN)

Intelligent Media Management

DingTalk Enterprise

YiDA

Alibaba Cloud Model Studio

Apsara Prime - For Easy Cloud Product Selection

Alibaba Cloud ECS - Cater All Your Cloud Hosting Needs

1TB CDN—Get Free 1 TB Outbound Traffic Plan Now

Security—Under Attack? Get Free Security Support

Short Message Service - Free Testing is Available

Elastic Compute Service (ECS) Hot

CloudBox

Compute Nest

Dedicated Host Hot

ECS Bare Metal Instance

Elastic GPU Service Featured

Simple Application Server (SAS) Hot

Auto Scaling

Cloud Phone Beta

Elastic Desktop Service (EDS) Featured

Batch Compute

Elastic High Performance Computing (E-HPC)

Super Computing Cluster (SCC)

Function Compute (FC)