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

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;";
        OdbcConnectio开发者_运维百科n 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

---

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.

---

It probably does not find the tnsnames.ora file. Make sure that the same environment variables are set when you start the service.

---

Make sure that the service account has permissions to read the tnsnames.ora file. Also in sqlnet.ora try removing NTS from SQLNET.AUTHENTICATION_SERVICES if it is there.

If your are still struggling try using ezconnect to bypass tns names all together ex: //ip.of.server/sid

http://www.orafaq.com/wiki/EZCONNECT

---

John C: A couple of things seemed odd to me. I hope I have not misunderstood your replies.

In the connection string in one of the replies, you got an error about the missing driver. To fix this you will have to mention the provider/driver since you are using the OdbcConnection and not an OracleConnection. The OracleConnection that most people would use would implicitly point the to the driver. I assume you need to service to work across databases so the OdbcConnection is required. The website gives a lot of examples for connection strings and I think you should find something that fits.

I would suggest you try

Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=7001))(CONNECT_DATA=(SERVICE_NAME=myDb)));Uid=myUsername;Pwd=myPassword;

Another thing was the location that your TNS_ADMIN variable pointed to. You had mentioned that you pointed to OraHome. I just wanted to confirm that you pointed the variable to the NETWORK \ ADMIN folder within OraHome. I believe you will have to point to the folder where the file exists.

Finally I would suggest you try and take some of the parameters out of the connection string you have in the original post. It would reduce the variables that can affect the outcome, making it easier to debug. And have the environment variable which are configured for the user as system environment variables. Perhaps the reason you winforms application works is because of a variable defined for the user. I suspect that windows service may only use system variables.

Driver={Oracle in OraHome92};Server=myServerAddress;Dbq=myDataBase;Uid=myUsername;Pwd=myPassword;

---

OK, I'm going to answer my own question here just to keep the final resolution separate from the helpful suggestions that led me to it. The post referenced by Nicola )once I read it carefully) turned out to have the answer. My service was running from Program Files(x86) and the oracle driver can NOT handle ( ) in the path of the calling app.
I moved my service to D:\ServiceTest and it works fine. My problem now is that I don't think our app can be moved out of Program Files (x86).
Thank you all for your help, I appreciate everyone's input but I will have to give the bounty to Nicola, as he pointed me to the post that contained the answer. Thanks again for all your help

---

Verify that the TNS Name definition does not have spaces before the name in the tnsnames.ora

---

I have fixed the problem.

there was a '@' character in the password of my database. After I changed the password and remove that character, the problem has been fixed and the export operation has completed successfully.

This link was helpful in solving the issue.