Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've been struggling to implement Oracle's network encryption between two of my test boxes. I think I've finally been able to do it, but I can't seem to find a way to verify that it is indeed enabled.

I know that tracing is enabled by default on the server side, but is there a command I can run on the remote sqlplus instance that will generate a trace and allow me to see if the specific sql is encrypted? Is there a better way to test this?

Thanks everyone!

share|improve this question

migrated from stackoverflow.com Jan 12 '13 at 23:25

This question came from our site for professional and enthusiast programmers.

I too am looking for a better way to do this, but what I've found is that you can enable tracing on the client side and then search the tracefile that was created.

I have this in my client-side sqlnet.ora file:

DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=/tmp
TRACE_FILE_CLIENT=nettrace
TRACE_LEVEL_CLIENT=16

If you're running the client from the same server, you can override TNS_ADMIN to point to a different directory (you may need to copy tnsnames.ora, etc, to that directory as well).

share|improve this answer
2  
Thanks for the heads up. I'm cautiously optimistic on my end at this point. I stood up another box on the same virtual network and used Wireshark to look at two different Oracle*Net connections, one using a non-encrypted service and the other using my TCPS. Looks like it's working, but I'm sure there has to be a better way of doing this... – Naitouk Jan 12 '13 at 19:17

Have you tried checking the view V$SESSION_CONNECT_INFO?

Query:

select NETWORK_SERVICE_BANNER 
from v$session_connect_info 
where SID = sys_context('USERENV','SID');

Results:

NETWORK_SERVICE_BANNER
-----------------------------------------------------------------------------------------------------
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.2.0 - Production
Oracle Advanced Security: encryption service for 32-bit Windows: Version 11.2.0.2.0 - Production
Oracle Advanced Security: crypto-checksumming service for 32-bit Windows: Version 11.2.0.2.0 - Prod 

If you get a row with NETWORK_SERVICE_BANNER like '%TCP/IP%', then you use TCP (without SSL)

If you get a row with NETWORK_SERVICE_BANNER like '%BEQUEATH%, then you use Bequeath (LOCAL=YES)

If you get a row with NETWORK_SERVICE_BANNER is null, then you use TCPS

For the current session, it is easier to select SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual

share|improve this answer
    
Thanks for the reply, Chris! I tried running the above query on both a TCPS connection as well as a standard 1521 TCP connection. While the results were different, both include the "encryption service" banner. I have been able to verify with Wireshark that one of the connections is indeed not in plain-text, so I'm still at a bit of a loss here. – Naitouk Jan 12 '13 at 23:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.