Encrypted Data Transfer between Oracle Database Server and Oracle Client

This post  describes one of the most important feature of Oracle Database , i.e. how to configure oracle Database and Oracle Client to communicate in an encrypted manner.

 

Environment Description:

Database Server: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

Hostname IP: 10.180.186.124

Port: 1521

Username: myuser

Password: password

Demo Table: mytable

Demo Entries:

SQL> select * from mytable;

SNO NAME

———- ———-

1 demouser1

2 demouser2

3 demouser3

4 demouser4

Database Client Version: SQL*Plus: Release 10.1.0.2.0 – Production

Host IP: 10.180.185.76

Communication between Database Server and database Client without Encryption:

  • On the client machine, start the Sqlplus client.
  • Connect to the remote database and make a simple select query.

  • Monitor the network packet on the Database Server Machine using the Wireshark Monitoring tool.
  • I have captured the Network packet :

Network-Packet-Without-Encryption.pcap

  • We can see from the below screen shot of the network packet that the username used for making connection, the select query used and the result data received from the Database can be seen in clear text.

Communication between Database Server and Database Client with Encryption Enabled.

In order to enable the Encryption between the Database Server and Database Client we need to make some configurations on both the server and the client end.

Configuration required on the Client Side:

We need to configure the listener service on the Client side. If this is already done then we can directly move on to the configuration of the encryption settings given below.

  • Invoke Net Manager.
  • Expand Local. Select Service Naming and click the green + (plus) sign to create a new service name.

  • In the Net Service Name field, enter a name for the net service name you wish to create. In this example, the name “demo_db_svc” is used. Click Next

  • Select “TCP/IP (Internet Protocol)” and click next.

  • Enter the host name or the IP address of the server for your database. Accept the default port number of 1521 or enter the port number that is appropriate for your database listener. Click Next.

  • Click Test to test your connection configuration. Note: By default the connection is tested using the username of SCOTT with a password of TIGER

  • You can change the user for the connection test by clicking Change Login.
  • In my case it is : myuser/password

  • After your connection test succeeds, click Close

  • Select Save Network Configuration in the File menu to save your configuration.
  • Select Exit in the File menu to exit Oracle Net Manager.

Configuration of Network Data Encryption on the Client Side:

  • Open the Net Manager again.
  • Select Profile. Select Oracle Advanced Security in the Naming menu.

  • Click the Encryption tab.

  • Verify that the Encryption field is set to CLIENT.
  • Select required in the Encryption Type menu.
  • Enter fewer than 70 random characters in the Encryption Seed field. I used : clientseed123
  • Select the encryption method by highlighting it in the Available Methods list and clicking > to move it to the Selected Methods list.

  • Select Save Network Configuration in the File menu to save your configuration.
  • Select Exit in the File menu to exit Oracle Net Manager.
  • The configuration on the Client is complete.
  • After this, we need to restart the listener on the client side using lsnrctl stop and lsnrctl start commands.

The sqlnet.ora file on the client side will look like this:

——————————————————————————————————–

SQLNET.ENCRYPTION_CLIENT = required

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.CRYPTO_SEED = clientseed123

SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256, RC4_256, AES192, 3DES168, AES128, RC4_128, 3DES112, RC4_56, DES, RC4_40, DES40)

——————————————————————————————————

Now we have to configure the Network Data Encryption on the Server Side:

  • We have follow the same steps of invoking the Net Manager on the serve side and while choosing the encryption options we need to use the below options:
  • Verify that the Encryption field is set to SERVER.
  • Select required in the Encryption Type menu.
  • Enter fewer than 70 random characters in the Encryption Seed field. I used : server123
  • Select the encryption method by highlighting it in the Available Methods list and clicking > to move it to the Selected Methods list.

  • Select Save Network Configuration in the File menu to save your configuration.
  • Select Exit in the File menu to exit Oracle Net Manager.
  • The configuration on the Server is complete.
  • After this, we need to restart the listener on the Server Side using lsnrctl stop and lsnrctl start commands.

The sqlnet.ora file on the Server Side will look like this:

———————————————————————————————————

SQLNET.AUTHENTICATION_SERVICES= (NTS)

SQLNET.ENCRYPTION_SERVER = requested

SQLNET.CRYPTO_SEED = ‘server123’

SQLNET.ENCRYPTION_TYPES_SERVER= (AES256, RC4_256, AES192, 3DES168, AES128, RC4_128, 3DES112, RC4_56, DES, RC4_40, DES40)

——————————————————————————————————–

  • Now we will do the connection test from Database Client and do a simple query and monitor the network on the server side and see whether the data is transferred in encrypted format or clear text.

D:oracleproduct10.1.0db_1BIN>sqlplus myuser/password@DEMO_DB_SVC

SQL*Plus: Release 10.1.0.2.0 – Production on Thu Dec 1 16:24:39 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from mytable;

SNO NAME

———- ———-

1 demouser1

2 demouser2

3 demouser3

4 demouser4

 

  • I have captured the network packet: Network-Packet-With-Encryption.pcap
  • Analyzing the above network packet we can see that there is no clear text data present on the wire.

Link referred :

http://docs.oracle.com/cd/E11882_01/network.112/e10746/asoconfg.htm#i1006517

Please follow and like us:

Please add your comments here