DBA Hub

📋Steps in this guide1/8

Configuration of TCP/IP with SSL and TLS for Database Connections

This article describes the server and client configuration needed to use TCP/IP with SSL and TLS for database connections.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Prerequisites and Assumptions

This article assumes the following prerequisites are in place.
2

Server Wallet and Certificate

Create a new auto-login wallet. Create a self-signed certificate and load it into the wallet. Check the contents of the wallet. Notice the self-signed certificate is both a user and trusted certificate. Export the certificate, so we can load it into the client wallet later. Check the certificate has been exported as expected.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
$ mkdir -p /u01/app/oracle/wallet

$ orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

$

$ orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 \
  -dn "CN=`hostname`" -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

$

$ orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=ol7-121.localdomain
Trusted Certificates:
Subject:        CN=ol7-121.localdomain
$

$ orapki wallet export -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 \
   -dn "CN=`hostname`" -cert /tmp/`hostname`-certificate.crt
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

$

$ cat /tmp/`hostname`-certificate.crt
-----BEGIN CERTIFICATE-----
MIIBqzCCARQCAQAwDQYJKoZIhvcNAQEEBQAwHjEcMBoGA1UEAxMTb2w3LTEyMS5sb2NhbGRvbWFp
bjAeFw0xNTA2MjYxNDQyMDJaFw0yNTA2MjMxNDQyMDJaMB4xHDAaBgNVBAMTE29sNy0xMjEubG9j
YWxkb21haW4wgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAJIrU1fGWAwMxRobFsS0UZBD1jFU
wAvnH9blsynhrQrZSkwyMBWGPRFq5tufRpaifoNVVHSrjJm/nti62A6RXECAKsug9rHL8T11FOgP
3R/+Itw2jLzwpdk7MbHMxpNHz6Y2IPCmBsJ5+625dRxugVKhLsIitAW5cUpT28bkrMl9AgMBAAEw
DQYJKoZIhvcNAQEEBQADgYEABqQaP056WcPNgzSAOhJgTU/6D8uAFGCgUN57HoraXxgAN3HgmeGq
hQfpb8tP+xeTF3ecqvWqJQHGdAJbuhRwpNR1rRovvlOxiv4gl0AplRzRuiygXfi6gst7KNmAdoxr
TOcUQsqf/Ei9TaFl/N8E+88T2fK67JHgFa4QDs/XZWM=
-----END CERTIFICATE-----
$
3

Client Wallet and Certificate

Create a new auto-login wallet. Create a self-signed certificate and load it into the wallet. Check the contents of the wallet. Notice the self-signed certificate is both a user and trusted certificate. Export the certificate so we can load it into the server later. Check the certificate.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
c:\>mkdir -p c:\app\oracle\wallet

c:\>orapki wallet create -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -auto_login_local
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

c:\>

c:\>orapki wallet add -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -dn "CN=%computername%" -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

c:\>

c:\>orapki wallet display -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=my-computer
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        CN=my-computer
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

c:\>

c:\>orapki wallet export -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -dn "CN=%computername%" -cert c:\%computername%-certificate.crt
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

c:\>

c:\>more c:\%computername%-certificate.crt
-----BEGIN CERTIFICATE-----
MIIBmzCCAQQCAQAwDQYJKoZIhvcNAQEEBQAwFjEUMBIGA1UEAxMLSVRTLUYxTUxDNUowHhcNMTUw
NjI2MDkzMzE2WhcNMjUwNjIzMDkzMzE2WjAWMRQwEgYDVQQDEwtJVFMtRjFNTEM1SjCBnzANBgkq
hkiG9w0BAQEFAAOBjQAwgYkCgYEAk/oX7ulDhW+DKXdD+qYC9DN7DoTsmeGZaW7EwYr48sw2qQWK
HP3pFb8/eVLHuqd2tX8RCniI6Dy5iMe7aM+BOvtGDT2bkCENO7xflww+L/Jp1JeF4OCawE36/Coy
sWAu4yom7n109ioT2rQsN62ERj8wPa53r8KAB12UnidBzRECAwEAATANBgkqhkiG9w0BAQQFAAOB
gQB7hbEUXM3ur2H2osuaX24mxmw83yxLnvx9BDi10kbTdH02St/EfCNlCWc69L5iAeJVESvaVgJQ
u1AZEeD3jPYMFWTnGfX4txo7+GJWwpxCJXqYYrmYQL2h1W6UtTVsJgQ08wo2bTHTjII6HB6wt8CK
OU46CFGLL+7B7Xrpnk1UwA==
-----END CERTIFICATE-----

c:\>
4

Exchange Certificates

Each side of the connection needs to trust the other, so we must load the certificate from the server as a trusted certificate into the client wallet and vice versa. Load the server certificate into the client wallet. Check the contents of the client wallet. Notice the server certificate is now included in the list of trusted certificates. Load the client certificate into the server wallet. Check the contents of the server wallet. Notice the client certificate is now included in the list of trusted certificates.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
c:\>orapki wallet add -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -trusted_cert -cert c:\ol7-121.localdomain-certificate.crt
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

c:\>

c:\>orapki wallet display -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=my-computer
Trusted Certificates:
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=my-computer
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=ol7-121.localdomain
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

c:\>

$ orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 \
   -trusted_cert -cert /tmp/my-computer-certificate.crt
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

$

$ orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=ol7-121.localdomain
Trusted Certificates:
Subject:        CN=my-computer
Subject:        CN=ol7-121.localdomain
$
5

Server Network Configuration

On the server, add the following entries into the "$ORACLE_HOME/network/admin/sqlnet.ora" file. You probably need to think about what cipher suites you want to support. You may wish to avoid those that support SSLv3 in favour of those that support TLS only. Your decision my vary depending on the Oracle database and client versions. Configure the listener to accept SSL/TLS encrypted connections. Edit the "$ORACLE_HOME/network/admin/listener.ora" file, adding the wallet information, as well as the TCPS entry. Restart the listener. The server is now configured.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = ol7-121.localdomain)(PORT = 2484))
)
  )

ADR_BASE_LISTENER = /u01/app/oracle

$ lsnrctl stop
$ lsnrctl start
6

Client Network Configuration

Edit the "$ORACLE_HOME/network/admin/sqlnet.ora" file, adding the following lines. Make sure the client cipher suites match the server configuration. Edit the "$ORACLE_HOME/network/admin/tnsnames.ora" file, making sure the port corresponds to that configured for SSL on the server and the protocol is TCPS. The client is now configured.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = c:\app\oracle\wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

pdb1_ssl=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=
TCPS
)
      (HOST=ol7-121.localdomain)
      (PORT=
2484
)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=pdb1)
    )
  )
7

Test Connection

You should now be able to make a connection to the server using the SSL/TLS enabled TNS entry.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
c:\>sqlplus test/test@pdb1_ssl

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 26 16:23:28 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
8

Oracle on Windows

If you install Oracle software under a new local user, you will need to manually make sure that user has permissions on any wallet created. Thanks to Martin Emptage for pointing this out. For more information see: - Enabling Secure Sockets Layer - End To End Examples of using SSL With Oracle's JDBC THIN Driver (Doc ID 762286.1) - Native Network Encryption for Database Connections Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!