If you're using the stock Messages app, find a message from the contact you wish to have a custom notification for. You have to select that message thread from that person. Then hit the three stacked dots in the top right corner, choose People & Options, Notifications, Sound. Each contact can have a custom SMS tone as long as you follow this path.