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

Kele

Is IIS sitting on the same machine as VS? – Dierdre

Yes, it's a development machine (Win2003). – Kele

Answer 1

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

Wolf

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

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

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

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!. – Convey

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

For ApplicationPoolIdentity, See here how to authorize: link – Deliberation

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.

Plait

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

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 – Ostensible

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

Averett

Answer 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!

Convey

Answer 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".

Candless

Answer 6

Posting my case because it took entirely too long to figure out.

I didn't have access to adjusting the folder permissions and setting the Application Pool Identity did nothing.

Ended up having to edit the Anonymous Authentication credentials of the individual site to my own and the application pool to Network Service. Hopefully this will help someone in a similarly frustrating situation one day.

Erleena

Answer 7

If you are using ApplicationPoolIdentity, Make sure ApplicationPoolIdentity has access to your TNS directories.

See here how to authorize: IIS7 Permissions Overview - ApplicationPoolIdentity

Deliberation

Answer 8

I had this issue and tried everything above with no luck. So posting one more thing that was overlooked that could be at cause. Your sqlnet.ora file (same directory as the tnsnames.ora) must exist and have TNS properly configured:

#SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH = (EZCONNECT,TNSNAMES)

EZCONNECT is optional but it's very useful in some situations, so I included it.

It was this last thing that enabled my app to work. Hope this helps.

Saval