10 Replies Latest reply: Apr 22, 2013 6:36 AM by iehf RSS

    ssl authentication

    sonidba
      Hello to all

      I am trying to configure ssl between client and server. I am following doc : E10746-05. My db server machine name is host1.oracle.com. I am running 11.2.0.1.0. on the server. database name that is running here is orcl.koenig.com
      Firstly, I created the wallet using orapki then added a self-signed certificate to it.

      I am pasting whatever I have done along with the output.
      +[oracle@host1 oracle]$ orapki wallet create -wallet /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle+
      Oracle PKI Tool : Version 11.2.0.1.0 - Production
      Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

      Enter password:

      Enter password again:

      +[oracle@host1 oracle]$ orapki wallet add -wallet /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle -dn 'CN=orcl' -keysize 2048 -self_signed  -validity 365+

      Oracle PKI Tool : Version 11.2.0.1.0 - Production
      Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

      Enter wallet password:
      To turn autologin on , I used owm.
      +[oracle@host1 oracle]$ owm+
      Done.

      +[oracle@host1 oracle]$ orapki wallet display -wallet /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracl+e
      Oracle PKI Tool : Version 11.2.0.1.0 - Production
      Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

      Requested Certificates:
      User Certificates:
      Subject: CN=orcl
      Trusted Certificates:
      Subject: CN=orcl
      Subject: OU=Class 1 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: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
      Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

      Here is the contents of sqlnet.ora and listener.ora from server side:

      _//sqlnet.ora_


      #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

      #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

      SSL_VERSION = 0

      SSL_CLIENT_AUTHENTICATION = FALSE

      WALLET_LOCATION =
      (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle)
      )
      )

      SSL_CIPHER_SUITES= (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_256_CBC_SHA)

      ADR_BASE = /u01/app/oracle

      _//listener.ora_

      SSL_CLIENT_AUTHENTICATION = FALSE

      WALLET_LOCATION =
      (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle)
      )
      )

      LISTENER =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = hostt1.koenig.com)(PORT = 2484))
      )

      ADR_BASE_LISTENER = /u01/app/oracle


      here is sqlnet.ora and tnsnames.ora from client machine

      _//sqlnet.or_a
      #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

      #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

      SSL_VERSION = 0

      SSL_SERVER_DN_MATCH = YES

      SSL_CLIENT_AUTHENTICATION = TRUE

      WALLET_LOCATION =
      (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle)
      )
      )

      SSL_CIPHER_SUITES= (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_256_CBC_SHA)

      ADR_BASE = /u01/app/oracle

      _//tnsnames.ora_

      ORCL =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = host1.koenig.com)(PORT = 2484))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = orcl.koenig.com)
      )
      )
      (SECURITY =
      (SSL_SERVER_CERT_DN="CN=orcl")
      )

      Now on client when I am writing sqlplus /@orcl
      produces

      ERROR:
      ORA-29024: Certificate validation failure


      Enter user-name:

      Any help to trouble shoot? Please inform if I need to perform some additional steps.

      Thanks
        • 1. Re: ssl authentication
          iehf
          Hi, sonidba

          First, you should create different wallets for client and server. Client certificate request must be signed by same root private key as server one.
          When you set SSL_SERVER_DN_MATCH = YES, then server certificate DN=CN=... have to be equal to database name.

          ps: sorry my poor english


          In your case you should make 3 wallets: root, database and user. Example is below. I tested it on my database 11.2.0.1 64 bit linux.
          Database name = CDB, password is the same on all wallets - Welcome1, domain in DN is for your taste (, dc=...)
          It's important for parameter -dn 'CN=CDB,DC=test,dc=com' that first CN have to be the name of your database.

          Example:

          1. Create necessary directories:
          -- for any wallet
          mkdir $ORACLE_HOME/wallets
          -- for wallet with self-signed root certificate
          mkdir $ORACLE_HOME/wallets/root
          -- for database wallet
          mkdir $ORACLE_HOME/wallets/db
          -- for test user wallet
          mkdir $ORACLE_HOME/wallets/user

          2. Create wallet that will contain a root certificate (self-signed for test) to sign database and users certificates
          orapki wallet create -wallet $ORACLE_HOME/wallets/root -pwd Welcome1
          2.1. Add self-signed certificate
          orapki wallet add -wallet $ORACLE_HOME/wallets/root -dn 'CN=root' -keySize 2048 -self_signed -validity 1825
          2.2. Export root certificate
          orapki wallet export -wallet $ORACLE_HOME/wallets/root -dn 'CN=root' -cert $ORACLE_HOME/wallets/root/root.cer

          3. Create database wallet
          3.1. Create auto-login (and password) database wallet
          orapki wallet create -wallet $ORACLE_HOME/wallets/db -auto_login -pwd Welcome1
          3.2. Import root certificate into database wallet
          orapki wallet add -wallet $ORACLE_HOME/wallets/db -trusted_cert -cert $ORACLE_HOME/wallets/root/root.cer -pwd Welcome1
          3.3. Create certificate request for database
          orapki wallet add -wallet $ORACLE_HOME/wallets/db -dn 'CN=CDB,DC=test,dc=com' -keysize 1024 -pwd Welcome1
          3.4. Export certificate request for signing
          orapki wallet export -wallet $ORACLE_HOME/wallets/db -dn 'CN=CDB,DC=test,dc=com' -request $ORACLE_HOME/wallets/db/dbcert.req
          3.5. Sign request with root private key
          orapki cert create -wallet $ORACLE_HOME/wallets/root -request $ORACLE_HOME/wallets/db/dbcert.req -cert $ORACLE_HOME/wallets/db/dbcert.cer -validity 365 -pwd Welcome1
          3.6. Import database certificate into database wallet
          orapki wallet add -wallet $ORACLE_HOME/wallets/db -user_cert -cert $ORACLE_HOME/wallets/db/dbcert.cer -pwd Welcome1

          4. Create user wallet (have to do for each user)
          4.1. Create auto-login (and password) user wallet
          orapki wallet create -wallet $ORACLE_HOME/wallets/user -auto_login -pwd Welcome1
          4.2. Import root certificate into database wallet
          orapki wallet add -wallet $ORACLE_HOME/wallets/user -trusted_cert -cert $ORACLE_HOME/wallets/root/root.cer -pwd Welcome1
          4.3. Create certificate request for database
          orapki wallet add -wallet $ORACLE_HOME/wallets/user -dn 'CN=ssluser,DC=test,dc=com' -keysize 1024 -pwd Welcome1
          4.4. Export certificate request for signing
          orapki wallet export -wallet $ORACLE_HOME/wallets/user -dn 'CN=ssluser,DC=test,dc=com' -request $ORACLE_HOME/wallets/user/usercert.req
          4.5. Sign request with root private key
          orapki cert create -wallet $ORACLE_HOME/wallets/root -request $ORACLE_HOME/wallets/user/usercert.req -cert $ORACLE_HOME/wallets/user/usercert.cer -validity 365 -pwd Welcome1
          4.6. Import database certificate into database wallet
          orapki wallet add -wallet $ORACLE_HOME/wallets/user -user_cert -cert $ORACLE_HOME/wallets/user/usercert.cer -pwd Welcome1

          Edited by: user11986436 on 17.04.2013 6:32
          • 2. Re: ssl authentication
            sonidba
            I followed all the steps that you have specified on a single machine. Suppose I am using a single machine only for client and server, what to do next?
            • 3. Re: ssl authentication
              iehf
              sonidba,

              now configure client and server on single machine. As i seen your ORACLE_HOME is /u01/app/oracle/product/11.2.0/db_1.

              Place client network configuration file into separate directory than server. I.e.
              Also suppose that wallet directories are named as shown above.

              1. mkdir $ORACLE_HOME/network/user

              2. in /u01/app/oracle/product/11.2.0/db_1/network/admin

              Here is the contents of sqlnet.ora and listener.ora from server side:

              //sqlnet.ora
              #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

              #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

              SSL_VERSION = 3.0

              SSL_CLIENT_AUTHENTICATION = FALSE

              WALLET_LOCATION =
              (SOURCE =
              (METHOD = FILE)
              (METHOD_DATA =
              (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/wallets/db)
              )
              )

              #SSL_CIPHER_SUITES= (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_256_CBC_SHA)

              ADR_BASE = /u01/app/oracle

              //listener.ora

              SSL_CLIENT_AUTHENTICATION = FALSE

              WALLET_LOCATION =
              (SOURCE =
              (METHOD = FILE)
              (METHOD_DATA =
              (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/wallets/db)
              )
              )

              LISTENER =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCPS)(HOST = hostt1.koenig.com)(PORT = 2484))
              )

              ADR_BASE_LISTENER = /u01/app/oracle

              3. in /u01/app/oracle/product/11.2.0/db_1/network/user

              here is sqlnet.ora and tnsnames.ora from client machine

              _//sqlnet.or_a
              #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

              #SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

              SSL_VERSION = 3.0

              SSL_SERVER_DN_MATCH = YES

              SSL_CLIENT_AUTHENTICATION = TRUE

              WALLET_LOCATION =
              (SOURCE =
              (METHOD = FILE)
              (METHOD_DATA =
              (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/wallets/user)
              )
              )

              #SSL_CIPHER_SUITES= (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_256_CBC_SHA)

              ADR_BASE = /u01/app/oracle

              //tnsnames.ora

              ORCLSSL =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCPS)(HOST = host1.koenig.com)(PORT = 2484))
              )
              (CONNECT_DATA =
              (SERVICE_NAME = orcl.koenig.com)
              )
              (SECURITY =
              (SSL_SERVER_CERT_DN="CN=CDB,DC=test,dc=com") #place your server DN here
              )
              )

              4. Restart listener
              5. Test ssl connection
              tnsping orclssl
              6. Create user and associate him with user wallet via DN
              sqlplus "/ as sysdba"
              SQL> create user ssluser identified externally as 'CN=ssluser,DC=test,DC=com'; -- place your user dn here

              User created.

              SQL> grant create session to ssluser;

              Grant succeeded.

              SQL> quit

              7. Set user environment
              export $TNS_ADMIN=$ORACLE_HOME/network/user
              8. Test user ssl connection
              sqlplus /@orclssl
              SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 19 13:15:12 2013

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


              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options

              SQL>

              Show your result to us :)

              Edited by: user11986436 on 19.04.2013 2:11
              • 4. Re: ssl authentication
                sonidba
                I have one more doubt. Do all these steps need oracle internet directory. ?
                • 5. Re: ssl authentication
                  iehf
                  nope
                  • 6. Re: ssl authentication
                    sonidba
                    After following all the steps as told by you, I am getting :

                    [oracle@host1 user]$ sqlplus /@orcl

                    SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 23 00:42:35 2013

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

                    ERROR:
                    ORA-01017: invalid username/password; logon denied
                    • 7. Re: ssl authentication
                      iehf
                      try do

                      tnsping orcl

                      what is in result?
                      • 8. Re: ssl authentication
                        sonidba
                        oracle@host1 admin]$ tnsping orcl

                        TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-APR-2013 05:28:56

                        Copyright (c) 1997, 2009, Oracle. All rights reserved.

                        Used parameter files:
                        /u01/app/oracle/product/11.2.0/db_1/network/user/sqlnet.ora


                        Used TNSNAMES adapter to resolve the alias
                        Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = host1.koenig.com)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = ORCL.KOENIG.COM)) (SECURITY = (SSL_SERVER_CERT_DN=CN=ORCL,dc=koenig,dc=com)))
                        OK (50 msec)


                        I am pasting the contents of my configuartion file.

                        ///sqlnet.ora on server side

                        SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

                        #SQLNET.AUTHENTICATION_SERVICES= (TCPS)

                        SSL_VERSION = 3.0

                        SSL_CLIENT_AUTHENTICATION = TRUE

                        WALLET_LOCATION =
                        (SOURCE =
                        (METHOD = FILE)
                        (METHOD_DATA =
                        (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/db)
                        )
                        )

                        #SSL_CIPHER_SUITES= (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_256_CBC_SHA)

                        ADR_BASE = /u01/app/oracle

                        ///listener .ora on server side

                        SID_LIST_LISTENER =
                        (SID_LIST =
                        (SID_DESC =
                        (GLOBAL_DBNAME = ORCL.KOENIG.COM)
                        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
                        (SID_NAME = orcl)
                        )
                        )

                        SSL_CLIENT_AUTHENTICATION = TRUE

                        WALLET_LOCATION =
                        (SOURCE =
                        (METHOD = FILE)
                        (METHOD_DATA =
                        (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/db)
                        )
                        )

                        LISTENER =
                        (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCPS)(HOST = host1.koenig.com)(PORT = 2484))
                        )

                        ADR_BASE_LISTENER = /u01/app/oracle


                        ///sqlnet.ora on client side

                        SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

                        #SQLNET.AUTHENTICATION_SERVICES= (TCPS)

                        NAMES.DIRECTORY_PATH=(TNSNAMES)
                        SSL_VERSION = 3.0

                        SSL_CLIENT_AUTHENTICATION =TRUE

                        SSL_SERVER_DN_MATCH = YES

                        WALLET_LOCATION =
                        (SOURCE =
                        (METHOD = FILE)
                        (METHOD_DATA =
                        (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/user)
                        )
                        )

                        #SSL_CIPHER_SUITES= (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_256_CBC_SHA)

                        ADR_BASE = /u01/app/oracle


                        ///tnsname.ora for client

                        ORCL =
                        (DESCRIPTION =
                        (ADDRESS_LIST =
                        (ADDRESS = (PROTOCOL = TCPS)(HOST = host1.koenig.com)(PORT = 2484))
                        )
                        (CONNECT_DATA =
                        (SERVICE_NAME = ORCL.KOENIG.COM)
                        )
                        (SECURITY =
                        (SSL_SERVER_CERT_DN="CN=ORCL,dc=koenig,dc=com"))
                        )
                        • 9. Re: ssl authentication
                          sonidba
                          Yes , it is done now.

                          I just added SQLNET.INBOUND_CONNECT_TIMEOUT=0 in server's sqlnet.ora

                          and INBOUND_CONNECT_TIMEOUT_LISTENER=0
                          DIRECT_HANDOFF_TTC_LISTENER=OFF
                          in listener .ora

                          because I was getting following in alert.log file

                          Fatal NI connect error 12170.

                          VERSION INFORMATION:
                          +     TNS for Linux: Version 11.2.0.1.0 - Production+
                          +     Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production+
                          +     sdp+
                          Time: 23-APR-2013 05:14:43
                          Tracing not turned on.
                          Tns error struct:
                          ns main err code: 12535

                          TNS-12535: TNS:operation timed out
                          ns secondary err code: 12606
                          nt main err code: 0
                          nt secondary err code: 0
                          nt OS err code: 0
                          Client address: (ADDRESS=(PROTOCOL=tcps)(HOST=192.168.2.100)(PORT=9780))
                          WARNING: inbound connection timed out (ORA-3136)
                          • 10. Re: ssl authentication
                            iehf
                            I'm glad for you :)