Well, if we are to include music, we have to have some Final Fantasy in here:
FFVII: The abandoned train courtyard near Midgaard fantastic music and atmosphere... how great it will be to visit that again in stunning next-gen graphics. Many other areas in FF VII as well.
FF VI: Gaaah, I must be getting old, I can't remember the name of the village, in a game I played just 15 years ago.... but I do appear to remember it is in the north west part of the world map somewhere, it has a fantastic tune each time you enter that village.... maybe someone who remember better can help me!
There are a lot of great areas in many great games, but if I had to pick one it would be the underdark area in Baldur’s Gate 2. The chapter intro movie sets it up just right. You step into this completely alien environment and there is a constant sense of dread as you explore. Every step you take feels like it could be your last, and the further you go, the deeper it all intensifies. I still remember the actual relief I felt when I first got back out into the sun. Not a happy place, but very well designed.