Linked Server failure on clustered SQL Server

Question

This question was migrated from Stack Overflow because it can be answered on Database Administrators Stack Exchange. Migrated last month.

I have two clustered Microsoft SQL Servers (SQLA & SQLB) installed, and confirmed that both of the servers have an ODBC connector for a local PostgreSQL server.

From that ODBC connection, I have a linked server created for use in some stored procedures that fails at least once a fortnight with this error message:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "POSTGRESP23"

When troubleshooting the issues, the ODBC connector on both SQLA and SQLB tests successfully from the System DSN menu on the server; the error originates from the linked server.

Currently, to fix this for convenience and lower downtime I am just deleting the linked server and remaking it, pointing it to the same ODBC object. However, this is not a sustainable process.

Can anyone suggest where to look when troubleshooting? As I'm at a loss.

Additional Information

psqlODBC_X64 is installed on both machines already from https://odbc.postgresql.org/

System DSN settings:

enter image description here

Linked Server settings:

EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESP23', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'PSQLPROD'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESP23',@useself=N'False',@locallogin=NULL,@rmtuser=N'postgres',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'remote proc transaction promotion', @optvalue=N'true'

enter image description here

• Dale K: Not really enough to go on there in order to offer assistance. And unfortunately this site isn't suitable for debugging tips.
• NathanM: Hey Dale, i understand where your coming from. i wish i could provide more information. i have searched through event logs for both clustered servers and cannot see anything relevant to this issue. If stack overflow isn't the appropriate place to hopefully catch somebody who has experienced a similar problem, can you suggest where else i can seek advice?
• Charlieface: The linked server definition sp_addlinkedserver would be a good start, along with the ODBC settings. At the moment we have absolutely nothing. Also you say ODBC but I see mention of OLEDB, which is a different type of driver. Have you tried the official ODBC driver odbc.postgresql.org
• NathanM: I have made edits to the post with hopefully the correct information.
• Dale K: Intermittent errors between servers are often caused by network issues.

Answer 1

If error comes "once a fortnight" I would check network first.

Another thought - I do not see a KeepAlive in your list of options. It wont help against real network troubles, but if you network decides to sleep due to inactivity - the KeepAlive will be a solution.