iOS 4.2 adds the ability to set individual text message tones to your contacts (finally!), but not custom tones, not even ringtones purchased from the iTunes Store. Apple seems to be keeping text tones completely separate from ringtones (and since they're shorter and functionally different -- they don't need to keep playing for a long period of time until you answer -- that may make some sense). Yup, that means you're still limited to the built in choices.
People want custom text tones, however, so here's hoping Apple adds a way to do that in one of the next few betas. (And not just through purchasing, because you just know the record labels would love to charge $0.99 for a 2 second text tone.)