AWS Database Blog

Implement Linked Servers with Amazon RDS for Microsoft SQL Server

December 2023: This post was reviewed and updated for accuracy.

Linked servers allow Microsoft SQL Server to run SQL Server statements on other instances of database servers. In this post, we focus on connectivity to other instances of SQL Server in Amazon Relational Database Service (Amazon RDS), hosted in Amazon Elastic Compute Cloud (Amazon EC2), or available from your data centers via a VPN or direct connect. To learn about linked server configuration in Amazon RDS Custom for SQL Server, refer to Configure Linked Servers on Amazon RDS Custom for SQL Server. We examine two connectivity scenarios:

  • SQL Server (Amazon EC2 or on premises) to Amazon RDS for SQL Server
  • Amazon RDS for SQL Server to SQL Server (Amazon RDS, Amazon EC2, or on premises)

Until the outbound networking improvement was released, linked server traffic was restricted to a single VPC. That restriction no longer applies. AWS recommends that Amazon RDS for SQL Server installations should generally be only privately accessible (that is, not directly exposed to the internet).

SQL Server Management Studio (SSMS) requires system admin rights to create a linked server using the graphical interface, which isn’t available in an RDS environment. Unfortunately, this approach prevents users from even launching the dialog box to use to create the needed Transact-SQL script. However, calling the stored procedure sp_addlinkedserver and then the stored procedure sp_addlinkedserverlogin directly from a query window lets an administrator add linked servers to the RDS installation of SQL Server.

For each of the following examples, you must allow network traffic by using the appropriate TCP port through the security group for each inbound instance of SQL Server. In other words, if you’re connecting SQL Server on Amazon EC2 to Amazon RDS for SQL Server, you must allow traffic from the IP address of the EC2 instance, as well as on the port that SQL Server is using to listen for database communications.

SQL Server to Amazon RDS for SQL Server

For this scenario, an EC2 or on-premises instance of SQL Server is connecting to an RDS for SQL Server instance. This scenario is the most straightforward because you can use either use the SSMS graphical interface or submit the Transact-SQL statements to create the linked server. The example code here is a connection to an RDS server in the cloud. Replace the placeholder variables according to your use case. Note that in this example, we are impersonating a single Amazon RDS standard user account.

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedtoRDS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'<myserver>.CB2XKFSFFMY7.US-WEST-2.RDS.AMAZONAWS.COM';

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedtoRDS',@useself=N'False',@locallogin=NULL,@rmtuser=N'<username>',@rmtpassword='<password>';

If you are using a common active directory (AWS Directory Service), then you can change the linked server security to something like the following code:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedtoRDS',@useself=N'True';

When the linked server is in place, you then use standard four-part naming to reference a table, view, and so on, on the remote server:

SELECT * FROM LinkedtoRDS.TestDB.dbo.t1;

If the RDS for SQL Server instance was a private instance (that is, not publicly available), then the EC2 instance needs to be in the same VPC as the RDS for SQL Server instance for connectivity. If an on-premises connection is required, then routing must be established between the corporate network and the subnet on the VPC that the Amazon RDS for SQL Server installation is using. If the RDS for SQL Server instance is publicly available (that is, available to the public internet), then this scenario also works with any SQL Server installation.

Amazon RDS for SQL Server to SQL Server

For this scenario, the previous restrictions about being in the same VPC no longer apply. As long as the IP address is reachable or the DNS name can be resolved, RDS for SQL Server linked servers can target any SQL Server.

The code for the call to sp_addlinkedserver now looks like one of these two options. The first option is as follows:

-- Using the IP address for the remote server
EXEC master.dbo.sp_addlinkedserver @server = N’REPLTest2′, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.0.135′;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’REPLTest2′,@useself=N’False‘,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;
GO

Alternatively, you can use the following code:

-- Using the DNS name for the remote server
EXEC master.dbo.sp_addlinkedserver @server = N’REPLTest2′, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’repltest2.datacenter.mycompany.com′;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’REPLTest2′,@useself=N’False‘,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;
GO

Just as we did in the preceding example, we use a four-part name to reference the remote RDS instance:

SELECT * from RDSPrivate.TestDB.dbo.t1;

If the target server is another Amazon RDS for SQL Server installation, AWS recommends that you use the DNS name to protect from IP address changes due to host replacements or server changes. One limitation for all RDS instances is that if the physical server supporting your RDS for SQL Server instance changes (such as when upgrading to a new version of SQL Server or changing the instance type), the private IP address of the RDS instance of SQL Server might change. This change can happen with no alerts or notifications to your administrative team. Therefore, if the linked server connection fails to an RDS for SQL Server instance, and you have chosen to reference the server by IP address instead of DNS name, your first troubleshooting steps should include verifying that the private IP address of the RDS instance has not changed. To mitigate this risk, AWS recommends that you use the DNS names rather than IP addresses for linked server connectivity.

Note that Amazon RDS for SQL Server currently doesn’t replicate linked servers to the mirrored database server (or Always On availability group secondary server) in a Multi-AZ deployment. If the linked servers are added before the configuration is changed to add mirroring or Always On, then the linked servers are copied, but only one time. Alternatively, you can create the linked servers on the primary instance, fail over to the high availability server instance, and then create the linked servers again so that they are on both RDS for SQL Server instances.

Conclusion

Now that Amazon RDS for SQL Server has the outbound networking improvement, linked servers are fully supported for SQL Server targets, and the previous limitations described in the older version of this post no longer apply.

About the Author

Richard Waymire is the Outbound Principal Architect for RDS SQL Server at Amazon Web Services.

Sugeshkumar Rajendran is a Senior Data Architect at AWS Professional Services, offers customers architectural guidance and technical support, facilitating their transition to the cloud and modernization. He values quality time with his family and enjoys extended drives.

Audit History

Last reviewed in December 2023 by Sugeshkumar Rajendran | Sr. Data Architect