Amazon RDS for SQL Server Linked Servers with named instances connects fail

Question

We are having an issue with Amazon RDS for SQL Server Linked Servers with named instances connections failing with our on-premise SQL Server instance. We know it is not a networking issue since we are able to created a linked server connection to the main database at the same IP address. Are there any other additional parameters that need to be passed for them to work or are they not supported?

Code used to add linked server, source [Implement Linked Servers with Amazon RDS for Microsoft SQL Server](https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/)

EXEC master.dbo.sp_addlinkedserver @server = N'x.x.x.x\InstanceName', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'x.x.x.x\InstanceName';

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'x.x.x.x\InstanceName',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password';

GO

Error being returned:

TITLE: Microsoft SQL Server Management Studio
	------------------------------
	
	The test connection to the linked server failed.
	
	------------------------------
	ADDITIONAL INFORMATION:
	
	An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
	
	------------------------------
	
	SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 
	OLE DB provider "MSOLEDBSQL" for linked server "x.x.x.x\InstanceName" returned message "Login timeout expired".
	OLE DB provider "MSOLEDBSQL" for linked server "x.x.x.x\InstanceName" returned 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: -1)

Answer

Hello,

Based on my understanding of the stored procedure called, a linked server was created for SQL Server (on-premises) to RDS SQL Server. The error messages could indicate  :

- Issue with network configuration between the RDS SQL Server Instance and the SQL Server ( on-premises). Please be sure you are allowing traffic from the IP address of the On-premises , as well as on the port that SQL Server is using to listen for database.

- Or, issue with calling the stored procedure sp_addlinkedserver and sp_addlinkedserverlogin.

I reviewed both called stored procedure and there are no issue detected. Compared to the [documentation](https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/), the stored procedure you provided is accurate. Therefore, please make sure you are allowing network traffic by using the TCP port through the security group for each inbound instance of SQL Server.

To further look into the DB Instance network configuration, please open a support case with AWS using the following https://console.aws.amazon.com/support/home#/case/create or by following steps in link https://docs.aws.amazon.com/awssupport/latest/user/case-management.html and provide details of the DB Instance endpoint, if used, on-premises source IP.

Amazon RDS for SQL Server Linked Servers with named instances connects fail

Relevant content