Technique to detect connection encryption?

[duffyjp]

I'm connecting to an oracle database which requires encryption via policy rather than technology.

I need to make sure the instant client on each server, dev box, CI worker etc is configured like this:

SQLNET.ENCRYPTION_CLIENT = required

https://docs.oracle.com/database/121/DBSEG/asoconfg.htm#DBSEG9593

I'd like to just abort the connection if it's not encrypted. Right now the only way I know to check is a tcpdump on the port I'm connecting to...

Thanks!

[kubo]

I googled about it. How about this?

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual

[duffyjp]

Thanks! Very interesting. For connections I had misconfigured, I received this error:

OCIError: ORA-12650: No common encryption or data integrity algorithm

Which is good, I don't want the connection to work if it's not encrypted. I fixed my sqlnet.ora and now those connections work. I had an overly restrictive list of SQLNET.ENCRYPTION_TYPES_CLIENT

When I test with the above SQL however, I get "tcp" instead of "tcps" I'm not sure yet if that's a misunderstanding on my part or a configuration problem. I'll update as I find out!

Hrs::Base.connection.exec_query("SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual").first
  SQL (4.7ms)  SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual
 => {"network_protocol"=>"tcp"} 

[duffyjp]

This looks promising: https://dba.stackexchange.com/a/188299

puts Hrs::Base.connection.exec_query("select NETWORK_SERVICE_BANNER from v$session_connect_info where SID = sys_context('USERENV','SID')").rows
  SQL (8.4ms)  select NETWORK_SERVICE_BANNER from v$session_connect_info where SID = sys_context('USERENV','SID')
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: AES256 encryption service adapter for Linux: Version 11.2.0.4.0 - Product
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: SHA1 crypto-checksumming service adapter

I tested all my connections:

Server	Result
1	Oracle Advanced Security: AES256 encryption service adapter for Linux: Version 11.2.0.4.0 - Product
2	Oracle Advanced Security: AES256 encryption service adapter for Linux: Version 11.2.0.4.0 - Product
3	3DES168 Encryption service adapter for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
4	Oracle Advanced Security: AES256 encryption service adapter for Linux: Version 11.2.0.4.0 - Product
5	3DES168 Encryption service adapter for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production

Based on those results, which there are only two distinct responses, I came up with this query: (note the intentional missing "e" on encryption / Encryption.

select NETWORK_SERVICE_BANNER from v$session_connect_info where SID = sys_context('USERENV','SID') AND NETWORK_SERVICE_BANNER LIKE '%ncryption service adapter%'

Hrs::Base.connection.exec_query("select NETWORK_SERVICE_BANNER from v$session_connect_info where SID = sys_context('USERENV','SID') AND NETWORK_SERVICE_BANNER LIKE '%ncryption service adapter%' ").any?

=> true

When I test this on a client that does not have the SQLNET.ENCRYPTION_CLIENT = required flag set, only the servers that have encryption required on their end show an encryption service adapter The .any? line above correctly responds false for those connections.

[kubo]

Thank you for the comments. I have not used encrypted Oracle connections. I just googled it and I've not tested it. Anyway, I closed this issue because it seems be resolved.

[cjbj]

When I worked with our network devs on this doc, NETWORK_SERVICE_BANNER in v$session_connect_info was indeed the recommended way to check the status.

[Nuck101]

Would like to add my thanks to this thread, as it helped me with figuring out whether Oracle's Network Data Encryption was actually enabled or not. It proved really difficult to find the select statement to show this anywhere in Oracle documentation on the subject (unsurprisingly).

[cjbj]

@Nuck101 for what it's worth, I did get a tiny bit of time from the security team to put together this short doc: https://oracle.github.io/node-oracledb/doc/api.html#securenetwork It will apply to Ruby and other tools that use Oracle's C API.