Linked server not reconnecting automatically after a reboot of the SQL Server

Question

I recently migrated databases from a SQL 2014 server to a SQL 2019 server (Standard edition). I have a linked server set up that uses the Oracle Provider for OLE DB. The linked server works fine until I restart the computer (probably true for bouncing SQL services as well but I haven't confirmed that yet.) Once I restart, I have to go into Linked servers and expand the linked server before any applications that use views or stored procedures that connect to it will function.

I didn't have this problem on any of the prior SQL Servers that I have configured this linked server on. (We've been setting up this linked server for 15 years.)

On the Oracle Provider I have the following options checked (on both old & new servers) Dynamic parameter, Nested queries, and Allow inprocess. The linked server security settings are identical between old and new servers. The Connection Timeout on the new server is set to 30 while on the old server it was 15.

For now, I've set up a notification to come to me when the server is restarted.

Answer 1

Hi @Forbes, Nancy ,
Oracle is really beyond my ability. I found an official link and I hope this can be helpful: https://learn.microsoft.com/en-us/troubleshoot/sql/admin/set-up-troubleshoot-linked-server
And I think you need to check the windows log to see what exactly happened when you open SQL.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

Linked server not reconnecting automatically

Linked server don't "reconnect", the connection is established on access. And I never heard restarting SQL Server causes any issue with Linked Server.
Which error message to you/the client app get?

Answer 3

Hi @Forbes, Nancy ,
yeah, I think now that we can't find any error messages, recreate maybe the best choice. Try recreating and to see if it works. Wish you good luck!

Answer 4

I have been lurking on this thread. I don't want to jump in directly, since I have no experience of working with Oracle.

But there are not really any difference between SQL 2014 and SQL 2019 here that I know. And specifically, the behaviour you describe seem more related to the Oracle provider.

People often run into problems with linked server when they move from one version of SQL Server to another. But the issue is not the version of SQL Server, but they forgot some configuration option when they move to a new machine. (Which you typically do when you move to a new SQL Server version.)

So I would check further. Are all options in sys.servers set the same? Are the registry option set the same? And, furthermore, are there any configuration for the provider itself?

What error message do you get when things fail?

Answer 5

I rebooted the SQL Server this weekend and the linked server connected fine on it's own - no issues. So it is a mystery.

Previously, I did run a script that turns on some sp_configure advanced options but according to my notes I already ran the script when I set up the instance.

Some of the advanced options I turn on with that script include: 'OLE Automation Procedures', 'xp_cmdshell', and 'Ad Hoc Distributred queries'.

I don't think I changed anything on the linked server itself. There are some settings we have to change on the Provider (OraOLEDB.Oracle) to enable Dynamic Parameter, Nested queries, and Allow inprocess. But I think those items were already checked on the provider.

As long as it is working now. Thanks for the input.