I have an RDS issue with a linked server (I know its not good, but I've inherited it)
I keep loosing my linked server and we get the error "an error occurred during encryption"
according to my google assistant and a few hours research every time... this occurs when Amazon switch us onto new hardware and the master key does not match the hardware.
the fix is to recreate the linked server
so for a short term fix I planned on creating a little proc that did a simple select from the linked server then if it failed in the try catch block then it recreated the linked server
here's the problem - I can log in and run the proc and it works fine, but I want it in SQL agent and run every hour, but sql agent doesn't have the permissions to create a linked server. It's so locked down by amazon that i'm fighting an uphill battle.
I've tried using "execute as" and every trick I know. the only way I can see is to create a c# service on our local infrastructure that calls the code