I am trying to connect to an Oracle 9.2 database from an ASP.NET application using the MSDAORA OleDB provider. It was working for a couple of days, then it mysteriously stopped working.
Just to run through my setup.
I have the Oracle client tools installed in a directory calle Ora92. I have granted the ASPNET user full access to this directory and its subfolders.
I have set the following registry settings:
OracleOciLib = oci.dll
OracleSqlLib = orasql9.dll
OracleXaLib = oraclient9.dll
In my tnsnames.ora file, I have the following:
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.8.131.52)(PORT = 1521))
(SERVICE_NAME = oratest1)
In my ASP.NET application, I define the following connection string in my web.config file:
<add key="StagingConnection" value="Provider=MSDAORA.1;Password=password;User ID=username;Data Source=oratest1" />
In the application, I create the connection as follows:
Dim dbConnection As New OleDbConnection(ConfigurationSettings.AppSettings("StagingConnection"))
On the Open line, I get the following error:
ORA-12154: TNS:could not resolve service name
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: ORA-12154: TNS:could not resolve service name
When I try to open the connection in the Visual Studio Server Explorer, I can browse tables and stored procedures. I have copied this connection string from the Server Explorer to web.config. This leads me to suspect that it's related to the ASPNET user's permissions, but I've given it full access to the Ora92 directory.
This is especially annoying as it was working for a few days without any problems.
Thanks in advance for any help.