Oracle ORA-12154 error on local IIS, but not with Visual Studio Development Server

Question

I've got an ASP.NET application that can connect to Oracle when it's started by Visual Studio Development Server, but when I deploy it to my local IIS, it does not connect, showing the "ORA-12154: TNS:could not resolve the connect identifier specified" error.

My connection string was:

Data Source=ABC; User Id=USER; Password=PASSWORD;

Tt's using TNS. My tnsnames.ora is located at C:\oracle\instantclient_10_2\network\admin, and my ORACLE_HOME system variable is pointing to C:\oracle\instantclient_10_2.

To make it work, I changed the connection string to:

"SERVER=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = server1.theplaceiwork.com)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = server2.theplaceiwork.com)(PORT = 1521))(LOAD_BALANCE = yes))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ABC)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC))));uid=USER;pwd=PASSWORD;"

So TNS is used no longer.

But I'd like to know why I can connect using TNS while debbuging with Visual Studio, but when using IIS I can't.

Any tips?

PS. I can use SqlDeveloper with TNS showing no connection errors.

Thanks

Answer 1

Make sure Network Service has access to your TNS directories or change your App Pool to run as you.

Answer 2

Found answer here:

Granting access to IIS 7.5 ApplicationPoolIdentity

When using "ApplicationPoolIdentity" with IIS 7.5 the corresponding user is (a virtual system user):

IIS AppPool\<AppPoolName>

You can grant this user permissions and check security setting by searching for this user. See the full explanation here:

http://www.iis.net/learn/manage/configuring-security/application-pool-identities

So basically just give permission to Oracle folder to the App Pool virtual user.