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): THeThe 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.