After having gone through Android versions 1.5 to 4.0 myself. I am not sure we will ever see individual text notification tones.
As you have found out, there are applications that have this function.
I'm most definitely NOT a VZW employee. If a post answered your question, please mark it as the answer.