Decode of TNS/SQL 1

[jdavis1]

Ok, I'm sure I'm not the first one to have this problem. Anyone have an easy way to decode TNS/SQL packets? The problem is that the sniffer (4.3.5) has the decode built in, but Oracle uses dynamic ports. I don't know all the details of the TNS protocol, but what I see is this:

1. Client connects to port 1521. Sniffer decodes this as TNS protocol.
2. Server responds with a TNS redirect to port 2327 (or some other port)
3. The socket on port 1521 gets torn down.
4. A connect is made to port 2327 and the TNS/SQL conversation takes place there. The sniffer doesn't recognize these as TNS/SQL packets.

I thought of using protocol forcing, unfortunately this only works at layer 3 and not above. Interestingly, I did suck the file into Ethereal and was able to force the protocol to decode dest port 2327 as a TNS packet. However, Ethereal doesn't decode the SQL call inside the TNS encapsulation, which is what I'm after.

Another thing that I considered was reading the raw trace file (.cap) into a C program and changing the dest 2327 port into port 1521, but I don't know what the file format is.

Anyone have a solution?

 

[fortunat]

Have you tried running it through Ethereal?



'Making things work better; bit by bit.'

 

[jdavis1]

Yes, I did run it through ethereal (see original post). Is there an SQL decode available that I don't have?

 

[patrckb]

I understand your frustration. I have an application that talks TNS on the back end to an Oracle database. But the SQL connections don't consistently go across the same port number. So I can't get a good "read" on how much of my traffic is this application's since the ports are all over the place.

The capture decode WILL decode it to being TNS when it inspects the packets that deeply. But the "Protocol" tab just shows it as mostly "Others." It's not much help for network profiling.

Patrick

 

[jdavis1]

Patrick,

What do you mean "The capture decode WILL decode it to being TNS when it inspects the packets that deeply"? The only packet in my trace that I see decoded as TNS are the original "connect" and "redirect to another port" packets that use port 1521. Is there a way to force the decode to recognize the packets that use other ports as TNS packets?

-Jeff

 

[mahaprabhu]

Can't you use port aggregation to make tns packets get counted under a user defined name, instead of 'others'

 

[thcollicutt]

?????


I have similar problems. If someone has an easyway to get TNS/SQL or other SQL packets to get decoded even when the app developer doesn't use constant ports, I would be interested.

 

[jdavis1]

Port aggregation? If you mean defining a non-standard port as TNS, then no, this won't work, because you never know what port it's going to be. Besides, isn't that just a basic monitoring function allowing you to get a view of protocol distribution? What I need to do is decode a packet capture.

 

[phaelon56]

Re the comment "I can't get a good "read" on how much of my traffic is this application's since the ports are all over the place".

Please bear with my ignorance as I haven't been using the latest Distributed Sniffer releases but am I correct in remembering that the newer versions of Sniffer allow one to monitor a dynamic range of ports in the Application Monitoring feature? If that is the case and if the dynamic range for SQL or Oracle is not too wildly dispersed (and if there aren't any other significant apps working on those ports), then perhaps this challenge could be met with that function of Sniffer.

Regrettably... I have no worthwhile input to offer regarding the original challenge mentioned in this post.

Owen O'Neill
Datacom Systems Inc.
Northeastern SE

http://www.datacomsystems.com

 

[mahaprabhu]

There used to be a product called Protocol Development Kit that allowed users to create their own protocol decodes and plu them into the Sniffer Portable product. I don't know if this is available for Sniffer Distributed.

But if you have time and / or money, you could have someone develop a basic protocol decode for tns. Or you could explore Sniffer Professional Services to create one for you.

The biggest challenge will probably be using heuristics on the packet content, to determine when a particular packet belongs to tns.

MP

 

[Greatgazzoo]

Is it on a specific server? You should be able to define the filter based on server and other details within each packet.

 

[bkirchoff]

JDAVIS..Did you ever get an answer to this problem? I'm going through the same thing.

 

[jdavis1]

Nope... sorry. What you see here is what you get. If you come across a solution, I'd be interested in learning what it was.

 

[Sniff4Food]

I have an application that I believe will decode it. Can you send me a small portion of the trace so I can test it before I list it here? I would need the 1521 setup, and the next 30 that are using the dynamic port number.