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)
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.
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:-
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 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.