Sporting got the two-goal winning margin they needed to reach the last 16 for the first time since the 2008-2009 season behind Group C winners Ajax.
The away defeat means third-placed Dortmund are heading to the knock-out stages of the Europa League.
"They were more consistent and uncompromising in the decisive situations," said Dortmund coach Marco Rose.
"We conceded goals too easily and made too many mistakes."
The Germans were weakened by the continued absence through the injury of prolific forward Erling Braut Haaland.
Sporting winger Goncalves, who also scored twice in the 4-0 thrashing of Besiktas, could have finished with a hat-trick but his late penalty was saved only for Pedro Porro to head in the rebound.
Porro had the last laugh having been kicked by Dortmund's second-half replacement Emre Can, who was sent off for the foul with 16 minutes left after tempers flared.
Dortmund's Dutch striker Donyell Malen gave the visitors brief hope with a consolation goal in stoppage time.
It was a bitter night for the Germans, who have lost three consecutive Champions League games for the first time.
"We are out, that's the bitter truth," moaned Dortmund captain Marco Reus.
Already missing Haaland, sidelined by a long-term hip injury, Dortmund were also without key defender Mats Hummels, suspended after being sent off in the 3-1 home defeat to Ajax.
Dortmund lost Raphael Guerreiro to injury in the warm-up and his place at left-back was filled by Nico Schulz, who played an unfortunate part in the opening goal.
With half an hour gone, Schulz misjudged a long ball that bounced into the path of Goncalves, who drilled his shot past Dortmund goalkeeper Gregor Kobel to fire Sporting ahead.
The hosts doubled their lead six minutes from the break when Dortmund defender Manuel Akanji cleared the ball only as far as Goncalves who hit the back of the net from the edge of the area.
Dortmund could have gone into the break level, instead of 2-0 down as Malen hit the post, then had a shot cleared off the line.
Can replaced the hapless Schulz at halftime.
Dortmund's hopes faded after Can was shown a straight red for fouling Porro, while centre-back Dan-Axel Zagadou was also booked for his part in the resulting pushing and shoving.
Zagadou then gave away a penalty when the VAR spotted he had kicked Sporting striker Paulinho.
Although Kobel denied Goncalves a hat-trick by saving the initial spot-kick, Porro was on hand to head the blocked shot into the net to make it 3-0 with nine minutes left.
Malen's consolation came after Dortmund captain Marco Reus beat the defence, but it was too late.