Cannot connect to Oracle using TCPS

Toad for Oracle & DevOps Toolkit
1

Our company recently upgraded an Oracle server to use TCPS and a connection I was able to make using Toad is no longer working.

The server changed so we had to update the connection string and import/install 2 certificates using the command line. This worked.

There's a user guide available to explain how to make this work in both DBVisualizer and SQL Developer, but sadly not for Toad. In the SQL Dev guide, it tells to edit the sqldeveloper.conf file and add a few entries of the type "AddVMOption -Djavax.net.ssl.trustStore....". Then the connection to Oracle is made using the "Custom JDBC" option.

I don't see anything equivalent in Toad, and when I try to login, I get "ORA-28759: failure to open file" error. Any ideas ?

2

Hi Daniel,

Do you have an Oracle wallet for this DB?

If you only use this one wallet, you can specify it in your SQLNET.ora file like this:

(Using your own wallet location, of course)

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = C:\Oracle\wallet_azure_21c)
     )
   )

If you have multiple wallets and need to use one or or the other depending on where you are connecting, AND you have a 19c or newer Oracle client, you can specify it in tnsnames.ora like this:

ORCL21C_PLUG_TCPS_WALLET1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 123.456.78.90)(PORT = 2484))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclpdb))
    (SECURITY =
      (SSL_SERVER_DN_MATCH = YES)
      (MY_WALLET_DIRECTORY = "C:\Oracle\wallet_orcl21c")
    )

Bottom line - if you configure your Oracle Client so that SQL*Plus can connect, then Toad will also be able to connect. Toad and SQL Plus use a similar connection protocol. SQL Developer is different.

-John

3

Sorry for the late follow up, I had a long back and forth with our DB support team (which is outsourced). The end result of these discussions is that for some unknown reason, they won't give me the Oracle Wallet for this DB. Could it be that it's for security reasons ? Their recommended solution is to use Oracle's own SQL Developer or DBVisualizer (which doesn't seem to depend on Oracle Wallet) to connect to this TCPS connection.

Is there any security reason that would prohibit our support to provide the Oracle Wallet files for a client connection ?

Thx

4

I believe in this case SQL Developrer and DBVisualizer are using JDBC to connect.

Toad uses the OCI, as does SQL*Plus.

Ask your security people how to configure your Oracle client so that SQL*Plus can connect via SSL. Then just the same Oracle client in Toad and it will "just work".

5

If it helps you -

I have used these steps to configure a database for SSL and then connect to it with Toad. These directions talk about the wallet files that I am referring to. Maybe your security people think you are asking for something else.

(Edit: The article referenced below article specifies SHA1 ciphers, which are outdated. Please see comments further down in this thread on how to make it more secure)

6

It is my opinion that the article is "a way to make it work" and not "a way to make it secure".

Cheers,
Russ

7

Hi Russ,

I'm listening. Why is it not secure as described there? What could be done differently to make it more secure?

-John

8
  1. Add the following to sqlnet.ora on both sides:

SSL_VERSION=1.2
ACCEPT_SHA1_CERTS=FALSE

  1. Do not specify SSL_CIPHER_SUITES so that the client and server can negotiate the strongest cipher suite that both support. That article specifies SHA1 ciphers, which are outdated. When NOT specifying ciphers with "SSL_VERSION=1.2", ONLY FIPS 140-2 ciphers will be used, and SHA1 suites are excluded. (See Oracle Database Security Guide 19c paragraph E.3.2 Approved TLS Cipher Suites for FIPS 140-2)

Cheers,
Russ

9

Ah, good point. Yes, I agree.

I think that caught my eye too when I first saw this article and was following the steps. But just now when I posted that, I forgot all about it.

I appreciate the feedback.

-John

1 Like
10

Hey Daniel,

Were you able to get your set up working?

We have been use TCPS for some time. Assisted oracle in getting their product to work with CAC, was that a long process. We use to have to run 2 different listeners 1 for native encryption and 1 for TCPS. We us a mix of hard and soft certs depending on the user type.

We have Toad and Sql Developer working with both.

The only parameter we use on the client side is the wallet location (mcs or wallet location).

Another thing to keep in mind, is if you are using soft cert via ODP.NET there is a bug in ODP.NET "Oracle Client Side" that it picks the first cert to authenticate. Meaning if you have more then 1 cert loaded it always picks the first one.
The .NET people on my team are pushing for an enhancement to fix it.

I feel the server/oracle should dictate the security but thats just my opinion.

SSL_VERSION = 1.2
SSL_CLIENT_AUTHENTICATION = TRUE

#fixes double encryption
SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE

#NOT IN OUR STAGE
SQLNET.AUTHENTICATION_SERVICES= (TCPS,BEQ,IPC)

#sql client version allowed no affect on encryption
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=12

#ENCRYPTION_TYPES -removed ,aes192,aes128
SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)
SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256)

#prior to 19c this would fail with cac sqlnet.ignore_ano_encryption_for_tcps setting fixes it
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_SERVER = REQUIRED

#CHECKSUM_TYPES
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (SHA384)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA384)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED

#For Oracle Native Encryption
SQLNET.FIPS_140=TRUE

SQLNET.WALLET_OVERRIDE=FALSE

Anyways not sure if any of this is useful but just thought Id share
Dave

11

Is this DOD CAC? If so, what is in the wallets? Does the CAC become the wallet?

I think this should go for more than just your opinion but a security best practice. Security decisions should not be to left to each individual user--especially since most users do not change the defaults and the defaults are not very secure. FIPS 140-2 security is absolutely imperative for any publicly facing databases even when 'publicly' is 'just' a government or military user space.

Cheers,
Russ

12

Correct the DOD CAC is really the wallet, you dont use/create a wallet with the pki tool for the cac user. It uses the MCS store on the windows client side. The server needs to have a cert as well which I think you said you have set up but it MUST have the trust chain of the cac or cac will connection will fail. The server uses soft cert because the first the obvious, it cant have a physical card, second its running on linux.

Dave

13

How do you specify the MCS as a wallet on the client side?

14

i think i just sent you an email on the dod network but Im ok with sharing that here.

WALLET_LOCATION = (SOURCE =(METHOD = MCS))