It varies by players, but I suspect most will prefer either a happy ending or a heroic sacrifice.
Different people have different tastes so it is impossible to provide a universal answer. However, for me personally, I very strongly prefer that my character receives either a happy ending or gets a chance to provide a heroic sacrifice that makes a difference.
In my experience as a GM, most players seem to prefer that their character ends in some sort of position that can be called a "win". What that means varies by person, but for most people it means either a happy ending or a heroic sacrifice.
Even among people that can happily accept a "bad" ending, I suspect the threshold to qualify it as a good game is higher when the ending is bad. In other words, in my experience, many people might accept a tragic ending if the journey to get there is equivalent to Hamlet, but accept a happy ending readily even if the plot consists of go forth and hunt the creature of the week.
Agency matters.
First, railroad-v-sandbox is more of a spectrum than an either/or. Also, different players have different preferences along that spectrum (though currently the general trend seems to be closer to sandbox).
With that said, I think most players will accept a tragic ending far more easily if it comes from their own decisions than if they are railroaded into it. I think that applies even to people that are willing to accept more rails.
Personally, I don't mind a certain amount of rails. As a GM I find it much easier to develop well rounded NPCs and an in-depth plot if I can impose some constraints on the players. I don't try to railroad everything, but if I spent time developing plans for say Menzoberranzen and the players try to head to Thay I will subtly or even not-so-subtly steer them back to Menzoberranzen for instance. As a player, I prefer in-depth plots and I'm willing to accept a certain amount of rails if it makes it easier for my GM. But, if I accept rails I expect that to bring me to a good destination with interesting scenery along the way. If the destination at the end of the rails is miserable, I will probably not be happy and I will (pointing at the rails, even if only in my own mind) think that it is directly because of the GM.
On the other hand, if it is a sandbox style game and I wind up in a bad position because the world reacted logically to my choices than I may not be happy, but I won't think it is directly because of the GM. Similarly, if my character dies in battle because I made strategic mistakes or I just get unlucky with the dice, I again won't be happy, but I won't think it is because of the GM.
Player expectation matters
Player expectations also matter a lot. If I go in expecting a horror story, I won't be surprised if the ending is darker than if I go in expecting an adventure story. Personally, I will still prefer a happy ending, but how I define that may be...lower. A happy ending in a horror story may involve my character getting out alive or dying saving just one other person, while with an adventure story I expect my character to either be remembered as a hero of a region, retire in wealth, or even both.
In other words, if you, as the GM, expect a tragic ending ahead of time, you may get a better response if you telegraph that heavily or even say it bluntly ahead of time. The setting is one way, though not the only, to telegraph expectations. If the story is in Forgotten Realms, I probably expect an adventure story unless you tell me otherwise ahead of time. If the story is in Ravenloft or CofD, I probably expect horror.
Your specific situation
In my opinion, the plot you laid out does not make for a good game. It might make for a good story. Stories about the results of hubris are one of the classic tragic archetypes.
But a game is different. Most players will want some agency, and a story about hubris is particularly difficult in a game. Your players may not show any hubris or, as you lay it out, will show hubris only because you strongly railroaded them into it which won't feel like hubris to them. It won't feel like their characters are being punished for hubris, it will feel like their characters are being punished because the DM felt like they should be. That combination will probably be annoying even to players that in general are happy to accept rails and tragic endings.
Nota bene: I am well aware that the effects of hubris are an integral part of some systems such as Mage: The Awakening. However, that is a horror game with different expectations than D&D and it also has systems with dice rolls to reflect the impact of hubris to allow the Storyteller to push off much of the responsibility for adjudicating hubris specifically. Personally, even in Mage: The Awakening I strongly prefer a "happy" ending, its just that what qualifies as a happy ending may be darker and with more collateral damage than the way I would define it in D&D.