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

Solution 1

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

Solution 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.

Solution 3

The difference is:

• When you are debugging in Visual Studio you are in the security of the logged in user (you)
• When you are running in IIS you are in the security context of the identity of the application pool. Default is Network Service.

Solution 4

In my case, I use Windows 7, IIS 7, Visual Studio 2010. For some reason I have installed 2 Oracle clients, 10.2 and 11.2 (I have 2 TNSNAMES.ORA in two differents path). When I use F5, Visual Studio use one TNSNAME and when I use IIS, It use the other TNSNAME!

If I use CMD and write:

tnsping ORCL

It give me the Oracle vertion I use is: 10.2 and not 11.2. I try turn off the Windows Firewall and not work. I try login in IIS->AppPool with my Windows Account and either not work.

Finally to fix (after 2 days reading a lot of solutions in stackoverflow and others):

• I give read access to both path C:\oracle\product and C:\app\user\product to Everyone account user, because I not found Network Service account.

• In IIS -> AppPool, I set ApplicationPoolIdentity to my Pool.

• In IIS I recycle the AppPool and Restart my WebSite.

and It Works!

Solution 5

I'd configured my app pool to run in the context of ApplicationPoolIdentity as @Jaanus describes.

The virtual account "IIS AppPool\MyAppPoolName" had been granted read and execute permission on the Oracle folder, however, permissions weren't being inherited from this folder. I had to traverse the Oracle folder structure to see where permissions inheritance was stopping and explicitly enable it from that point.

I found the problem using Process Monitor from SysInternals / Microsoft:

1. Start capturing
2. Run the code that accesses the Oracle resources and wait for the exception to be raised by Visual Studio.
3. Stop capturing.
4. Filter the Result field for the string "ACCESS DENIED".

View more solutions

Comments

[Fabio]

Yeah, I granted access to the C:\oracle\instantclient_10_2 directory, and it has worked! Thanks!

[SUN Jiangong]

How to check network service has access to TNS directory or not ?

[Jap Evans]

My app pool runs with ApplicationPoolIdentity and i have given Network service access TNS directories. I am still getting same problem. Please help

[Hernaldo Gonzalez]

In my case, I use Windows 7, IIS 7, Visual Studio 2010, I have installed 2 oracle clients 10.2 and 11.2 (I have 2 TNSNNAMES.ORA in two differents path). When I use F5, Visual Studio use 1 TNSNAME, When I use IIS It use other ORA!!, finally I give to both path full access to Everyone acount user and in AppPool in IIS I set AppPoolIdentity, and It Works!.

[stricq]

I changed the Application Pool from ApplicationPoolIdentity to use Network Service and granted permission to Network Service on the Oracle install directory. This worked.

[Refael]

For ApplicationPoolIdentity, See here how to authorize: link

[Ochoto]

Is there any group that contains all the app pools? Otherwise I need to grant permissions each time I add a pool.

[Ochoto]

In case you want to give permissions to all the application pools you can include de IUSR virtual system user to the ORA_HOMENAME_SVCSIDS local group