The Perth side battled back from a goal down to eliminate the former Champions League quarter-finalists and will next face FC Minsk in the third qualifying round.
Frazer Wright's winner had given Saints a slender advantage after the first-leg, but Alexander Soderlund levelled the tie after only four minutes at McDiarmid Park.
This early setback failed to unsettle the hosts and Stevie May's strike regained the lead for the hosts, who held off a late onslaught from Rosenborg.
"It was magnificent. It could not have gone any better for us. I think it will only be in the morning that we will realise the magnitude of the result," said Wright.
"Now we face a rush to find out about Minsk.
"We want the journey to continue. I told the players they would look back on this night with a lot of pride and satisfaction long after they've finished playing.
"But we dared to believe that we could beat Rosenborg and now we're saying let's see how far we can go. You want to get through the next round and the play-off and into the Europa League group. And on the standard they've set over these two games that wouldn't be unreachable."
Matchwinner May struggled to recall his goal but hopes his side can pull off another memorable result against Minsk.
"I don't remember much about the goal," he said. "I know David Wotherspoon played it in but it was just a nothing ball, the defender was in front of me and I threw a leg around the side of him and managed to lob the ball over the keeper and there's no better feeling when the ball goes in the net.
It was a goal made in Perth with two local lads.
"I'm not too sure what FC Minsk are like but I doubt they'll be as high a calibre as Rosenborg. But that's not to say they won't cause us problems or be more of a threat, but we believe we're good enough and if we work as hard as we have then we can progress."