Rodríguez received an ovation when he was substituted early in the second half, but the Santiago Bernabéu crowd left disappointed as Madrid let victory escape.
Karim Benzema put Madrid ahead in the 82nd minute but Sergi Guardiola stunned the hosts with an 88th-minute equalizer, stopping Zinedine Zidane's team from adding to its winning start to the league.
Still without the injured Eden Hazard, Rodríguez was Madrid's main attraction in a match that marked the playmaker's return to action more than two years after joining Bayern Munich on loan.
"After a long time, I was back at the Bernabéu," Rodríguez said on Twitter. "It was a unique feeling. Thanks to the fans for the support. We will keep working to improve."
The Colombia midfielder created several chances and came close to scoring on more than one occasion before being substituted by Vinícius Júnior in the 57th.
"It was a good match for James, especially in the first half," Zidane said. "We replaced him because he had a little bit of pain and we didn't want to risk anything serious.'"
Madrid, which opened the season with a 3-1 win at Celta Vigo, is trying to rebound from one of its worst campaigns in decades and is seeking a first league title since 2017.
Benzema broke the deadlock by scoring his 150th Spanish league goal with a well-placed low shot into the corner after controlling a Raphael Varane pass just outside the area. Guardiola equalized from close range after the Madrid defense was caught off guard from a late counterattack.
"We leave with a bad taste in our mouths," Zidane said. "We did the most difficult thing, which was to score while we were not playing our best, but then in three or four minutes they equalized when our defense was not positioned correctly."
Zidane started with a different lineup from the opener in Vigo, using Rodríguez in midfield and Francisco "Isco" Alarcon in attack along with Benzema and Gareth Bale in a move that relegated Vinícius Júnior to the bench.
Bale started for the second match in a row after the team failed to reach a deal for his transfer in the offseason. The Wales forward produced a good performance in the opener but was quiet against Valladolid.
The visitors had a couple of good chances to take the lead on counterattacks late in the second half. The team owned by former Brazil star striker Ronaldo had begun the season with a 2-1 win at Real Betis.
OTHER RESULTS
Promoted Osasuna drew 0-0 with Eibar, while Celta Vigo defeated Valencia 1-0 with a winner by Gabriel Fernández early in the first half. Athletic Bilbao followed its opening home win against Barcelona with a 1-1 draw at Getafe. Raúl Garcia opened the scoring for the visitors in the sixth minute before Jaime Mata equalized in the 12th.
On Sunday, two-time defending champion Barcelona hosts Real Betis, while Atlético Madrid visits Leganés.
Sevilla won 1-0 at Granada on Friday and sits top as the only team with two victories.