:Create a linked Server from an ECS-based user-created SQL Server database to an RDS SQL Server

Introduction

This article describes how to create a linked Server from an on-premises SQL Server database on an ECS instance to a RDS SQL Server.

Background

Alibaba Cloud reminds you that:

• Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

1. Open the local SQL Server Management studio, select windows Authentication, and click connect.
2. Click create query.
3. Run the following SQL statement to add a linked server:

   exec sp_addlinkedserver @server='[$RDS]', @srvproduct='', @provider='[$SQLOLEDB]', @datasrc='[$Host]' go

   Note:
   

   • [$RDS] is the alias of the Accessed server. You can customize the server name.
   • [$SQLOLEDB] is set to a value consisting of OLE DB characters.
   • [$Host] is the address of the RDS instance.
   • The srvproduct value does not need to be written by default.

4. Run the following SQL statement to add the mapping from login to username:

   exec sp_addlinkedsrvlogin @rmtsrvname='[$RDS]', @useself='false', @locallogin='', @rmtuser='[$Rds_User]', @rmtpassword='[$Password]' go

   Note
   

   • The useself value is written in a fixed way, and the value is false.
   • The locallogin value is the local operation user name. Leave it blank here.
   • [$Rds_User] is the username created in RDS.
   • [$Password] is the Password of the username used to log on to the apsaradb for RDS instance.

5. Run the following SQL statement to query data in RDS:

   select * from rds.[ $DB].schema.[ $Table]

   Note:
   

   • [$DB] is the name of the database.
   • [$Table] is the Table name.

6. A diagram of the complete operation process is shown.

Application scope

• ApsaraDB RDS for SQL Server