This discussion is archived
5 Replies Latest reply: Sep 24, 2013 2:24 PM by landyman RSS

TNS Less Connection

185706 Newbie
Currently Being Moderated
*For some reason on my production server I can not get a connection to the database using TNSnames --*
TNSping Works and the TNSNAMES.ORA is correct.
And it works fine on my test server.

So I hard coded the server IP in the connection string

<connectionStrings>
          <add name="ORAConnectionString" connectionString="Data Source=XXX.XXX.XXX.XXX:1521;User ID=MyUser;Password=MyPassword;" providerName="Oracle.DataAccess.Client"/>
</connectionStrings>

But my boss hates it because I'm not using TNSNAMES proper..

<connectionStrings>
<add name="ORAConnectionString" connectionString="Data Source=MyTNSnames;User ID=MyUser;Password=MyPassword;" providerName="System.Data.OracleClient"/>
</connectionStrings>


Does anybody know how to resolve this issue?  Is there a way to point the TNSNAMES.ORA file from within the CoonectionString
  • 1. Re: TNS Less Connection
    Tridus Journeyer
    Currently Being Moderated
    From the connection string? Not that I'm aware of.

    Unmanaged (standard) ODP.net can set a TNS path as an environment variable, or in the registry. See this for some more info: http://www.dba-oracle.com/t_windows_tnsnames.ora_file_location.htm

    The managed beta has a web.config/app.config setting that lets you set the path, which is quite handy.
  • 2. Re: TNS Less Connection
    ReubenC Explorer
    Currently Being Moderated

    What's the mesage?  Are there multiple Oracle homes on the web server?

  • 3. Re: TNS Less Connection
    UserAL1178M Explorer
    Currently Being Moderated

    185706 wrote:

     

    <connectionStrings>
    <add name="ORAConnectionString" connectionString="Data Source=MyTNSnames;User ID=MyUser;Password=MyPassword;" providerName="System.Data.OracleClient"/>
    </connectionStrings>

    This connection string looks ok. Just put in your data source your TNS name. Does it give any error?

  • 4. Re: TNS Less Connection
    landyman Newbie
    Currently Being Moderated

    Use the fancy new app/web.config method using 12c fully managed provider. It's very nice:

    EDIT: forgot to add this.. it has to be right after <configuration> on line 2

    <configSections>

        <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />

        <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />

      </configSections>

      <oracle.manageddataaccess.client>

        <version number="*">

          <settings>

            <setting name="TraceLevel" value="0" />

            <setting name="TraceOption" value="0" />

            <setting name="TraceFileLocation" value=".\logs" />

          </settings>

          <dataSources>

            <dataSource alias="orcldb1.blahblah.com" descriptor="(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=host1.blahblah.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2.blahblah.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host3.blahblah.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host4.blahblah.com)(PORT=1521)))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic)(DELAY=50))(SERVER=default)(SERVICE_NAME=MY_AWESOME_SERVICE)))"/>    

          </dataSources>

        </version>

      </oracle.manageddataaccess.client>

     

    <connectionStrings>

        <add name="MyOracleDBString" connectionString="user id=oracleuser;password=secret;data source=orcldb1.blahblah.com"/>

    </connectionStrings>

     

    And you can of course encrypt the connectionStrings part which has your credentials, while still knowing what your "TNS" looks like. Managed provider checks this before looking for existing TNS files.

     

    The new Managed provider in 12c is like discovering the wheel for us .NET folks.

  • 5. Re: TNS Less Connection
    landyman Newbie
    Currently Being Moderated

    Also, to answer your original question, the answer is yes. You need to edit the MACHINE.CONFIG file ( c:\Windows\Microsoft.Net\Framework[64]\[Version]\Config\machine.config ). Mine, on my local developer workstation, looks like this:

     

      <oracle.manageddataaccess.client>

        <version number="4.121.1.0">

          <settings>

            <setting name="tns_admin" value="c:\oracle\tns" />

          </settings>

        </version>

      </oracle.manageddataaccess.client>

     

    I always have ORACLE_BASE setup as c:\oracle with one folder \tns that contains my tnsnames instead of using one within each provider. it seems dumb to have 5 tnsnames.ora files if you have 5 clients installed (not that I have 5 clients installed....)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points