From my experience, I've found that a challenging game with a worthy payoff can be immensely enjoyable, but those are the exceptions and certainly not the rule.
However, I've also noted that a game doesn't need to be challenging to be entertaining, only satisfying, and I don't think this is necessarily a bad thing.
Look at classic turn-based RPGs: they're stupid easy to beat, and that's because the only thing separating you from victory is a certain amount of time spent grinding random encounters.
And yet, there are many people who cite these same RPGs as some of the best gaming experiences they've ever had. Can anyone argue that they're wrong when all they're expressing is their enjoyment and, more importantly, their patronage?
Games can strike a balance in difficulty through a number of means. There's the "difficulty selection" option which generally works well, but in the case of Nintendo games, they usually have situations where facing greater difficulty results in greater reward but isn't required for game completion.
There's getting all the medals in SF64, 120 stars in SM64, finishing the Metroid games in under 2 hours, etc. These add an extra element for the gamers which want more of a challenge while not harming the fun factor for the gamers who want less of a challenge.
Wii Sports is also the same way. The game is ridiculously easy to pick up, but competitive players who have mastered all of its depth can crush lesser players with ease.
I think the balance can be found and can be fun, it's just that developers will need to go that extra mile to ensure their games can appeal to new players while also having ample depth for hardcore gamers to explore.
In the end, the goal is to make sure everyone has fun playing your game.