Sebastien Thill snatched victory for the debutants with a stunning final-minute strike, sparking scenes of wild celebration from the Moldovan champions.
Jasurbek Yakhshiboev’s first-half header had given them the lead before Karim Benzema levelled with a penalty.
But Thill’s goal was a worthy way to clinch a historic victory – the Luxembourg midfielder’s half-volley reducing the home crowd to silence as it flashed past Thibaut Courtois.
“It’s the best and most important goal of my career, that’s for sure,” said Thill, who was last year playing on loan at FC Tambov – a Russian club which in May was dissolved after declaring themselves bankrupt.
“The side were so brave with how we played and luckily enough I was able to score a bit of a stunner.
“After this match, we all went crazy. There are a lot of foreigners in the club, we come from all types of countries. It’s our strength.”
This was not what Real had planned as the Bernabeu hosted Champions League football for the first time in 580 days, and shouts of celebration from Sheriff players were the soundtrack to a monumental upset.
Sheriff captain Frank Castaneda said victory was a “dream come true” and that they are focused on reaching the knockout stage.
“We are dreaming of getting into the last 16 and that’s our objective,” he said.
The home side dominated every facet of the game, having 76% possession and 31 shots – 11 of which were on target.
But Sheriff played with supreme composure and were ruthless when presented with even a hint of a chance – scoring twice from just three shots on goal.
Real Madrid boss Carlo Ancelotti said he was “a little sad” as he felt Real “deserved to win”.
“You could say we had bad luck, we lost the game on the small details, they scored their goals on the counter-attack and from a throw-in,” the Italian said. “We had a lot of shots at goal but sometimes luck deserts you in games.
“Everything went well for them; everything went wrong for us.”
The match in the Spanish capital was Sheriff’s 10th Champions League fixture of the season as they came through four rounds of qualifying to reach the group stage for the first time.
A milestone win against Shakhtar Donetsk earlier this month sent them top of Group D, and this remarkable triumph ensures they remain unlikely leaders ahead of illustrious opposition that also includes Italian champions Inter Milan.
“Our attention now turns to Inter and we’ll work hard in order to ensure we keep getting positive results,” Sheriff captain Castaneda said.
While they are still minnows on the European stage, they have won 20 of the 22 Moldovan league titles they have contested, and in the two league games before heading to Madrid, they scored 11 goals and conceded none.
Tuesday’s result means they become just the second club to win on their first Champions League trip to the Bernabeu, replicating Arsenal’s achievement in 2005-06.
And not since Leicester City in 2016-17 have a side won their opening two games on their debut in the competition.
Real had intended to make history of their own on their return to the Bernabeu, which has been refurbished, as they are just one short of 100 Champions League victories at home.
But the competition’s most experienced club – and one which has attempted to reshape European club football as a founding member of the controversial Super League – were beaten.