Create a Linked-Server from SQL Server 2008 R2 to AWS RDS SQL Server

Question

How do I correctly create a linked-server entry, from an on-prem SQL Server, to an AWS RDS SQL Server instance? On-prem is 2008 R2, AWS-RDS is running 2012.

This is the error I get when attempting to SELECT from a table in the linked-server:

OLE DB provider "SQLNCLI10" for linked server "RDS-Q1-LINK" returned message "Unable to complete login process due to delay in opening server connection". Msg 7303, Level 16, State 1, Line 30 Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "RDS-Q1-LINK".

I have tried following instructions here and people in the thread say they've got it working (from on-prem to AWS, not the reverse, which I don't care about), but nobody specified exactly how they did it or what work-arounds or gotchas they encountered. Another article says the same thing -- they've "had success LINKING TO RDS" but they fail to specify how.

Answer 1

On SQL Server 2008 R2 the default value of remote login timeout (s) is five (5) seconds, I believe, though it is configurable.

Since you are now trying to connect to an AWS RDS SQL Server, have you considered whether the remote login timeout (s) is appropriate for this environment?

Try raising the timeout a bit. You might start with 10 seconds:

sp_configure 'remote login timeout (s)',10;
RECONFIGURE;

I have had to bump up the timeout seconds in some environments, so do not be afraid to try this. (It is equally simple to set it back to the previous default.)

You can try to configure a longer timeout, if needed. So far 10 seconds has almost always worked for my environments, but I have gone to 20 seconds a couple of times.