Access a self-managed on-premises SQL Server through a linked server-ApsaraDB RDS(RDS)-阿里云帮助中心

Prerequisites

Your ApsaraDB RDS for SQL Server instance must meet the following requirements:
- Editions: Cluster Edition, High-availability Edition. Basic Edition is not supported.
- Instance types: General-purpose and Dedicated. Shared instance types are not supported.
- Billing methods: Subscription and Pay-as-you-go. Serverless instances are not supported.
Before you deploy a VPN Gateway, plan your network:
- Ensure that the CIDR block of your on-premises client does not overlap with the CIDR block of the Virtual Private Cloud (VPC) that you need to access. Otherwise, communication fails.
- Ensure that the client has internet connectivity.

Billing

Deploying a VPN Gateway incurs fees.

Step 1: Connect your VPC to the on-premises network

1.1. Create a VPN gateway instance

Create a VPN gateway

Newly created VPN gateways support only IPsec-VPN connections in dual-tunnel mode. If you have an existing single-tunnel VPN gateway, upgrade its IPsec-VPN connections to dual-tunnel mode to ensure high availability and benefit from the latest features.

Console

To create a VPN gateway using the console, go to the VPN Gateway buy page and configure the following parameters:

Region: Select the same region as your VPC.
Gateway Type: Select Standard to ensure the gateway uses industry-standard commercial cryptographic algorithms for IPsec-VPN connections.
Network Type: Select Public to assign a public IP address for the IPsec-VPN connection. For private connectivity, use a private IPsec-VPN connection and bind it to a transit router.
Tunnels: Select Dual-tunnel to enhance availability.
- Select the associated VPC and two vSwitches in different availability zones. When IPsec-VPN is enabled, the system creates an Elastic Network Interface (ENI) in each vSwitch. These ENIs serve as the traffic interfaces between the IPsec-VPN connection and the VPC, and each ENI consumes one IP address.
- In regions that support only a single availability zone, zone-level disaster recovery is not possible. To maintain high availability for the IPsec-VPN connection, select two different vSwitches within the same zone.
  
  Regions that support only a single zone
  
  China (Nanjing - Local Region), China (Fuzhou - Local Region), China (Wuhan - Local Region), Thailand (Bangkok), South Korea (Seoul), Philippines (Manila), UAE (Dubai), and Mexico.
- The associated vSwitches cannot be modified after the VPN gateway is created.
Maximum Bandwidth: The maximum supported bandwidth varies by region. If you select 10 Mbit/s or 5 Mbit/s, the inbound peak bandwidth from the on-premises data center to the VPN gateway is limited to 10 Mbit/s.
Enable IPsec-VPN and disable SSL-VPN.
If IPsec-VPN is not enabled during creation, you can enable it later by locating the VPN gateway in the console and clicking Enable in the Feature Configuration column.

API

To create a VPN gateway using the API, call the CreateVpnGateway operation and specify the required parameters.

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 of the SSL server.
The SSL server and VPN gateway must reside in the same region.
On the SSL Servers page, click Create SSL Server.

In the Create SSL Server panel, set the following parameters and click OK.

Parameter	Description
Name	Enter a name for the SSL server.
Resource Group	The resource group to which the VPN gateway belongs. The SSL server is automatically added to the same resource group as the VPN gateway.
VPN Gateway	Select the VPN gateway to associate with the SSL server. Make sure that SSL-VPN is enabled for the VPN gateway.
Local Network	The CIDR block that clients use to access resources through the 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. The following CIDR blocks cannot be used for a local network: 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 prefix length of the local network's subnet mask must be between 8 and 32 bits.
Client CIDR Block	The IP address pool from which the VPN gateway assigns addresses to client virtual network interfaces. This is not the client's existing private network. When a client connects, the VPN gateway assigns it an IP address from this block. The client then uses this 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. 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, use a /27 subnet or larger (e.g., 10.0.0.0/27 or 10.0.0.0/26). If the number of SSL-VPN connections is 10, use a /26 subnet or larger (e.g., 10.0.0.0/26 or 10.0.0.0/25). If the number of SSL-VPN connections is 20, use a /25 subnet or larger (e.g., 10.0.0.0/25 or 10.0.0.0/24). If the number of SSL-VPN connections is 50, use a /24 subnet or larger (e.g., 10.0.0.0/24 or 10.0.0.0/23). If the number of SSL-VPN connections is 100, use a /23 subnet or larger (e.g., 10.0.0.0/23 or 10.0.0.0/22). If the number of SSL-VPN connections is 200, use a /22 subnet or larger (e.g., 10.0.0.0/22 or 10.0.0.0/21). If the number of SSL-VPN connections is 500, use a /21 subnet or larger (e.g., 10.0.0.0/21 or 10.0.0.0/20). If the number of SSL-VPN connections is 1,000, use a /20 subnet or larger (e.g., 10.0.0.0/20 or 10.0.0.0/19). Important The subnet mask of the Client CIDR Block must have a prefix length between 16 and 29 bits. The client CIDR block must 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 their subnets for the client CIDR block. If you must use a public IP address range, you must configure it as a user-defined CIDR block for the VPC to ensure proper routing. For more information, see VPC FAQ. After you create the SSL server, the system automatically adds a route for the Client CIDR Block to the route table of the VPC. Do not manually add this route to the VPC route table. Otherwise, SSL-VPN connections may be disrupted.
Advanced Configuration
Protocol	The protocol 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 (default)
Two-factor Authentication	Specifies whether to enable two-factor authentication for the VPN gateway. By default, two-factor authentication is disabled. Two-factor authentication verifies the identity of a client by using the default SSL client certificate and the username and password of IDaaS EIAM before an SSL-VPN connection is established. The client must pass both authentications before the connection can be created. Two-factor authentication helps prevent user identity theft and unauthorized SSL-VPN connections. It efficiently improves the security of SSL-VPN connections and protects sensitive data in VPCs against data breaches. For more information, see SSL-VPN two-factor authentication. After two-factor authentication is enabled, you can select the IDaaS EIAM instance and IDaaS application ID used for authentication. Click to view the two-factor authentication procedure The client initiates an SSL-VPN connection request. After the VPN gateway receives the request, the VPN gateway verifies the SSL client certificate of the client. After the client passes the authentication, you need to enter the username and password on the client. Then, the VPN software passes the username and password to the VPN gateway. After the VPN gateway receives the username and password, it sends them to IDaaS for authentication. IDaaS verifies the username and password, and returns the authentication result to the VPN gateway. The VPN gateway accepts or denies the SSL-VPN connection request based on the authentication result. 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 navigation pane on the left, 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 SSL client certificate and click OK.

