Trailing 2-1 after last week's bad-tempered clash at Eastlands, United turned the tie on its head with goals from Paul Scholes and Michael Carrick, only for their former striker Carlos Tevez to level it for City.
But with the aggregate score 3-3 and extra time looming, Rooney's 21st goal of the season crushed City's bid for a long overdue trophy and sparked ecstatic celebrations inside a crackling Old Trafford.
Holders United will take on Aston Villa at Wembley on February 28, a repeat of the 1994 League Cup final which Villa won 3-1.
"You like to win your derby games," United manager Alex Ferguson told Sky Sports. "The atmosphere here tonight and the fact it was a semi-final added a lot of spice to the match and to score so late was a special kind of celebration."
While Ferguson now has the chance to claim a 20th domestic trophy since taking charge of United in 1986, City's long wait for silverware goes on.
It was 10 years before Ferguson began his glittering reign at Old Trafford that City won the 1976 League Cup and despite being the world's richest club after being bought by Abu Dhabi billionaire Sheikh Mansour bin Zayed Al Nahyan, they remain in the shadow of their illustrious neighbours.
"We played the same level as them," said City manager Roberto Mancini, who was hoping to steer the club to a first final since 1981 just over a month after replacing Mark Hughes.
"We played well but I'm just sorry we lost. That's football. Now we have the FA Cup and the Premier League."
Fears of crowd disturbances after several flashpoints in the first leg did not materialise, apart from one incident when City's Craig Bellamy appeared to be struck by a missile, with a packed stadium absorbed by a classic Manchester derby.
FRENETIC OPENING
It took until the second half for the match to come to the boil after a frenetic opening period in which Tevez, scorer of two goals in the first leg and booed by United's fans, came closest to scoring with a diving header.
Mancini's side looked comfortable and had the first chance after the break when a rampaging Micah Richards forced a great save from Edwin van der Sar.
A yellow card for Tevez for a foul on Rafael stoked the atmosphere and United went in front after 52 minutes with a clinical finish by Scholes.
The goal seemed to suck the belief from City's players and they were on the ropes with 20 minutes to go when Carrick's precise finish after being teed up by Darren Fletcher put United ahead on aggregate.
Rooney should have buried City when he somehow missed the target from inside the six-yard area and that looked costly when Tevez pounced, cleverly flicking in Bellamy's cross with the outside of his right boot.
United would not be denied, however, and swarmed forward looking for a winner. Shay Given produced a superb save to deny Fletcher but could do nothing when Ryan Giggs curled in a magnificent cross which England striker Rooney nodded in.
"We started a bit slow but when we got at them they didn't know what to do," Rooney said.
