My favorite puzzles, the ones that have really stuck in my memory, mostly come from text adventures, and were neither obtuse nor "mechanical" puzzles. They were usually about figuring out a logical answer to a "real" situation, and a little bit of thinking outside the box to find the correct answer. Puzzles like that make me sit and bask in my victory for a moment, while acknowledging the author with a "Wow, that was so clever!"
I'll provide two examples from Legend Entertainment and one from Infocom. Warning: This will contain spoilers for Spellcasting 301, Deathgate, and Spellbreaker.
Example 1:
Spellcasting 301. You are Ernie Eaglebeak, hopeless nerd in charge of the task of finding some girls to attend to a party thrown by your fraternity. There is a group of girls standing around who are, shall we say, clearly better endowed with looks than brains.
In the game is a gizmo called a "studfinder". I had no idea what it was for, looked like a red herring, but handed it to the girls. They said "Wow, let's go find some studs!" but then I never saw them again. I had no idea what the gizmo was supposed to do, so I restored, turned it on, and wandered around the game with it. At some point, it started beeping. I did a thorough search of the room, and located... a wooden two by four.
Flash of insight! Bring the "stud" to the party, drop it off, and THEN go back and give the girls the studfinder. Minutes later, they locate the party, and comment: "Wow, these guys look like total geeks, but this thing is totally going wild, so they must be studs!" Hilarity ensues.
Example 2:
Deathgate. You are Haplo, a spellcaster with near godlike powers. The game has a tricky spellcasting system which involves tracing hexagonal runes in the air. You have a repertoire of spells that you can call up at any time, and the correct rune pattern will be displayed. Mostly, you don't have to mess with the runes; just click a spell and it will be cast.
Near the end of the game, you arrive at a magic mirror. No glass, just a duplicate of you standing in your path. You can't proceed because your mirror image is in the way. He moves when you do. You can't cast spells at the duplicate because the "mirror" stops all spells. Your mirror image casts spells when you do, or at least tries to: the game writes "You cast the spell. The mirror image traces the same runes in reverse, but it is not a spell so nothing happens." Some spells are symmetrical, so he can cast those.
There is one spell in the game that seems totally useless. It's sort of a "kill yourself" spell that doesn't work on anyone else. But -- Insight! -- if you carefully select the runes and lay them out BACKWARDS... what happens?
Example 3:
Spellbreaker. Near the end of the game, you come across a room that looks identical to a location that you have visited before, except that it looks much newer and less worn. You find you can't leave the room; you always die for some reason, though the description of your death is very cryptic.
You conclude that you have travelled back in time. The only way you can escape is to set up the room EXACTLY the way you found it the first time. This involves leaving an item in a locked cabinet (which becomes "a moldy book" when you found it in the future), breaking the door off its hinges, and leaving without a trace (by magic). You may not leave any other object behind, because that would change history, and that's how you get killed.
What do all these puzzles have in common? They are all examples of a surprising action that you (probably) couldn't come up with by accident. But you are not expected to read the game designer's mind; the clues are all there, you just need the insight to read them. Most importantly, they're not contrived, like a bunch of gears and switches in the middle of nowhere; they all MAKE SENSE in the context of the game's world.
