Hi,
I hope someone here can help me, as this is proving to be a real chore.
I need a .NET web application to be able to connect to an Oracle Database.
I'm using IIS 7.5 on a Windows 2008R2 Web Server 0x64, and ASP.NET framework 4.0
I know very little about Oracle I'm afraid (I'm a dedicated SQL Server man - sorry!), so I duly donwloaded and installed the 12.1 0x64 instant client.
I followed all the instructions, created the PATH and TNS_ADMIN enivronment variables (set to c:\instantclient_12_1, where the Oracle DLLs are installed), I had a few problems with a missing 0x64 DLL (the visual .net distribution mfc100.dll) bundled with the instant client (since this is a pretty much virgin production Web Server, and I have no wish to install anything I don't need), but eventually I succesfully managed to make an ODBC System DSN connect successfully.
The tnsnames.ora file was supplied to be by the DBA, and is as follows;
xxx =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SID = xxxx)
(SERVER = DEDICATED)
)
)
Great I thought. Now for IIS.
My IIS connection string is of the form;
Dim conn As New Odbc.OdbcConnection("DSN=xxx;Uid=xxx;Pwd=xxx;")
However, whenever I try to open said connection, I get the ubiquitous;
"ERROR [08004] [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specifiedERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"
After much research, I came to realise that I'm not the only person having the above error. It seem to be everywhere.
It means that my IIS application presumably isn't getting to the tnsnames.ora lookup reference right?
OK, I thought, let's add all IIS accounts to the c:\instantclient_12_1 (read and execute). It's IIS not having permissions to access the tnsnames.ora file, but still no joy.
I also tried setting the IIS application pool identity to run under a domain account with extra privalidges, but still the same error.
I tried playing around with various connection string permutations. I tried this one;
Dim conn As New Odbc.OdbcConnection("Driver={Oracle in instantclient_12_1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SID=xxxx)(SERVER=DEDICATED)));uid=xxx;pwd=xxx")
and this one...
Dim conn As New Odbc.OdbcConnection("Driver={Oracle in instantclient_12_1};Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxx)));uid=xxx;pwd=xxx")
Both resulted in a completely different error, but just as ubiquitous, it would seem;
ERROR [HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter errorERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failedERROR [HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error"
In order to try and home in on this, I tried writing a small VB SCript file to access the DSN using the same connection string I first tried;
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = "DSN=xxx;Uid=xxx;Pwd=xxx;"
This worked a treat, and I was able to connect perfectly. In order to try and drill down to the problem, I run this file as the domain user I set IIS to run as, and it worked a treat with the IIS credentials too.
Thus I am at my wits' end. I cannot understand why IIS can't just use the the System DSN.
Thanks to anyone in advance for their help. It is much appreciated.