i'd say it varies from game to game for me. when you are in a game that has been run and gun the entire time, then you suddenly encounter an enemy / obstacle that you cannot defeat and must sneak by (with poor mechanics since the game wasn't designed for that), those missions are very annoying.
on that same vein though, escort missions, especically those with poor NPC AI where they either walk at 2MPH or sprint head long into enemies in some poor attempt to create tension, also tend to annoy me.
in other words, i enjoy the sneaking and escort missions provided the game has been develeoped well enough to have them incorporated properly.