create linked server between rds instances

Question

Hello!
I have two RDS instances (AAAAA e BBBBB). Both instances belong to the same security group in EC2.
In the instance AAAAA created a linked server using the commands:

EXEC master.dbo.sp_addlinkedserver @server = N'BBBBB', @srvproduct= N'', @provider= N'SQLNCLI', @datasrc= N'BBBBB.coz3rhfkaikg.sa-east-1.rds.amazonaws.com'
go
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BBBBB' ,@useself=N'False' ,@locallogin=NULL,@rmtuser =N'admin',@rmtpassword='xxxxxxx'
go

When I try to connect, the following error message returns:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Test connection to linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [53].
The OLE DB provider "SQLNCLI11" for the connected server "BBBBB," returned the message "Login timeout expired."
The OLE DB provider "SQLNCLI11" for the connected server "BBBBB," returned the message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

What needs to be configured to make the connection successful?

Answer

There are a few things to check to allow connecting to a linked RDS instance:

1. Make sure the security group of the source RDS instance allows inbound access from the security group of the target RDS instance on the SQL Server port (typically 1433).

2. On the target RDS instance, make sure the public accessibility is not set to "No". It should be set to "Yes" or at least "Yes, and require SSL".

3. For the linked server configuration, specify the DNS name of the target RDS instance, not the IP address.

4. Make sure the master user on the target RDS instance is allowed to connect from the source instance. You may need to create a specific user for the linked server rather than using the RDS master.

5. Double check that the username and password specified in sp_addlinkedserverlogin are correct.

Some key things to verify:

- Security groups allow communication between the instances

- Public accessibility on target instance is enabled

- Use DNS name not IP for datasrc

- Linked server login has permissions on target

- Linked login credentials are valid

With those checked, the linked RDS connection should work properly. Let me know if you still have any issues!

create linked server between rds instances

When I try to connect, the following error message returns: TITLE: Microsoft SQL Server Management Studio

Test connection to linked server failed.

ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Relevant content