We're running a SQL Server instance on AWS RDS. I believe this is a 2017 instance. We also have a couple of Linux EC2 instances running SQL Server (not sure the version, but I assume 2017)
Our RDS instance has linked servers set up to both of the Linux instances. What I've found is that after creating the linked servers, I can query them and they work fine.
This is a QA environment and, to save money, we take all the servers down at night. Then they are started on demand. The problem I'm seeing is that once the servers come back up, I get the following error when trying to query the linked servers:
System.Data.SqlClient.SqlException (0x80131904): An error occurred during decryption.
My best guess is that when the RDS instance comes back up, it appears to be different hardware and I'm guessing there is a decryption key based on that hardware?
I've tested a couple things to eliminate possibilities:
I tested leaving the Linux instances up and just stop/start (not restarted) the RDS instance. This seemed to reproduce the problem. I haven't yet tried the reverse (leave RDS running and stop/start a Linux instance).
I tried dropping and recreating the linked server when I get this error. This seems to fix the problem.
Theoretically, I guess I could use this sp_procoption procedure I've been reading about to run a query at server startup that would drop and recreate all the linked servers. But that seems like the wrong way to fix this.
We could leave our servers running 24x7, though our budget is pretty lean so we'd rather not. And besides, if we had some other reason to stop/start our RDS instance, we still have this problem.
Is there any way to make this work other than just running a drop/create script on the linked servers at server startup?