Tension and Fear are two of the hardest things to inject into a game. No matter how much you try, ultimately, your players are aware that they're just sitting around a table and having fun while a game is going on. This can make certain scenes hard when your players aren't the type to fully dive into the game world. However, there are some solutions to the problem.
Solution 1: Go All Out
One of the best ways I've seen GMs add tension to games is with sound. But this is harder to do than it sounds. For starters, the GM who did this was taking a sound design course and basically added about twelve hours to his preparation time just to make the sound loops he needed for a derelict space ship the PCs were exploring.
Sound is a strong tool for this.We use our ears for a lot, including placing us in an environment. More to the point, our ears are capable of listening on multiple levels even if we aren't consciously aware of it. You can listen to what your GM is saying all the while your ear is taking in the creaks and moans of a haunted mansion.
Add to that some theatrical lessons for dialogue. Talking low and slow, or using long sentences than changing to faster cadence, and you can get your players hooked before they know what hits them.
Solution 2: Limited Resources & Win/Loss Conditions
People who play RPGs are aware that, ultimately, a lot of the "game" part of an RPG comes down to resource management. You have a limited number of Hit Points, Wound Points, Ammo, Spells, Items, and other resources at your disposal. Even combat is based around how you spend your various actions you have to the greatest effect.
The fun thing about this is that RPGs have an inherent loss condition. What happens when you're out of resources? What happens when you're out of resources and a fight finds you? You lose. Your PC dies. Because of this, as a character's resource meter goes into the critical, and the player knows they can't stop or go back, they naturally become more tense.
Every spell becomes more precious. Every round must be used to utmost effect. Every attack or damage hurts.
So what happens when you do this to a player or players without combat? The stress costs them HP (or better yet, mental wounds if the system has it.) The lower it gets - the less they can do - the more tension they'll feel. After all, a threat could be around any corner, and they can't take on what they can normally take on.
Solution 3: Something Valuable At Risk
The third solution is harder, or easier, to pull off depending on who is in your game. Something valuable to put at risk can be other PCs, or it can be something in the game if your PCs actually value things in the game.
With my groups I can do this with important NPCs - provided said NPC is actually important to the PC in question - or other items that the PCs feel they need in order to progress. In other games this may be more or less true, and that's perfectly ok.
Do what you need for your game, but putting things at risk that the PCs want safe is one way to make them feel real tension and fear. Especially when it is genuinely at risk.