Connecting to Oracle Database using SQL Developer and Wallet authentication method

The story behind this post

A friend encountered a trouble opening a connection using SQL Developer to oracle database using Wallet authentication method.

The ORA error code we got was ORA-12578 TNS: wallet open failed.
After an hour of trying to troubleshoot we were able to make it work.

This post documents how to use the Oracle Sql developer while using the wallet authentication

Assumptions

• Your Wallet already setup at the database, if you need any help see http://www.dba-resources.com/oracle/a-guide-to-oracle-wallet/
• You should have the files cwallet.sso and ewallet.p12 files at the end of the process
• Your Wallet storage is setup on your Client (where the SQL developer is installed)
• Your TNS is setup with the connection to the database, example set is:

TEST_WALLET_ALIAS = 

    ( DESCRIPTION  =  

       ( ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.17.2)(PORT = 5138))

       ( CONNECT_DATA = 

          (SERVICE_DATA = demo_srv)

       )

    )

• Your sqlnet.ora have the needed configuration. Our at the client side sqlnet.ora looked like this:

WALLET_LOCATION =

    ( SOURCE =

      ( METHOD = FILE)

      ( METHOD_DATA = 

        ( DIRECTORY = c:\app\product\11.2.0\client_2\network\admin)

      )

    )

Preparing

Verifying Wallet connection is working locally

Before trying to work remotely using the SQL developer, verify the wallet is working by doing the following (NOTICE: This requires access to the database machine itself. Not always you have that access rights. If you do not, try):

1. Connect to the machine (using SSH or Remote Desktop)
2. Connect locally using the wallet authentication method using the following syntax:
1. sqlplus /@<your wallet name> (For example  sqlplus /@TEST_WALLET_ALIAS
3. If you are able to connect on the database itself continue.

Verifying Wallet connection works remotely using sqlplus

If you have installed the oracle client correctly you probably have the sqlplus client (Oracle's proprietary client).

We'll use it to verify the Wallet is working remotly

1. Open terminal or command line
2. Go to your oracle client installation directory. Our's was at c:\app\product\11.2.0\client_2)
3. Go to the network admin directory.
4. Copy the sqlnet.ora, tnsnames.ora and wallet files from the database. The tnsnames.ora should have the alias, in our case it was TEST_WALLET_ALIAS
5. Connect using the syntax of the wallet authentication
1. sqlplus /@<your wallet name> (For example  sqlplus /@TEST_WALLET_ALIAS

Connecting using SQL Developer

If you've reached this point, I'm assuming sqlplus worked for you at least remotely.

So, in order to make the SQL Developer use the wallet authentication we'll do the following

1. Verify that you have environment variable named TNS_ADMIN in the system environment variables pointing to the directory where the sqlnet.ora directory resides,
1. To configure environment variables on windows, go to Control Panel --> System Properties --> Environment Variables
2. In the sqlnet.ora you need to direct to wallet directory.
3. Create a wallet using the wallet manager
4. Copy the wallet files from the Oracle database to the wallet directory
5. Create a new advanced connection using the following format
1. Connection Name: TestWaller (the name does not really matter)
2. Connection Type: Advanced
3. Custom JDBC URL: jdbc:oracle:oci /@<your wallet alias> (In our case it was jdbc:oracle:oci /@TEST_WALLET_ALIAS)

Further reading

http://www.dba-resources.com/oracle/a-guide-to-oracle-wallet/