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

Short description

Amazon RDS is a managed service, so users don't have sysadmin access. If you directly create a linked server from a graphical user interface (GUI), then you get 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 remain intact even after a host replacement.

Resolution

RDS for SQL Server instance to RDS for SQL Server instance

When you create a linked server from RDS for SQL Server to another RDS for SQL Server, use the DNS name. This prevents IP address changes from host replacements or server modifications.

In Amazon RDS, IP addresses are dynamic and endpoints are static. Use endpoints to connect to your instance. Every Amazon RDS instance has an endpoint. In the following commands, replace these parameter values:

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

Connect to the RDS for SQL Server instance

Connect to the instance with the master login, and then run the following command. Use the endpoint and not the IP address because 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

Complete the following steps:

1. In SQL Server Management Studio (SSMS), connect to the RDS instance.
2. On the View menu, choose Object Explorer.
3. Choose Server Objects, and then choose Linked Servers.
4. Open the context (right-click) menu for your server name, and then choose Test the connection.

Query the linked server

Run the following query:

SELECT * FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]

Note: Replace LinkedServerName with your linked server name, DatabaseName with the target database name, SchemaName with the schema name, and TableName with the table name.

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'[IP_ADDRESS]'
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 with 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

Complete the following steps:

1. In SSMS, connect to the RDS instance.
2. On the View menu, choose Object Explorer.
3. Choose Server Objects, and then choose Linked Servers.
4. Open the context (right-click) menu for your server name, and then choose Test the connection.

Query the linked server

Run the following query:

SELECT * FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]

Note: Replace LinkedServerName with your linked server name, DatabaseName with the target database name, SchemaName with the schema name, and TableName with the table name.

Configure the linked server with 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:

• Create a domain with AWS Managed Microsoft AD and join your instances to it.
• Verify connectivity between the source EC2 SQL Server instance and the target RDS for SQL Server instance.

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

Sign in with your domain credentials, and then run the following command 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

Note: Replace LinkedServerToRDSInstance with your linked server name and EndpointName with your RDS endpoint.

Test the linked server

Complete the following steps:

1. In SSMS, connect to the RDS instance.
2. On the View menu, choose Object Explorer.
3. Choose Server Objects, and then choose Linked Servers.
4. Open the context (right-click) menu for your server name, and then choose Test the connection.

Query the linked server

Run the following query:

SELECT * FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]

Note: Replace LinkedServerName with your linked server name, DatabaseName with the target database name, SchemaName with the schema name, and TableName with the table name.

Troubleshoot the double hop error

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 (SPN) configuration for AWS Managed Microsoft AD to process the authentication between the client and the EC2 instance.
• You configured the linked server with an endpoint that isn't from your domain, such as the RDS instance endpoint. The authentication method to both EC2 and RDS must be KERBEROS.

To resolve this issue, take the following actions:

Confirm that RDS and EC2 use KERBEROS authentication

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 in your domain

Complete the following steps:

1. In Active Directory Users and Computers, expand your domain, and then choose Users. For example, expand example.com, expand yourdomain, and then choose Users.
2. To view the properties, open the context (right-click) menu for YourServiceAccount.
3. In the Delegation tab, choose Trust this user for delegation to any service (Kerberos only), and then choose 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:

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

   Note: Replace example with your domain name. Also, replace ServiceAccountName and Ec2name with the correct values for your domain.
6. To verify the newly created SPNs, run the following command:

   setspn -l example\ServiceAccountName

Recreate the linked server with the RDS domain endpoint

Note: For heterogeneous linked servers, use RDS Custom for SQL Server.

Complete the following steps:

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, note the server name.

3. To verify the SPN, run the following command:

   setspn -l YourServerName

   Note: Replace YourServerName with the server name from the preceding step.

4. The output shows the registered ServicePrincipalNames for your RDS instance. Verify that the output includes a ServicePrincipalName entry similar to the following example:

   MSSQLSvc/YourServerName.example.com:1433

5. To recreate the linked server with the domain login, run the following command:

   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

   Note: Replace LinkedServerToRDSInstance with your linked server name and YourServerName.example.com with the server name from step 2.

6. Test connectivity from the client.

Related information

Implement linked servers with Amazon RDS for Microsoft SQL Server