Login problem with 4.0.7.1

[Clayton Arends]

Hello,

I recently upgraded DOA from 4.0.2 to 4.0.7.1. Now I have an odd problem when attempting to log in.

When I use the format "username/password" I get the message "ORA-12560: TNSrotocol adapter error". I have reproduced this error in a small test app that only has a TOracleSession and TOracleLogon on it. I can also reproduce it at design-time by setting "Connected" to true.

This happens against a 9i DB and 10g DB from my machine running Oracle 9.2.0.1.0. It also happens on a coworker's machine against the same databases and his is on Oracle 10.1.0.2.0.

With the previous version of DOA this was not an issue for me (which I verified by putting it back in place). Any ideas?

Thanks

 

[Marco Kalter]

Perhaps you can modify sqlnet.ora and add/set trace_level_client=user? The generated SQL Trace file may help us determine what goes wrong.

 

[Clayton Arends]

Hi Marco,

Because of UBB posting issues I need to split the client trace file up. I kept getting an error about parenthesis not valid in HTML code. When I made the post smaller these issues went away.

However, I will point out one interesting item of note. The test app uses TOracleLogon. After an invalid connection attempt is made the username and password fields are populated but the database field is blank. Also, in the client trace you'll see that the database the client is attempting to connect to is "ORCL" which isn't what I'm connecting to. I am connecting to "SHEL6". After I saw this file I tried a logon attempt without the user/pass notation and an empty database and received the same error as if I had used the user/pass notation and a valid database.

Thanks,
- Clayton

 

[Clayton Arends]

Here is cli_3840.trc (part 1):
[03-AUG-2006 09:54:50:125]
--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[03-AUG-2006 09:54:50:125] New trace stream is C:\Source\cli_3840.trc
[03-AUG-2006 09:54:50:125] New trace level is 4
[03-AUG-2006 09:54:50:125] --- TRACE CONFIGURATION INFORMATION ENDS ---
[03-AUG-2006 09:54:50:125]
--- PARAMETER SOURCE INFORMATION FOLLOWS ---
[03-AUG-2006 09:54:50:125] Attempted load of system pfile source C:\oracle\ora92\network\admin\sqlnet.ora
[03-AUG-2006 09:54:50:125] Parameter source loaded successfully
[03-AUG-2006 09:54:50:125]
[03-AUG-2006 09:54:50:125] Attempted load of local pfile source C:\Source\sqlnet.ora
[03-AUG-2006 09:54:50:125] Parameter source was not loaded
[03-AUG-2006 09:54:50:125]
[03-AUG-2006 09:54:50:125] -> PARAMETER TABLE LOAD RESULTS FOLLOW PARAMETER TABLE HAS THE FOLLOWING CONTENTS

 

[Clayton Arends]

Here is cli_3840.trc (part 2):
[03-AUG-2006 09:54:50:125] NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)
[03-AUG-2006 09:54:50:125] SQLNET.AUTHENTICATION_SERVICES = (NTS)
[03-AUG-2006 09:54:50:125] --- PARAMETER SOURCE INFORMATION ENDS ---
[03-AUG-2006 09:54:50:125]
--- LOG CONFIGURATION INFORMATION FOLLOWS ---
[03-AUG-2006 09:54:50:125] Log stream will be "C:\Source\sqlnet.log"
[03-AUG-2006 09:54:50:125] Log stream validation not requested
[03-AUG-2006 09:54:50:125] --- LOG CONFIGURATION INFORMATION ENDS ---

[03-AUG-2006 09:54:50:125] nigini: Count in NI global area now: 1
[03-AUG-2006 09:54:50:125] nigini: Count in NI global area now: 1
[03-AUG-2006 09:54:50:125] nrigbni: Unable to get data from navigation file tnsnav.ora
[03-AUG-2006 09:54:50:156] niotns: niotns: setting up interrupt handler...
[03-AUG-2006 09:54:50:156] niotns: Not trying to enable dead connection detection.
[03-AUG-2006 09:54:50:156] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRA M=C:\Source\Project1.exe)(HOST=BENNY)(USER=Owner))))
[03-AUG-2006 09:54:50:156] nscall: connecting...
[03-AUG-2006 09:54:50:203] nsopen: opening transport...
[03-AUG-2006 09:54:50:218] nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=530, nt[1]=126, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[03-AUG-2006 09:54:50:218] nsopen: unable to open transport
[03-AUG-2006 09:54:50:218] nioqper: error from nscall
[03-AUG-2006 09:54:50:218] nioqper: nr err code: 0
[03-AUG-2006 09:54:50:218] nioqper: ns main err code: 12560
[03-AUG-2006 09:54:50:218] nioqper: ns (2) err code: 0
[03-AUG-2006 09:54:50:218] nioqper: nt main err code: 530
[03-AUG-2006 09:54:50:218] nioqper: nt (2) err code: 126
[03-AUG-2006 09:54:50:218] nioqper: nt OS err code: 0
[03-AUG-2006 09:54:50:218] niqme: reporting NS-12560 error as ORA-12560
[03-AUG-2006 09:54:50:218] niomapnserror: returning error 12560
[03-AUG-2006 09:54:50:218] niotns: Couldn't connect, returning 12560
[03-AUG-2006 09:54:50:265] nigtrm: Count in the NI global area is now 0
[03-AUG-2006 09:54:50:265] nigtrm: Count in the NL global area is now 0

