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?

Solution 1

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;

Solution 2

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>

Solution 3

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/

Comments

[marty]

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.

[C. Tewalt]

Sometimes I have to use forward slashes in the tns_admin setting. Call it superstition, but seems to help solve my issues.

[Kenneth K.]

This doesn't answer the question asked, though.

[EdStevens]

Superstition has no place in dealing with computers.

[shawad]

This answer along with stackoverflow.com/questions/25508402/… solved my issue (remove LDAP from NAMES.DIRECTORY_PATH in the sql.ora file)