This is on a jailbroken iPhone 3GS running 3.0, with Winterboard (and Winterboard Settings) installed.
So with my FTP program (Transmit on Mac OS X) I went into /private/var/stash/Themes.kp0lv3/Pac-Man Dies SMS Tone.theme/UISounds and renamed sms-received.caf to sms-received1.caf. Now it worked when I select "Tri-Tone" (the first sound chouce) in my Settings -> Sounds -> New Text Message.
Then I copied the file five times, naming them all sequentially, like so:
And now it works no matter what "New Text Message" sound I select in Settings -> Sounds.
I just turn it on (or off) in Winterboard.
This might be elementary to some of you folks, but I'm just sharing because I'm new in the scene and figured it out myself ... and thought it might help someone else out there.