I am trying to resolve a problem for a customer. They have a job which calls a stored proc on Server A, which calls a stored proc on Server B, via a linked server called RefreshDB. The linked server references a DNS alias, pointing to Server B.

Recently we migrated all the databases on Server B to ServerC. All is working fine, but when we updated the DNS alias from referencing Server B to Server C, the job on Server A started failing. Basically, the stored procedure fails saying it cannot locate RefreshDB in sys.servers. RefreshDB is the linked server, which does exist in sys.servers. It references the DNS alias.

If I ping that alias from Server A, I get Server C, which is correct. If I change the linked server to reference the actual server name, Server C, instead of the alias, it works. If I change the linked server to point to our test server, Server Test, it works. The problem is with the reference to the live server, Server C.

I have tried deleting RefreshDatabases linked server, and creating a test one called TestLink. TestLink is configured to point to Server C. When I try and run the job it fails saying cannot find RefreshDatabases! But why? I have dropped it! I even used sp_dropservers 'RefreshDatabases'.

If I update TestLink to point to another server, it works. It's just when we reference Server A that we get a failure referencing RefreshDatabases. There is some sort of cached setting that is causing problems here, but I just don't know where! Any thoughts?

  • Are you sure you migrated all the security properly when migrating Server B objects over to Server C? This sounds like the account you are using to establish connectivity over the Linked Server may not have sufficient permissions on Server C to execute the SP. Also, confirm that the RPC Out server option is enabled on the RefreshDB Linked Sever on Server A. During the dropping/recreating of your linked servers, this option may have been left at its default, which is False and disallows remote SP calls. – John Eisbrener Jun 21 '17 at 14:31
  • @doodling-dba did you ever figure out what happened there? We're about to replace a bunch of linked server with CNAMEs, so I'm curious about both your problem, and whether we need to bounce boxes afterwards, etc. Thanks. – mbourgon May 9 at 18:50

Your Answer

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

Browse other questions tagged or ask your own question.