I had the same problem and here's the fix that worked for me (iPhone 15, version 17.5). Go to Music and then Library, find the song you want to use for an alarm, click the ... next to the song, and then click Download (if Download isn't an option in the menu, then this song might work for an alarm already). Even though the song was already on my phone and I could play it in Music, clicking download did something to "reload" it and now when I select it for alarms, it plays the song when the alarm goes off. Note that once I did this, when I went back to the song in Music and clicked ..., the Download option was no longer in the menu.