I set up a few weeks ago a linked server in my sql server 2016 to my oracle 12c following this guide. It was working properly, but after some windows updates that restarted my machine, it no longer works. I can log into oracle properly through sqlplus and sql developer, but not through sql server. I get the following error:
OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_ORACLE" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_ORACLE".
I restarted the SQL Server service, as well as restarted windows again, but still same error. What could have changed in a single restart? How can I fix it and get the linked server working again?
For some reason, SQL SERVER was properly reading the tnsnames.ora file before the restart, but after the restart it stopped. I had to recreate the linked server, and put the whole connection string in the Data Source field for it to work again.
Sample Data Source found here:
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED)));