Check “notification settings” under the app settings of your sms app (Something like: Settings → Apps → SMS app or whatever name it has → Notifications). You should find there a way to change the tone of that app notifications.
I’m using QKSMS app for SMS:
Hope it helps.