Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle server I have tried has the ORA-01804 issue. The actual message is "Error while trying to retrieve text for error ORA-01804" but I figure the ORA-01804 is the issue.
TNSPING to the name in @datasrc uses LDAP to resolve and resolves fine. (note: there is no tnsnames.ora file, ldap.ora and sqlnet.ora files were copied from the source server where everything is working.)
TOAD for Oracle has no trouble connecting to the database using that name as well.
SQL Server (source server is 2016, target is 2019, dev machine but same result on actual server):
SQL Server: 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 18363: ) (Hypervisor)
Oracle Database 19c Enterprise Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.0
I had scripted the linked server connection from the server being migrated so everything should be equal (names changed to protect the innocent):
EXEC master.dbo.sp_addlinkedserver @server = N'ORACLE_SERVER', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'orasrv' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLE_SERVER', @useself=N'False', @locallogin=NULL, @rmtuser=N'orasrv', @rmtpassword='redacted'
I'll skip the options, but they are there as well.