Windows Service in .net cannot resolve tns service name

Answer 1

Check that the service can access the registry entry with tells oracle where the tnsnames.ora file is located. From memory it is HKLM\SOFTWARE\ORACLE\Key_Client with a TNS_NAMES registry string which is the folder holding the tnsnames.ora file. You can also create the same thing in the HKCU tree.

Oops ! now i'm at work i can see the string name should be TNS_ADMIN. Also try the env variable TNS_ADMIN and make sure there is nothing in HKCU.

Noticed as well you need the sqlnet.ora file in the same folder as tnsnames.ora

Answer 2

Try setting the windows environment variable TNS_ADMIN to the path where the tnsnames.ora file resides and restarting your serivce.

Answer 3

Run Process Monitor to see if it's loading the TNSNAMES.ORA file. I suspect it is, but your name resolution is broken as that user.

If you can log in interactively with the service account, try using tnsping to see if you can connect to the name.

Oracle resolves files in this order (as per Metalink article 114085.1):

1. Oracle Net files in present working directory
2. TNS_ADMIN defined as a user/session environment variable
3. TNS_ADMIN defined as a global environment variable
4. TNS_ADMIN defined in the registry
5. Oracle Net files in %ORACLE_HOME%\network\admin (the Oracle default location)

See which, if any, are being read with Process Monitor.

Answer 4

First you can change the connection string to expand out the tns entry:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.161.50.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOUAT)));Password=password;User Id=username;

Then you get the real error:

And got ‘ORA-06413: Connection not open.’

Which was due to the 'Windows Service' path having brackets in it '(DEV)' or '(UAT)'

ANSWER: Expand out the connection string to exclude the TNS resolution and then make sure that the path to the calling application does not contain brackets '(' or ')'.

Answer 5

bobmcn thank you for posting this information:

"Thank You! This was a real lifesaver. Be careful installing 32 bit apps on a 64 bit server, because they may be installed in C:\Program Files (x86), which will cause the same problem."

This was exactly the issue we were having and your post resolved it for us. It was a true life saver. Thanks again.