Just wanted to let you know I found a way to set custom alert tones in Messages.
1. Convert your sound file to .caf format -
http://stackoverflow.com/a/10392106
2. Go to System > Library > PrivateFrameworks >Tonelibrary.framework > Versions > A > Resources > AlertTones > Modern
3. Pick an existing tone that you'll never use (I went for Synth) and rename it slightly (I went with sms_alert_synth_bak.caf)
4. Move your newly created .caf file to this folder and rename it to the original name of the file in step 3 (in my case, sms_alert_synth.caf)
5. Open Messages, go to Preferences > Message received sound and select the name of the alert tone you've replaced (Synth for me). You should hear your custom tone playing.
Thanks to ccmath for pointing me in the right direction!