Configuration	Description
Name	Enter a name for the SSL client certificate.
Resource Group	Select the resource group to which the SSL server belongs. The SSL client certificate must be in the same resource group as the SSL server.
SSL Server	Select the SSL server to associate with this certificate.

1.4. Download the SSL client certificate

Log on to the VPN Gateway console.
In the navigation pane on the left, 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 an OpenVPN client.
Decompress the downloaded certificate package and copy the files to the config folder in the OpenVPN installation directory.
Click Connect to initiate a connection.

1.6. Test the connection

Create an ECS instance in the same VPC.
In the OpenVPN client, run the ping command to test the connectivity to the ECS instance.
Note
- Make sure that the security group rules for the ECS instance allow remote client connections. Set the authorization object to the client CIDR block that you specified in the SSL server configuration. You must also specify the service port of the on-premises database you want to access. For more information, see Security group configuration examples.
- If the connection fails, a firewall on the local host might be blocking it. You must configure the firewall to allow remote connections.

Step 2: Create a linked server

Using SSMS

Connect to an ApsaraDB RDS for SQL Server instance by using SQL Server Management Studio (SSMS).

In SSMS, run the following SQL command to create a linked server:

DECLARE
    @linked_server_name sysname = N'yangzhao_slb', -- The name of the linked server, used to identify the remote server.
    @data_source sysname = N'****.sqlserver.rds.aliyuncs.com,3888 ', -- The IP address and port of the self-managed SQL Server database. Format: <IP>,<Port>. Example: 10.1.10.1,1433.
    @user_name sysname = N'ay15', -- The username to connect to the self-managed SQL Server database.
    @password nvarchar(128) = N'******', -- The password for the specified username.
    -- Other options for the linked server, provided in XML format.
    @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>'
;

-- Call the sp_rds_add_linked_server stored procedure to create the linked server.
EXEC sp_rds_add_linked_server
    @linked_server_name,
    @data_source,
    @user_name,
    @password,
    @link_server_options;

Run the following SQL command to view the list of configured linked servers:
```
SELECT * FROM [yangzhao_slb].master.sys.servers;
```

Using DMS

Connect to an ApsaraDB RDS for SQL Server instance by using DMS.

In DMS, run the following SQL command to create a linked server:

-- Call the sp_rds_add_linked_server stored procedure to create the linked server.
EXEC sp_rds_add_linked_server
    @linked_server_name = N'yangzhao_slb', -- The name of the linked server, used to identify the remote server.
    @data_source = N'10.1.10.1,1433',   -- The IP address and port of the self-managed SQL Server database. Format: <IP>,<Port>. Example: 10.1.10.1,1433.
    @user_name = N'ay15', -- The username to connect to the self-managed SQL Server database.
    @password = N'******', -- The password for the specified username.
    -- Other options for the linked server, provided in XML format.
    @link_server_options = N'
        <rds_linked_server>
            <config option="data access">true</config>
            <config option="rpc">true</config>
            <config option="rpc out">true</config>
        </rds_linked_server>'
;

Run the following SQL command to view the list of configured linked servers:
```
SELECT * FROM [yangzhao_slb].master.sys.servers;
```

FAQ

Query performance variation

Scenario: After you enable a linked server between two ApsaraDB RDS for SQL Server instances in the same region and VPC but in different zones, the execution time for the same query statements increases. When you test query performance using different methods, such as querying from an ECS instance in the same zone, querying from DMS, or querying another RDS instance in a different zone by using a linked server, the query performance varies. The following examples show the performance of different query methods:
- Querying an RDS instance by using an SSMS connection: No cross-instance network transmission is involved. This method provides the lowest latency and the fastest speed.
- Querying an RDS instance by using DMS: DMS returns only a small amount of data (up to 3,000 rows are returned). Because the amount of transmitted data is small, the query is fast.
- Querying an RDS instance from an ECS instance in the same zone: The network latency within the same zone is low, resulting in better performance than cross-zone queries.
- Cross-zone query between RDS instances: This involves cross-zone network transmission. The network latency is high and the data volume is large, resulting in poor performance.
Cause analysis: Network latency and data volume affect query performance. Query speeds rank as follows: local query > same-zone query > cross-zone query. The details are as follows:
- Network latency: The network latency of cross-zone queries is higher than that of same-zone queries.
- Data volume: The larger the amount of data transmitted, the more significant the impact of network latency on performance.
Performance optimization suggestions:
- Avoid cross-zone queries whenever possible. Prioritize deploying instances in the same zone. To change the zone of an ApsaraDB RDS for SQL Server instance, see Migrate an instance across zones.
- Reduce the amount of transmitted data and optimize query logic, for example, by adding filter conditions or using paged queries.

Access a self-managed on-premises SQL Server through a Linked Server