Even though I feel compelled to dive into all the fun things about stealth (and indeed I sort of already had in the last post), I think that it would be more elucidating to cover some of the situations where stealth goes bad. I'm among the first to admit that while there's a lot of fun and interesting things that can be done with the mechanic, that if used improperly it's one of the things that players can often loathe the most in games.
By far the most common way to make stealth into a burden rather than a blessing is to essentially cram it down the player's throats. There's a lot of simpler flash games on Newgrounds that do this, and due to their nature can even get away with it and be fun, but I think that people might know what I'm getting at here. Games that foist a "if you're spotted you lose" scenario on you when there are other options. There are many reasons games do this: lack of imagination, to artificially ramp the difficulty higher than it should be, or even because the designers thought it would just be a good idea. Let me tell you that it almost never is.
See, stealth is best when it's recognized as a tool, and should always be considered as one tool among many available options given to the player. Even in some stealth based games like Metal Gear Solid being spotted doesn't mean the end of the world; it does mean that you're going to have a tougher go of things, but you can fight your way through. With some games though, it's either a mission failure, a black mark that can lead to one, something that makes going forward insurmountably difficult, or a combination of the last two. A game that was -- in my assertion -- bad for this was the first Splinter Cell. I know that they were trying to go for a more realistic stealth game, but the "three alerts and it's game over" thing that a lot of the levels had made traversing them absolutely ridiculous and tedious.
Basically, when stealth becomes the first, last, and only option, people stop seeing it as fun and start seeing it as suffocating. Being spotted and sent back to the last checkpoint (being rendered into a bloody paste is optional, but always disheartening) is one of the simplest ways short of an escort mission to get people to throw up their controllers in disgust and walk away.
That's not to say that a game can't be based mostly around stealth. Just make sure that the player has other options, and that being sneaky is a nice way of being rewarded, instead of making a lack of stealth a reason to be massively punished.