locked
connecting to Oracle with ASP.NET web.config connection string ?Do I need to install a client? RRS feed

  • Question

  • User-1957998148 posted

    I am able to access 9i and 10g Oracle DBs from other clients on my pc - sqlplus, PLSQL Developer, etc.

    As a simple test I just wanted to connect and access the same oracle DBs through ASP.NET on my same client ...

     my web config  connection entry looks like this (somebody elsewhere said this was working for them):

    <add name="pp2" connectionString="user id=xxx;password=xxx;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xxx.xxx)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=myservicenamet)))"/>

    This asp.net  code :

    <asp:GridView ID="GridView1" runat="server" DataSourceID="pp2Source">

    <Columns>

    <asp:BoundField DataField="yyyy" HeaderText="" SortExpression="" />

    </Columns>

    </asp:GridView>

    <asp:SqlDataSource ID="pp2source" runat="server" ConnectionString="<%$ ConnectionStrings:pp2 %>"

    selectcommand="select * from xxxx" >

    </asp:SqlDataSource>

     

    produces this error:

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    Just to make sure, I remove the block of code and the page works fine. Note SQL Server messages, though trying to connect to Oracle. Also, wondering if I need to identify a provider type??

     

    Thank you for any help or information!

     

    ...

    Update on this, I've tried all of these .. Do I need to install a client or something? Do I need add a namespace? It appears like I'm not telling what provider to use correctly. I can access all of these database with no problem from my client using sqlplus and plssql developer client tools and all expected entries are in my TNSNAMES.

     

    <add name="pp1010" connectionString="Data Source=myinstance;user id=xxx;password=xxxx" providerName="System.Data.OleDb" />

    <add name="pp2" connectionString="user id=xxx;password=xx;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=xxxxxxxx)))"/>

    <add name="cap" connectionString="user id=xxx;password=xxxx;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxxxxxxxxt)))" providerName="System.Data.OleDb"/>

    <add name="o2" connectionString="Data Source=xxxxx;Persist Security Info=True;password=xxx;user ID=xxxx" providerName="System.Data.OracleClient" />

    I've done this before so many time at other shops, but never through the web config. It must be something stupid I'm I'm missing. FYI, not sure if related, but the web.config and this application is doing asp.net Membership. 

     

    Thursday, June 7, 2007 4:23 PM

Answers

  • User-319574463 posted

    You can investigate what the connection string should be like:

    1) Create an empty text file in windows explorer and rename it to X.UDL
    2) Double click on it and the datalink provider dialog will appear.
    3) Select the provider tab. Find the provider for your data access method and click next.
    4) Select your source 
    5) Test the connection and save it.
    6) Compare the contents of X.UDL with your connections string.

    You could try looking on http://www.connectionstrings.com/ for examples of connection strings.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 7, 2007 5:44 PM
  • User-2098386135 posted

    The below settings work for me: 

     

    <asp:SqlDataSource ID="MyDataSource1" runat="server"

    ConnectionString="<%$ ConnectionStrings:ora %>"

    ProviderName="<%$ ConnectionStrings:ora.ProviderName %>"

    SelectCommand="select * from .....">

    </asp:SqlDataSource>

     ---------------------------------------------

    web.config

    <connectionStrings>

    <add name="ora" connectionString="Data Source=ds;user id=user;password=pssw;"

    providerName="System.Data.OracleClient" />

    </connectionStrings>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 7, 2007 6:09 PM

All replies

  • User-319574463 posted

    You can investigate what the connection string should be like:

    1) Create an empty text file in windows explorer and rename it to X.UDL
    2) Double click on it and the datalink provider dialog will appear.
    3) Select the provider tab. Find the provider for your data access method and click next.
    4) Select your source 
    5) Test the connection and save it.
    6) Compare the contents of X.UDL with your connections string.

    You could try looking on http://www.connectionstrings.com/ for examples of connection strings.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 7, 2007 5:44 PM
  • User-2098386135 posted

    The below settings work for me: 

     

    <asp:SqlDataSource ID="MyDataSource1" runat="server"

    ConnectionString="<%$ ConnectionStrings:ora %>"

    ProviderName="<%$ ConnectionStrings:ora.ProviderName %>"

    SelectCommand="select * from .....">

    </asp:SqlDataSource>

     ---------------------------------------------

    web.config

    <connectionStrings>

    <add name="ora" connectionString="Data Source=ds;user id=user;password=pssw;"

    providerName="System.Data.OracleClient" />

    </connectionStrings>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 7, 2007 6:09 PM
  • User1205347182 posted

    I came to step 2, but in that list I didn't get Oracle as an provider.

    Why not  ????

     

    I have installed oracle provider from  “ORACLE_HOME”\ASP.NET\sql\InstallAllOracleASPNETProviders.sql.

    Everything went ok, but I still can't find the provider.

     

    And when I go to the ASP.NET configuration in Microsoft visual studio, on provider flip. I "select a different provider for each feature", end there I choose OracleMembershipProvider and OracleRoleProvider.

     

    Any solution ?

    Please help 

    Tuesday, May 11, 2010 8:38 AM
  • User-319574463 posted

    I came to step 2, but in that list I didn't get Oracle as an provider.

    Was this step 2 of the x.udl test?

    There would appear to be a problem in your data access components. Please go to http://support.microsoft.com/kb/301202, download the component checker and run it.

    You may need to rerun the MDAC installation.

    Wednesday, May 12, 2010 2:39 AM
  • User1205347182 posted

    Yes, it was step 2 of the x.udl test.

     

    I went to http://support.microsoft.com/kb/301202 and download the component checker and run it.

    It said unknown, shall I still run MDAC again ?

     

    And do I need MDAC when I shall user Oracle provider with Oracle db. It's Oracle provider I can't se in the list on step 2 of the x.udl

    Wednesday, May 12, 2010 5:05 AM
  • User-319574463 posted

    I suggest you go to http://support.microsoft.com/kb/231943/en-us and download the appropriate version of MDAC for platform and OS.

    Install the correct version and then rerun the component checker.

    Wednesday, May 12, 2010 7:31 AM
  • User-159225446 posted

    Did you ever get a decent reply on this?

    Why someone is suggesting MDAC doesn't make sense, depending on your OS MDAC is already installed. And is a thing of the past when it comes to .NET providers, etc. MDAC was last updated, I believe, back on WIndows server 2003. But it comes installed and should not be messed with (Windows server 2008 does not need it). MDAC contains a very old Oracle ODBC driver (not supported) and OLEDB driver, none of which are used for .NET. You shoudl be using the Oracle providers though, not MS.

    For Oracle you use the ODAC, latest ODAC1120320_x64 (for 64 bit, I use 11g). So your dev box can have the ODAC or the Oracle client with devloper tools installed. I'm currently using vs2012, but also use vs2010 and 2008.

    In your NET project the namespace is Oracle.DataAccess.

    I just use a typical connection string:

    <add name="dasOracle" connectionString="Data Source={server name};User ID={user id};Password={pswd}" providerName="Oracle.DataAccess.Client"/>

    And on the web server, you install the ODAC (or the Oracle client). And use the Tsname file for connecting to the oracle server.

    MDAC, 2.8 latest:

    http://en.wikipedia.org/wiki/Microsoft_Data_Access_Components

    Wednesday, August 14, 2013 9:04 AM