4

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:

  1. 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).

  2. 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?

Improve this question
4
  • This is probably the reason: blogs.msdn.microsoft.com/karthick_pk/2012/01/09/…
    – Alex
    Commented Dec 8, 2019 at 21:48
  • 1
    You should also test your prod environment for this issue.
    – Alex
    Commented Dec 8, 2019 at 21:49
  • Thankfully we haven't yet set up linked servers in prod yet. So if I understand correctly, when I create the linked servers, their login info is encrypted using the current service master key. Then when the server is stopped and started again, something must be regenerating a different service master key. I guess the solution must be to somehow backup the service master key before taking down the instance, then restore it when the instance comes back online.
    – Dan
    Commented Dec 9, 2019 at 3:24
  • 1
    And I guess specifically in our case, SQL Server tries to decrypt the service master key using two different methods and both fail (mattsql.wordpress.com/2012/11/13/…). I'd guess the local machine decryption fails because RDS isn't restoring to the same machine. And the service account fails because it's not a domain user, which I'm not sure we can even change on RDS. Sounds almost like linked servers aren't usable on RDS.
    – Dan
    Commented Dec 9, 2019 at 3:34

1 Answer 1

Reset to default
1

Update: July 2020

As of July 2020, we now replicate the service master key after a host replacement. Previously, during a host replacement linked servers with passwords would get the error “An error occurred during decryption”. This is now resolved.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

If a host replacement happens, RDS SQL Server does not restore the service master key. This means that linked servers with passwords will get the error “An error occurred during decryption” when you try to use the linked server. Unfortunately, there is no current notification of a host replacement. The closest you can come is to monitor reboots and failovers via SNS events. You can read more about subscribing to those events on Using Amazon RDS Event Notification. When a reboot or failover happens, you need to manually re-add the linked server login passwords. This is a temporary workaround until a future update to RDS SQL Server addresses this by restoring the service master key after a host replacement.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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