Warning ….super Technical Article 🙂
For all those that encounter the dreaded error below when setting up a SQL Linked Server for Oracle.
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".
So frustratingly…. I encountered this error once. This error happened after my linked server was working for awhile so it was quite unusual. Using the SQL Explorer I could make a connection, just wouldn’t work. The connection in the TNSnames.ora file looked like the below.
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(HOST = berlin.testingserver.com)
(PORT = 1521)
(CONNECT_DATA = (SID = kraus) ) )
So the TNS entry with the SID wasn’t working. Now other connections using the service name were working, just not the entries using SID. So after some research, I found a fix below that worked on StackOverflow. The fix involved setting the data source in the Linked Server. After that, everything worked correctly.
Data Source=(DESCRIPTION=(CID=berlin)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= berlin.testingserver.com)(PORT=1521)))(CONNECT_DATA=(SID=kraus)(SERVER=DEDICATED)));