Rashi brings from the Tanchuma that Hashem wanted it made clear that it was this sin, and this sin alone, that sealed Moshe's fate.
The natural way to read this is as a tragic mistake, judging by the high standards of Moshe Rabbeinu, a flawed decision that bore bitter fruit. If only Moshe hadn't done this! He would have led Klal Yisrael into Eretz Yisrael, and history would have been entirely different!
But Rashi in Devarim brings a Sifrei that casts an entirely different light on this episode.
Rashi in Vezos Haberacha (Devarim 33:8) says the following:
וללוי אמר תמיך ואוריך לאיש חסידך אשר נסיתו במסה תריבהו על מי מריבה.
Translation: And of Levi he said: "Your Tummim and Urim belong to Your pious man, whom You tested (teriveihu) at Massah and whom You tried at the waters of Merivah.
תריבהו וגו': כתרגומו. דבר אחר תריבהו על מי מריבה נסתקפת לו לבוא בעלילה, אם משה אמר (במדבר כ, י) שמעו נא המורים, אהרן ומרים מה עשו:
Translation: and whom You tried…: As the Targum renders it. Another explanation: “Teriveihu” — You brought about a pretext against him [Levi], for even if Moses [was punished with death and not permitted to enter the Land of Israel because he] said to Israel, “Listen now, you rebels!” (see Bamidbar 20:10), but [how do we understand why] Aaron and Miriam [were also punished with death and were not permitted to enter the Land of Israel] — what did they do [to deserve this]?- [Sifrei 33:8]
It appears that for Moshe Rabbeinu, his inability to enter Eretz Yisrael was a foregone conclusion that was waiting for a cause.
Chazal discuss this in several places, but I have to warn you that their explanation leaves us with as many questions as we came with. At the end of this discussion, I bring a safer alternative from the Maharitz Chayos.
For clarity, I'm labeling each source, starting with the Tanchuma.
The Tanchuma in Parshas Vayeishev (4), that says that Moshe Rabbeinu had absolutely no chance of entering Eretz Yisrael. If he hadn't struck the rock, he would have done something else. If he lived a perfect life and was absolutely blameless, perhaps someone he was responsible for would have done something wrong. The point is that the reality of his not entering Eretz Yisrael was determined long before he struck the rock at Meriva.
The Tanchuma says that certain human choices and/or the consequences of those choices are preordained. Hashem ensures that they come to pass through subtle manipulation. There is no suppression of free will, and Hashem does not force people to act in certain ways. Instead, Hashem puts people in positions where the likely result will be behavior that will move history in the desired direction, or that Hashem chooses a Middah (Din or Rachamim) that generates the intended consequences from a human action. The story of Mankind is teleological; certain events must ultimately occur, and seemingly unrelated or random events will inevitably lead to those ends. Here is the Tanchuma.
Then there is the Medrash Rabba (in MR Breishis 85:1 on Breishis 38:1)
gU directs us to the parsha of involuntary manslaughter and Ir Miklat.
Chaim B points out something that has to be addressed. Reb Meir Simcha in his introduction to Sefer Shemos asks, what does it mean that Hashem told Moshe Rabbeinu וגם בך יאמינו לעולם? But Moshe was a human being, and capable of making choices, good or bad, righteous or wicked. How could Hashem tell him that he would be forever trustworthy? Reb Meir Simcha answers that indeed at that point, Moshe Rabbeinu's bechira was taken away. He had reached the perfection human beings are capable of, and there was no point in his retaining his bechira. Reb Meir Simcah's words, with some ellipses, are
Or you could answer that his not having Bechira was limited to certain parameters, namely, where the wrong choice could affect his reliability vis a vis יאמינו לעולם, Klal Yisrael's trust in Moshe Rabbeinu.
See Divrei Dovid in Shemos 4:13, who says that Moshe Rabbeinu already knew in Mitzrayim that he most likely would not be allowed to enter Eretz Yisrael. See also Netziv here in 33:8.
Dr. Stone reminds us of the Gemara in Sanhedrin (111a) that says that Moshe's fate was sealed all the way back in Mitzrayim, when he said mei'az basi el Pharaoh heira la'am hazeh.
This discussion as presented above might not be useful for a drasha. One reason; it provides an excuse for every avaryan. It's not my fault! Hashem made it impossible for me to avoid the aveira! Second reason: people are מיאש, and they say, if no matter how high you climb, it is made impossible for you to make the right decision, if Moshe Rabbeinu, without Bechira, didn't satisfy what was expected from him, what's the point of even trying. לפום גמלא שיחנא, and if the strong donkey is punished for not carrying his heavier load just as severely as the weaker one is punished for not carrying his lighter load, then why bother becoming a stronger donkey?
Both reasons are devarim beteilim, but I personally know several people that would walk away with either one or both of these reactions.
So one needs to bear these in mind, and arrange the drasha to anticipate and prevent these reactions. For those people, perhaps you should just stick with the Maharitz Chayos on the Gemara (Sukkah 5a) מעולם לא ירדה שכינה למטה מעשרה. He says