AWS Database Blog

Implement Linked Servers with Amazon RDS for Microsoft SQL Server

Linked servers allow Microsoft SQL Server to run SQL Server statements on other instances of database servers. In this blog post, we focus on connectivity to other instances of SQL Server in Amazon RDS, hosted in Amazon EC2, or available from your data centers via a VPN or direct connect. We examine two connectivity scenarios:

  • SQL Server (EC2 or on-premises) to RDS SQL Server
  • RDS SQL Server to SQL Server (RDS, 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 RDS 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 examples following, 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 EC2 SQL Server to RDS 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 (EC2 or on-premises) to RDS SQL Server

For this scenario, an EC2 or on-premises instance of SQL Server is connecting to an instance of RDS SQL Server. This scenario is the simplest one, because you can use either use the SSMS graphical interface or just submit the Transact-SQL statements to create the linked server. The example code here is a connection to an RDS server in the cloud. Variables you should replace are listed in angle brackets. Note that in this example we are impersonating a single 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 (the AWS Directory Service), then you can change the linked server security to something like this:

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 instance of SQL Server was a private instance (that is, not publicly available), then the EC2 instance needs to be in the same VPC as the RDS SQL Server instance for connectivity. If an on-premise connection is required, then routing must be established between the corporate network and the subnet on the VPC that the RDS SQL Server installation is using. If the RDS SQL Server instance is publicly available (that is, available to the public Internet), then this scenario also works with any SQL Server installation.

RDS SQL Server to SQL Server (EC2 or on-premises)

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 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:

-- 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

Or

-- 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 preceding, use a four-part name to reference the remote RDS instance:

SELECT * from RDSPrivate.TestDB.dbo.t1;

If the target server is another RDS 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 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. Thus, if the linked server connection fails to an RDS 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: RDS SQL Server currently doesn’t replicate linked servers to the mirrored database server (or AlwaysOn Availability Group secondary server) in a Multi-AZ deployment. If the linked servers are added before the configuration is changed to add mirroring or AlwaysOn, then the linked servers are copied, but only once. 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 instances of RDS SQL Server.

Now that RDS 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 blog post no longer apply.

Additional note: If a host replacement happens, RDS SQL Server does not restore the service master key. This means that linked servers with passwords will get the error “An error occurred during decryption” when you try to use the linked server. Unfortunately, there is no current notification of a host replacement. The closest you can come is to monitor reboots and failovers via SNS events. You can read more about subscribing to those events on Using Amazon RDS Event Notification. When a reboot or failover happens, you need to manually re-add the linked server login passwords. This is a temporary workaround until a future update to RDS SQL Server addresses this by restoring the service master key after a host replacement.

Additional note: As of July 2020, we now replicate the service master key after a host replacement. Previously, during a host replacement linked servers with passwords would get the error “An error occurred during decryption”. This is now resolved.

 

This post has been updated as of 12/10/2019.

About the Author

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