And here is sqlnet.log:

***********************************************************************
Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\Source\Pro ject1.exe)(HOST=BENNY)(USER=Owner))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 9.2.0.1.0 - Production
Time: 03-AUG-2006 09:54:50
Tracing to file: C:\Source\cli_3840.trc
Tns error struct:
nr err code: 0
ns main err code: 12560
TNS-12560: TNSrotocol adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 126
nt OS err code: 0

 

[Marco Kalter]

However, I will point out one interesting item of note. The test app uses TOracleLogon. After an invalid connection attempt is made the username and password fields are populated but the database field is blank. Also, in the client trace you'll see that the database the client is attempting to connect to is "ORCL" which isn't what I'm connecting to. I am connecting to "SHEL6". After I saw this file I tried a logon attempt without the user/pass notation and an empty database and received the same error as if I had used the user/pass notation and a valid database.

What happens if you do not use TOracleLogon, but set the TOracleSession properties directly and set Connected to True?

 

[Clayton Arends]

I thought I covered that in the original post :

I can also reproduce it at design-time by setting "Connected" to true.

However, to answer your question directly this problem happens no matter how I try to log in. In fact, it first came to my attention using my own login dialog. If I set the properties directly then after the failed logon attempt the username and password have been stripped and placed into LogonUsername and LogonPassword respectively and LogonDatabase has been reset to blank.

 

[Marco Kalter]

Can you let me know the username/password@database combination? You can send it by e-mail.

 

[Clayton Arends]

At this point I take it that your attempts to reproduce this problem on your end have failed. If this is the case then I am surprised ... more on that later.

If you believe that this problem has something to do with what the username and password look like then let me assure you that this is not the problem. The problem happens no matter what username or password I use. In fact, it happens even if I use the wrong password.

Or you could be thinking that it is a configuration issue with our servers? Again, this is not the case. This problem happens even if I disconnect myself from the network. Besides, all of our servers are behind a VPN firewall so giving you access would have to be a coordinated effort with IT.

Here is what I think. We purchased the source to DOA a while back. I took a look at Oracle.pas -- specifically at the 'DecodeUsername' method. I compared 402 to 407 source and found that in the earlier version this function builds a "user/pass@db as connectas" string and then calls 'DecodeConnectString' to repopulate FUsername, FPassword, FDatabase and ConnectAs. The 407 source (and 406 for that matter) no longer builds the connection string before calling 'DecodeConnectString'. It simply passes whatever the value of FUsername is.

The logic of 'DecodeConnectString' will return False if this string only contains the username but returns True if the string contains at least one of the other fields (password, database, connectas). When true is returned the FUsername, FPassword, FDatabase, and ConnectAs are all set to whatever the function sets the parameter arguments to ... which is an empty string for all of the other fields that were not present.

I verified this theory in the IDE by setting the LogonUsername to user@db and turned Connected to True. After the empty password error, LogonDatabase contained the string 'db', LogonPassword is empty, and LogonUsername contained 'user'.

It seems to me that the solution would be to revert to the 402 logic which builds an appropriate string before calling 'DecodeConnectString'. Or only take the values returned from 'DecodeConnectString' which are not empty.

My $.02

 

[Marco Kalter]

My apologies, I have been a bit slow to understand the exact situation . It is only now that I realize that you have entered a value for LogonDatabase, leave the LogonPassword property empty, and set the LogonUsername to /. This does indeed reproduce the error, and this is indeed caused by the DecodeConnectString changes you mentioned.

We'll fix this for the next patch release.