Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

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

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

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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