In Tefilas Geshem we say "Al HaSela Hach Vayeitzu Mayim"
translated by Artscroll as:
Remember the one [Moses] drawn forth in a bulrush basket from the water. They said, 'He drew water and provided the sheep with water.' At the time Your treasured people thirsted for water, he struck the rock and out came water.
So far I know, Moshe Rabeinu was taken to task over hitting the rock. Then why do we pray that in this Zechus we should get water???
Taken from Artscroll's Daily Dose ed. 1 vol. 14 p. 272:
Answer #1: The author of these piyutim believes that Moshe's sin was not hitting the rock, but for some other reason.
Problem: Although it is not a sin, why mention it at all? The prayer is about recalling the merits of people relating to rain.
Answer #2: It is referring to the incident in Beshalach (as in msh210's answer).
Problem (Baruch She'amar): Ein kateigor naaseh saneigor (a prosecutor can't become a defendant); the rock recalls both instances.
Problem (R' Schwab): The piyut uses the word "sela," which is the word used in Chukas; if it were referring to Beshalach, it should have said "tzur" (the word used there).
Answer #3 (Baruch She'amar): The premature death of tzadikim that comes as a result of punishment, atones (Yerushalmi, Yoma 1:1). Therefore, we do recall Mei Merivah, because it is a merit (since it atones)!
Answer #4 (R' Schwab): Although Moshe sinned, the water coming out was nevertheless a miracle and kidush Hashem, so it is also partially a merit.
It happened twice, you know. The first time he was supposed to hit it, did so, and wasn't taken to task. I have no source, but imagine the piyut is referring to this.
The following is taken from the Torah Tavlin parsha sheet - Chukas 5774
The word להקדישני explains Rashi, is meant to denote Kiddush Hashem. Had Moshe spoken to the rock, as Hashem commanded, and not hit it the way he did, it would have created a great sanctification of Hashem’s name, and the people would say, “Look how the rock, which does not speak or hear and needs no sustenance, fulfills the word of Hashem, so should we!”
R’ Simcha Sheps ZT”L points out that when we say the special prayer for rain, Tefillas HaGeshem, we mention the merit of Moshe hitting the rock: על הסלע הך ויצא מים, בצדקו חון חשרת מים - “He hit the rock and water came out; in the merit of his righteousness grant us an abundance of water.” If Moshe was punished for hitting the rock, why do we mention this as a merit? Commentaries on the prayer point out that these words are in reference to the end of Parshas Beshalach when the nation cried out for water and Hashem told Moshe that he was to hit the rock by Chorev and water will come out from it. (Shemos 17:7)
But why did Moshe hit the rock instead of speaking to it in this parsha? R’ Sheps learns from here the incredible depth of mesirus nefesh (giving one’s all) that Moshe had for his people. If he would speak to the rock and it would give water, it would be a terrible accusation on Klal Yisroel. When you talk to a rock it listens to Hashem, but when you talk to Klal Yisroel they don’t listen? So he hit the rock and jeopardized himself for the benefit of the people. Thus, בצדקו חון - in the merit of this righteous deed have favor, חשרת מים - and give us an abundance of water! However, although Moshe’s intention was entirely pure and self effacing, he was punished , because there is no place for one’s own calculations and reasoning in the face of Hashem’s command!
