How do I create a linked server in RDS for SQL Server with the source as RDS?

Short description

Amazon RDS is a managed service, so users don't have sysadmin access. Directly creating a linked server from a GUI results in an error. To create a linked server, use T-SQL.

As a prerequisite, you must have connectivity between RDS for SQL Server and the target SQL Server.

Note: The linked server password and configuration remains intact even after a host replacement.

Resolution

RDS for SQL Server instance to RDS for SQL Server instance

When you create a linked server with RDS for SQL Server as the source to RDS for SQL Server as the target, use the DNS name. This prevents IP address changes due to host replacements or server changes.

In Amazon RDS, IP addresses are dynamic and endpoints are static. So, it's a best practice to use endpoints to connect to your instance. Every Amazon RDS instance has an endpoint. See the following parameters:

• @server: Your linked server name.
• @datasrc: Your RDS endpoint name. For Amazon Elastic Compute Cloud (Amazon EC2) on-premises instance your EC2 on-premises IP address or DNS name.
• @rmtuser: The login name that has access to the target database.
• @rmtpassword: The password for the login name.

Connect to the RDS for SQL Server instance

Connect to the instance using the master login, and then run the following command. Make sure that you use the endpoint and not the IP address. IP addresses of RDS instances might change during a host replacement.

EXEC master .dbo.sp_addlinkedserver @server = N'LinkedServerRDSSQL', @srvproduct= N'', @provider= N'SQLNCLI', @datasrc= N'SQL-2019.ckeixtynaaaj.us-east-1.rds.amazonaws.com'
go
EXEC master .dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerRDSSQL' ,@useself=N'False' ,@locallogin=NULL,@rmtuser =N'linkedserverloginname',@rmtpassword='YourStrongPassword'
go

Test the linked server

1. In Microsoft SQL Server Management Studio (SSMS), connect to the RDS instance.
2. On the View menu, select Object Explorer.
3. Select Server Objects, Linked Servers.
4. Right-click your server name, and then select Test the connection.

Query the linked server

Run the following query:

select * from [LinkedServerName].[Databasename].[schemaname].[tablename]

RDS for SQL Server instance to an EC2 SQL Server instance or an on-premises SQL Server

Create the linked server

Create the linked server with RDS for SQL Server as the source to SQL Server on an EC2 instance or to an on-premises SQL Server.

To create the linked server with the IP address for the remote server, run the following commands:

EXEC master .dbo.sp_addlinkedserver @server = N'LinkedServerRDSSQL', @srvproduct= N'', @provider= N'SQLNCLI', @datasrc= N'10.0.0.152'
Go
EXEC master .dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerRDSSQL' ,@useself=N'False' ,@locallogin=NULL,@rmtuser =N'linkedserverloginname',@rmtpassword='YourStrongPassword'
Go

To create the linked server using the DNS name for the remote server, run the following commands:

EXEC master .dbo.sp_addlinkedserver @server = N'LinkedServerRDSSQL', @srvproduct= N'', @provider= N'SQLNCLI', @datasrc= N'ServerName.datacenter.mycompany.com'
Go
EXEC master .dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerRDSSQL' ,@useself=N'False' ,@locallogin=NULL,@rmtuser =N'linkedserverloginname',@rmtpassword='YourStrongPassword'
go

Test the linked server

1. In Microsoft SQL Server Management Studio (SSMS), connect to the RDS instance.
2. On the View menu, select Object Explorer.
3. Select Server Objects, Linked Servers.
4. Right-click your server name, and then select Test the connection.

Query the linked server

Run the following query:

select * from [LinkedServerName].[Databasename].[schemaname].[tablename]

Configure the linked server using Microsoft Windows Authentication

Note: You can't configure a linked server from RDS for SQL Server to an EC2 instance or to an on-premises SQL Server with Windows Authentication.

Prerequisites

• You must have the domain created and joined with the AWS Managed Microsoft AD.
• The source EC2 SQL Server instance and target RDS SQL Server must have connectivity.

Configure the linked server from an EC2 or on-premises SQL Server to RDS for SQL Server using Windows Authentication

• Log in with your domain login, and then run the following query to create the linked server.

USE [master]
GO
EXEC sp_addlinkedserver    @server=N'LinkedServerToRDSInstance',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'EndpointName';
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkedServerToRDSInstance', @locallogin = NULL , @useself = N'True'
GO

Test the linked server

1. In Microsoft SQL Server Management Studio (SSMS), connect to the RDS instance.
2. On the View menu, select Object Explorer.
3. Select Server Objects, Linked Servers.
4. Right-click your server name, then select Test the connection.

Query the linked server

Run the following query:

select * from [LinkedServerName].[Databasename].[schemaname].[tablename]

Troubleshooting

When you connect from the client, you might receive the following error message:

Msg 18456, Level 14, State 1, Line 21
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

This error occurs from a "double hop". A double hop occurs when one computer connects to another computer to connect to a third computer. Double hops might occur in the following scenarios:

• There isn't a service principal name configuration (SPN) for AWS Managed AD to process the authentication between the client and the EC2 instance.
• The linked server is configured with an endpoint that isn't from your domain, such as the RDS instance endpoint. The authentication method to both EC2 and RDS needs to be KERBEROS.

To resolve this issue, complete the following steps:

Check the authentication method to confirm KERBEROS is being picked when connecting to both RDS and EC2

Run the following query with the domain login from the client:

select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid;

Correct the SPNs for the SQL Server service account that is part of your domain

1. In Active Directory Users and Computers, select example.com, example (domain name), Users.
2. To view the properties, right-click YourServiceAccount.
3. In the Delegation tab, choose Trust this user for delegation to any service (Kerberos only), and then select OK.
4. Restart the SQL server service on the EC2 instance or the on-premises SQL Server.
5. Add the SPN for the service account as shown in the following example command. Replace example with your domain name. Replace ServiceAccountName and the Ec2name domain with the correct values for your domain:

   setspn -A MSSQLSvc/Ec2name.domain.com example\ServiceAccountName
   setspn -A MSSQLSvc/Ec2name.domain.com:1433 example\ServiceAccountName

To verify the newly created SPNs, run the following command:

setspn -l example\ServiceAccountName

Recreate the linked server using RDS example.com endpoint

1. To retrieve the server name, run the following query in RDS for SQL Server:

   select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid;

2. In the output from the preceding command, check the server name column to verify the SPN:

   setspn -l YourServerName

3. The output also shows the registered ServicePrincipalNames for your RDS instance, as shown in the following example:

   MSSQLSvc/ YourServerName.example.com:1433

4. To recreate the linked server with the domain login, run the following command. The data source is the same one that you retrieved from the command output in step 2:

   USE [master]
   GO
   EXEC sp_addlinkedserver    @server=N'LinkedServerToRDSInstance',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'YourServerName.example,com';
   GO
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkedServerToRDSInstance', @locallogin = NULL , @useself = N'True'
   GO

Test connectivity from the client.

For heterogenous linked servers, you can use RDS Custom for SQL Server.

Related information

Implement linked servers with Amazon RDS for Microsoft SQL Server