I found Ravenholm scary. The first time through. When I was thirteen years old.
Thank god some people on this forum at least are cultured enough to mention the haunted hotel from Bloodlines. Actually, there were quite a few creepy places in that game, but the hotel is easily the most famous and well made.
Also, someone mentioned Duskwood from World of Warcraft, and honestly, I have to agree. Say what you want about that game, but it's actually really atmospheric at places. At least if other players are absent for the most part.