We are at the beginning stages of evaluating SQLNET encryption. To understand the problem, I wanted to validate what TNS data appears in the clear between the database server and a SQL client session. I started Wireshark and applied a filter for the IP address of the database server and a TCP port of the DB's SQLNET port. I captured packets from basic SQL queries. I decoded the TNS packets. I see the SQL query and I see the data, or not really, which leads to my question. Why do I see "ORA-01403: no data found" in the decoded packet while I see a row in my SQL session? Thank you for your help.
SQLNET data is not encrypted except for authentication (i.e. connect). The only way to encrypt requires the Advanced Security Option.
As for your ORA-01403, that could be from some recursive SQL on internal tables. You're best to work that out with SQL tracing in the database or SQLNET tracing which will also show you that the traffic is not encrypted except for certain authentication actions. Use SQLNET tracing instead of wireshark.
And to my "authentication" point: that's why you should change passwords using the sqlplus "password" command instead of "ALTER USER IDENTIFIED BY". The former will be encrypted in SQLNET while the later will not!
Thank you very much for your help, and useful guidance on how to change passwords.
I figured out the "ORA-01403: no data found" in the packet payload - this only means "that's the end of the data you requested" (or something like that).
As for why I thought I was seeing nothing in Wireshark - it appears that Wireshark balks at numbers in the SQLNET payload. I base this statement on the fact that the number appeared in the packet payload after I converted it to a character in the SQL.
It's not just Wireshark; same result in Netmon. In fact, both drop numbers and dates unless converted to character.
And it's not just Wireshark or Netmon - SQLNET tracing produces the same results: numbers and dates do not show in the trace results unless you convert them to character. Is this expected?
Yes, ASO to encrypt SQLNET.
ora-1403 is returned at the end of every select statement, it indicates the end of the result set and is handled internally by the client (not displayed on the screen),
wireshark (or any low level packet sniffer) is a good way to check for encryption before / after you specify encryption parameters in the sqlnet.ora, for normal ASO
encryption (not ssl) you can also use oracle sqlnet tracing, but if you use ssl (tcps) over sqlnet then the packets are in the clear in the oracle network trace files,
this is because the ssl encryption happens on a lower protocol layer in that case, but of course if you use wireshark you should be able to verify data is encrypted,
Harm ten Napel
Thank you. I understand you to say that the transport occurs at a lower level in the stack than the encryption and that the SQLNET trace occurs also at the lower level. Is this right?
From the point of view of the OSI model, which perhaps does not apply here, sockets are secured at the presentation layer (L6), above the session layer and below the application layer (where HTTP happens).
Does ASO secure sockets at the transport layer, after any SQLNET tracing occurs but before a network sniffer such as Wireshark would trace the packet?