2

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.

2

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.

  • Nice idea! So I would set this option in the on-prem instance, not the RDS, correct? (RDS probably can't even allow you to do so, due to its limitations.. Just not in front of a computer right now so I wanted to double check.) – NateJ Jun 23 '16 at 2:23
  • Correct, the on-prem instance is the one doing the work of the remote login. – RLF Jun 23 '16 at 12:44
  • A good suggestion; unfortunately, not productive, as my setting is already at 20 seconds. Sorry :( – NateJ Jun 23 '16 at 14:32
  • Feel free to try even longer. I am not familiar with the latency of AWS RDS SQL Server. But it does not hurt to try a longer period. – RLF Jun 23 '16 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.