A little, but not really. New Londo isn't really frightening, it's just dreary. The ghosts are gimmicks: at the point in the game you usually go to New Londo they're really easy to kill provided you don't forget to pop Transient Curses. The level design is pretty straightforward. Once you get down below the Darkwraiths are intimidating at first, but they're basically just like the black and silver knights you've fought plenty of at this point in the game.
Latria, specifically 3-1, is a totally different story. It's dark and mazelike, first of all: it's very, very easy to get hopelessly lost, or to fall to your death down a pit. The sound design, as has been mentioned, is phenomenal: as you're wandering around lost you hear the moans of the prisoners, the screams of Lord Rydell (though you won't know who it is), the tinkling bells of the mindflayers, and sometimes out of nowhere operatic singing. The "hollow" prisoners moan and follow you around worshipfully but don't attack, so you get into the habit of ignoring them - except some of them do attack, without warning, when you enter their cells. The mindflayer guards are absolutely perfect. They're terrifying, and so your instinct is to play extremely cautiously, but that's exactly what'll get you paralyzed and brainsucked: the best way to kill them is to just bite the bullet and rush up to them and melee them down, which is exactly the opposite of everything Souls teaches you.
3-1 is the most scared I can ever recall being in a game, and that's including survival horror games like Dead Space and Amnesia.