无法通过TCPS连接Oracle数据库的技术求助

Let’s work through this step by step—since your listener confirms the TCPS port is active, we can rule out basic listener setup, but there are several common SSL-related pitfalls that could be blocking your client connections.

1. Double-Check Your Connection String (Critical for No tnsnames.ora) #

Without tnsnames.ora, you need to specify the full TCPS connection details directly in your client tool. A valid EZConnect-style string for TCPS should look like this:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=your-server-hostname)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=your-oracle-service))(SECURITY=(SSL_SERVER_CERT_DN="CN=your-server-certificate-common-name")))

• Ensure the SERVICE_NAME matches exactly what’s listed in lsnrctl services (use SERVICE_NAME, not the SID, unless you replace it with SID_NAME).
• The SSL_SERVER_CERT_DN must match the Distinguished Name of your server’s SSL certificate. Grab this by running openssl x509 -in server-cert.pem -noout -subject on the server’s certificate file.

2. Validate Client Truststore Configuration #

You mentioned adding the server certificate to the client’s vmargs, but let’s confirm you did this correctly:

• For DBeaver: Make sure your vmargs include an absolute path to a truststore containing the root CA certificate (not just the server’s leaf cert) and the correct password. Example:

  -Djavax.net.ssl.trustStore=/full/path/to/your/truststore.jks
  -Djavax.net.ssl.trustStorePassword=your-truststore-password
  

  Relative paths often cause the client to fail finding the truststore, so stick to absolute paths here.
• For SQuirrel SQL: Edit squirrel-sql.bat (Windows) or squirrel-sql.sh (Linux/macOS) and add the same truststore parameters to the VMARGS section. Restart the tool after making changes.

3. Verify Server-Side SSL Setup (Even Defaults Can Have Glitches) #

Even with the wizard’s default settings, let’s check a few key server files:

• sqlnet.ora: Look for WALLET_LOCATION (should point to the wizard-created wallet) and SSL_VERSION (ensure it matches your client’s supported TLS version). Example:

  WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/wallet)))
  SQLNET.AUTHENTICATION_SERVICES = (TCPS)
  SSL_VERSION = TLSv1.2
  

• Wallet Permissions: The Oracle listener process needs read access to the wallet directory. Run ls -l /path/to/wallet to confirm the oracle user (or listener user) can read ewallet.p12 and cwallet.sso.
• lsnrctl Services: Run lsnrctl services to confirm the TCPS endpoint is linked to your target service. You should see output like:

  Service "ORCL" has 1 instance(s).
    Instance "ORCL", status READY, has 1 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:0 refused:0 state:ready
           LOCAL SERVER
        "TCPS" established:0 refused:0 state:ready
           ADDRESS=(PROTOCOL=tcps)(HOST=your-server)(PORT=1522)
  

4. Test with Oracle’s Native sqlplus First #

Rule out client tool issues by testing the connection with sqlplus on the client machine (or server):

sqlplus your-username/your-password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=your-server)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=ORCL))(SECURITY=(SSL_SERVER_CERT_DN="CN=your-server-dn")))

If sqlplus connects successfully, the problem is specific to DBeaver/SQuirrel. If it fails, focus on server-side SSL or network issues.

5. Check Network and Firewall #

• Port Connectivity: Use telnet your-server 1522 or nc -zv your-server 1522 to confirm the port is reachable from the client. If this fails, check server-side firewall rules (iptables, firewalld) or client-side proxies blocking the port.
• Hostname Match: Ensure the hostname in your connection string exactly matches the CN (Common Name) in the server’s SSL certificate. Oracle SSL enforces hostname verification by default—mismatches will break the connection.

6. Enable SSL Debug Logging for Clients #

To get detailed error messages, add the SSL debug flag to your client’s vmargs:

• For DBeaver/SQuirrel: Add -Djavax.net.debug=ssl to the vmargs. This will print verbose SSL handshake logs to the console or log file. Look for errors like unable to find valid certification path (truststore issue) or certificate hostname mismatch (CN vs hostname issue).

内容的提问来源于stack exchange，提问作者throw into