Can't connect to Oracle from a windows service (error: ORA-12154: TNS:could not resolve service name (12154) )

Question

LATEST Update (Nov 2 2011 9AM) I tried running tnsping from the service and it WORKS! However i still get error 12154 when i try to connect. I'm completely confused now, i can't understand how tnsping could work fine but the connection is unable to resolve the service name.

For some reason when i run the following code from a windows service (on a timer event) I get the error: ORA-12154: TNS:could not resolve service name (12154)

When i run the exact same code from a windows form app, it connects just fine. Both the service and the app are running under my account, so there is no difference in the account permissions.
I'm baffled as to why the service fails, can anyone shed some light on this please?

string connectionString =     ";DSN=o1;UID=SCOTT;PWD=TIGER;DBQ=ORCL;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;";
        OdbcConnection cnn;
        cnn = new OdbcConnection(connectionString);
        try
        {
            cnn.Open();
            myEventLog.WriteEntry("Connection SUCCEEDED!!!");
            cnn.Close();
        }
        catch (Exception ex)
        {
            string mes = "Connection FAILED!!!" + ex.Message;
            myEventLog.WriteEntry(mes);
        }

Updates:

1) I've tried with both system and user dsn, both have the same behaviour

2) I added a TNS_ADMIN to the system environment variables to make sue it can find the tnsnames.ora file. This did not change the behaviour.

New Updates (Nov 1 2011):

1) A lot of the suggestions have involved putting the ip address of the Oracle server in the connection string to bypass the tnsnames.ora file. Unfortunately the app has to work with a user set oracle connection, so we don't have any of that information. All i have to work with is a DSN. I have to make it connect from a windows service using an Oracle DSN.

New Updates (Nov 2 2011): 1) It looks like the service IS successfully reading the tnsnames.ora file. I ran process monitor and got these lines:

7:52:54.4365217 AM  OracleService.exe   4624    CreateFile          C:\oracle\ora92\network\Names\sdns.ora  NAME NOT FOUND  Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a
7:52:54.4368466 AM  OracleService.exe   4624    CreateFile  C:\Windows\SysWOW64\tnsnames.ora    NAME NOT FOUND  Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
7:52:54.4371203 AM  OracleService.exe   4624    CreateFile  C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
7:52:54.4372693 AM  OracleService.exe   4624    QueryBasicInformationFile   C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS CreationTime: 01/11/2011 3:10:08 PM, LastAccessTime: 01/11/2011 3:10:08 PM, LastWriteTime: 01/11/2011 3:10:42 PM, ChangeTime: 01/11/2011 3:18:44 PM, FileAttributes: A
7:52:54.4372866 AM  OracleService.exe   4624    CloseFile   C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS 
7:52:54.4375418 AM  OracleService.exe   4624    CreateFile  C:\oracle\ora92\network\ADMIN   SUCCESS Desired Access: Read Data/List Directory, Synchronize, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
7:52:54.4375857 AM  OracleService.exe   4624    QueryDirectory  C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS Filter: tnsnames.ora, 1: tnsnames.ora
7:52:54.4376192 AM  OracleService.exe   4624    CloseFile   C:\oracle\ora92\network\ADMIN   SUCCESS 
7:52:54.4377770 AM  OracleService.exe   4624    CreateFile  C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a, OpenResult: Opened
7:52:54.4379306 AM  OracleService.exe   4624    ReadFile    C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS Offset: 0, Length: 337, Priority: Normal
7:52:54.4380061 AM  OracleService.exe   4624    ReadFile    C:\oracle\ora92\network\ADMIN\tnsnames.ora  END OF FILE Offset: 337, Length: 4,096
7:52:54.4380276 AM  OracleService.exe   4624    CloseFile   C:\oracle\ora92\network\ADMIN\tnsnames.ora  SUCCESS 
7:52:54.4385823 AM  OracleService.exe   4624    CreateFile  C:\oracle\ora92\network\ADMIN\ldap.ora  NAME NOT FOUND  Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a

So does anyone have any idea why it might be failing after reading the tnsnames.ora file? Thanks

Answer 1

Environment variables you define in the corresponding dialog are not available to Windows services. One thing you can try is to expand your connection string as described in this post. Other suggestions from the same post may be applicable.