Originally posted in ,
I know I'm coming at this from the MSSQL end of the chain, but am getting nowhere on MS forums, so hoping someone can take a crack at it.
A system running Win 2008 with MS SQL 2008. Has a linked server (several, actually) referencing oracle databases. It uses the oracle OLEDB provider. This particular server has two oracle homes, one for an old client install and one for the OTG, to enable db links from oracle to MSSQL. (The gateway is not part if the current issue. Here we are working with MSSQL to Oracle.). Since there are two ORACLE_HOMEs, there was also a setting of local environment variable TNS_ADMIN, pointing to E:\app\oracle\tns_admin -- a directory created solely to be the target of tns_admin and hold tnsnames.ora and sqlnet.ora.
Now, because of a spate of server migrations coming down the pike, we decided to implement a single, central tns admin. We set tns_admin=\\myserver\myshare\tnsadmindir, where myserver is a file server. I know some will complain that oracle does not officially support this, but I've done it many, many times and it has never been an issue. Using both tnsping and sqlplus on the MSSQL server, we can confirm that they use the shared tns files. But when we opened MS SQL Management Studio and tested the linked server, it threw errors. Eventually we got it working after a full restart of the entire Win 2008 server. Unfortunately we did not keep careful notes of exactly where we stood and test results along the way, but the overall conclusion was that MSSQL / OLEDB for oracle was not relocating/re-reading tnsames on every request, but appeared to be caching some information.
But now things get really weird. Once we had everything working on the db server, We opened Management Studio from a desktop. From there, referencing the same SQL Server and its linked server, we got ora-12154. On the desktop machine, we could have tns_admin pointing to either a local directory or to the shared location at \\myserver\myshare\tnsadmindir. Didn't matter. The only way we could use one of the linked servers on the MSSQL machine, from another machine, is if the MSSQL machine is using a local tnsnames file.
Bottom line is both I and my MSSQL guy are at a loss. There appears to be something about the oledb provider that is handling the locating and use of the tns config files differently than any other client modules.