One of the more popular posts on my site is My iPhone’s Start Trek SMS tone (a how-to). This delves a bit into the sequence of ssh commands on a jailbroken phone to swap out the default SMS sound with a Star Trek: The Next Generation communicator sound.
iOS 5, thankfully, has rendered that post useless. Changing this sound is now a simple and foolproof process. I have already converted it from the *.AIFF sound format used by the other method into the ringtone format used currently. Just download this file:
This is an iTunes ringtone. Next, drag that file into iTunes and sync it to your iPhone. Finally, go in to Settings > Sounds > Text Tone and select this as your sound.