It seems Samsung removed this feature from the Oreo versions of its contacts and messaging apps (both on the S8 and on the S9). You can use a different messaging app and set a per-thread notification sound.
If you need additional help or have more questions or details to share, please join the site so you can reply in this thread. See this link for instructions on how to join Android Central.