For individual contacts, it's possible through Contacts.app or the Phone.app. Click on the contact, and scroll down to Text Tone, and point it to an appropriate ringtone extension file that you've transferred over through iTunes. If you want it for each and every contact, you'll have to repeat it for each and every contact.
If you want full access/reign on the SMS tone, you'll have to jailbreak your device as it requires that you to have root access to modify the tone files