Settings; Sounds & Notifications; Messaging: New text message.
Select "Play Sound" and select a sound. Looks like you have to use a .wav file from the \windows directory.
If you want to use a wma/mp3 etc, get phoneAlarm and select whatever sound you want for SMS messages.
Attached are a few of my favorite notifications to get you started.