Fixing SQL Linked Server with Oracle Driver – ERROR – ORA-12154

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.

berlin =
(DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
           (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)));

Fixing SQL Linked Server with Oracle Driver – ERROR – ORA-12154