Capturing Traffic Between the App and Database Servers

In order to troubleshoot issues you are having you may want to capture the network traffic between your application and database servers. This will allow you to see SQL statements being passed to the database, along with a lot of other potentially useful information. The basic procedure is outlined below (thanks to Eric Glass):

Here are some general instructions for capturing traffic sent from the application server to the database server:

1) Stop the application server.

2) Set up a remote SSH tunnel on any free port on the application server (e.g., 9999), pointing to the database host/port. The exact terminology used for this varies by SSH client, but typically it would be:

Source Port: 9999
Destination Host: database server
Destination Port: database port (typically 1521 for Oracle, 1433 for SQL server)

For Putty, the required settings can be found in the connection configuration dialog under SSH -> Tunnels (source port would be "9999", destination would be of the form "host:port", e.g. "mydbserver:1521"). For OpenSSH, the command line parameter would be "-R 9999:mydbserver:1521".

3) Make the SSH connection. You will need to leave the shell window open for the duration for the tunnel to remain open.

4) Start Wireshark. Go to Capture -> Interfaces; click "Options" for the interface you want to capture. In the Capture Options dialog, enter the following in the Capture Filter:

host mydbserver && port 1521

(substituting the appropriate database server host/port). Ensure "Update list of packets in real time" is checked. Click "Start" to start capturing.

5) Test the tunnel; make another SSH connection to the application server, and type:

telnet localhost 9999

This will open a connection to the remote end of the tunnel; traffic will be routed over the SSH connection, and an outgoing connection will be made from your client box to the database server on the destination port. You should see the outgoing connection traffic in Wireshark. Close the second SSH window and restart capturing in Wireshark (Capture -> Restart from the menu). Alternatively, you can revise the capture filter at this point to also include web traffic from the browser to the application server:

(host mydbserver && port 1521) || (host appserver && port 80)

6) Modify the application server JDBC configuration to point to port 9999 on 127.0.0.1. The application server will be connecting to the tunnel source port locally, which will spawn an outgoing connection from your client box to the database server (which will be captured).

7) Restart the application server and verify that traffic is received in Wireshark.

8) Restart capturing in Wireshark, perform the desired operation in the application, then stop capturing in Wireshark. This will ensure you get a concise capture with minimal "noise" from other application activity.

9) Save the capture file, and reset your configuration to normal.

Note that technically, you don't have to establish the SSH tunnel directly to the application server; any SSH server will work (i.e. a Unix box completely unrelated to the application system). You would need to ensure the source end of the tunnel on the SSH server accepts connections from other hosts (typically an option in the tunnel configuration settings); you would then point the application server to that box instead of 127.0.0.1.

Once you have the capture, there are a variety of tools Wireshark provides to analyze the capture. For example, select Statistics -> Conversations from the Wireshark menu and click the "TCP" tab; this will show all TCP connections active during the capture period. You can start examining these by selecting an entry from the list, right clicking, and selecting "Apply as Filter -> Selected -> A<->B" from the popup menu. This will filter the packet window to display only that connection. You can also view or save all the traffic for a connection as text by selecting a packet in the list and choosing "Follow TCP Stream". For many protocols, Wireshark includes dissectors which will parse out the traffic and present it semantically (for example, it can decode HTTP traffic and display request/response information). I don't believe there is currently a dissector for Oracle, but you can view the raw TCP streams to see the SQL statements going over the wire