Oracle Managed ODP.NET can't find tnsnames.ora

Question

My managed ODP.net webapp works locally but when deploying it to a server, it fails with the error:

"TNS:listener does not currently know of service requested in connect descriptor"

From looking around, it seems like this is because it can't get to the tnsnames.ora file.

I have tried the following with no success:

Placing a tnsnames.ora file (the same one that works locally) into an [oracle home][product]...\network\admin folder.
Setting a TNS_ADMIN setting in the Managed ODP's web.config section pointing to the environment variable.
Setting the TNS_ADMIN setting in the Managed ODP's web.config section pointing directly to the tnsnames.ora file.

On the server, attempting to run tnsping yields error TNS-03502: Message 3502 not found; No message file for product=NETWORK, facility=TNS

What am I missing?

Can you show us a censored version of your connection string and tsnames.ora? — Tom Halladay, Mar 07 '13 at 21:59
seems that your oracle client omn the server not installed correctly. can it be the issue? — evgenyl, Mar 28 '13 at 21:51

score 14 · Answer 1 · answered Apr 08 '13 at 19:46

14

Try using a connection string that doesn't depend on tnsnames.ora, such as:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

answered Apr 08 '13 at 19:46

Branko Dimitrijevic

50,809
10
93
167

We have some servers with multiple oracle clients installed and this answer saved us big time when having 10g and 11g installed on the same box. – marty Dec 24 '13 at 15:16
7

This doesn't answer the question asked, though. – Kenneth K. Dec 06 '17 at 15:57

score 8 · Answer 2 · answered Jul 09 '15 at 15:34

8

Just adding the tns_admin path to web.config or app.config and point it to the folder where you have a tnsnames.ora file should work.

<oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="tns_admin" value="E:\oracle11\product\11.2.0\client_1\network\admin" />
      </settings>
    </version>
</oracle.manageddataaccess.client>

answered Jul 09 '15 at 15:34

LcRok

91
1
4

1

Sometimes I have to use forward slashes in the tns_admin setting. Call it superstition, but seems to help solve my issues. – C. Tewalt Sep 13 '17 at 18:34
Superstition has no place in dealing with computers. – EdStevens Feb 15 '21 at 20:54
This answer along with https://stackoverflow.com/questions/25508402/odp-net-connection-exception solved my issue (remove LDAP from NAMES.DIRECTORY_PATH in the sql.ora file) – shawad Jul 05 '21 at 12:52

score 1 · Answer 3 · answered Mar 13 '18 at 22:45

I was after the same exact thing I ended up doing some regex on the TNSNAMES file. Once you've done the regex on the file you should be able to bring that into an object in Powershell or C#

param($tnsnamesPath = 'c:\tns\tnsnames.ora',$username = 'user',$password = 'gotmehere', $connectionName = 'mustard', $query = 'Select sysdate from dual')
$simplySQLPath = (Get-Module -ListAvailable simplySQL).ModuleBase
if($simplySQLPath -and (test-path $tnsnamesPath -PathType Leaf) -and (![string]::IsNullOrEmpty($node)))
{
    [System.Reflection.Assembly]::LoadFile("$simplySQLPath\DataReaderToPSObject.dll") | OUT-NULL
    Import-Module SimplySql -Force
    $parsedTN = (get-content $tnsnamesPath -raw)  -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='  
    $splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)' 
    $tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
    $connstring = "$tnsnames;User Id=$username;Password=$password"
    try
    {
        Open-OracleConnection -ConnectionString $connstring -ConnectionName $connectionName
        $result = Invoke-SqlQuery -ConnectionName $connectionName -Query "$SQLQuery"
        Close-SqlConnection -ConnectionName $connectionName
    }
    catch
    {
        $_.exception
    }

}
Else
{
    if(!(test-path $tnsnamesPath -PathType Leaf -ErrorAction Ignore))
    {
        Throw "Check TNSnamesPath:  $tnsNamesPath"
    }
    else
    {
        Throw "Exeception SIMPLYSQL not found in module Path $($env:PSModulePath)"
    }
}
$result

I've blogged about this code here: https://powershellposse.com/2018/03/13/tnsnames-file-parsing/

score 0 · Answer 4 · answered Oct 12 '15 at 08:41

An old post, but I've been looking for a similar solution.

It occurs to me that as it looks like ODP.net doesn't allow for specifying a TNS file path, then if you are aware of the file path, just read the file programmatically and set the contents to the DataSource field of the ConnectionStringBuilder. Not ideal, but a reasonable workaround.

Oracle Managed ODP.NET can't find tnsnames.ora

4 Answers4

Linked