Error/Fix: Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server. (.Net SqlClient Data Provider)

Recently I was working on one user request to create a linked server for Oracle database in SQL Server 2014 instance.

After installing the correct Oracle ODBC drivers, when I tried to expand the Linked server node to browse objects, I was greeted with below error:-

Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “ORACLE_DR”. (.Net SqlClient Data Provider)

Resolution:-

In order to fix the issue, I simply needed to add the connection string provided by user in TNSNAMES.ORA file.

To find the exact location of ORA file, first check for ORACLE_HOME path. In many cases, the System PATH variable itself can provide the location of Oracle client binaries. To get this information, the easy method could be to use Powershell.

dir env:

Once base Drive is identified, like in my case, it is F:\ drive for 1st Oracle binaries path, then I simply needed to find TNSNAMES.ORA file closest to the above binary path. For finding this ORA file, below Powershell command can be used:-

Get-ChildItem -Path f:\ -Recurse -Name *TNSNAMES.ORA*

In short, I added the connection string provided by the user in file F:\app\oracle11g\network\admin\TNSNAMES.ORA. In some cases, we have to reboot SQL Server services as well.

Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.