Intros to Fallout and Fallout 2; Kerghan's final speech in Arcanum was extremely strong and to think of it, the whole story of Little Big Adventure had a lot of value, both in what it directly told and in its wonderful more subtle parts; it is a story that both children and adults can equally enjoy.
From what I recently played, nothing left a strong impression story-wise. Maybe the ending of Icewind Dale, because it had caught me entirely off-guard. Still, it only was a mild impression because by then the whole experience had left me very indifferent. In Icewind Dale, to give a very crude comparison as I see it - you take on the role of adventurers who work for the cowboys, to get rid of the Indians. One of the few games where I actually disliked the story.