The commentaries offer various explanations as to what Moses’ (and Aaron’s) sin was. Rashi says that it was that he struck the stone instead of only speaking to it, as G-d had instructed. According to Maimonides, it was the fact that he got angry, and said, "Hear now, you rebels."
Nachmanides questions both explanations, pointing out that: a) G-d told Moses to take along his staff, implying that he was supposed to strike the stone (as in Exodus 17:6); b) It does not say here that Moses’ words were spoken in anger; whereas on other occasions we find Moses getting angry (cf. Numbers 31:14), and we don’t find that he was punished for it; c) the verse explicitly says that his sin involved a lack of faith–not disobedience or anger. Nachmanides therefore explains the sin as lying in Moses’ declaration, "Shall we get you water out of this rock?" when he should have attributed the miracle solely to G-d.
Chassidic Master Rabbi Levi Yitzchak of Barditchev sees Maimonides’ explanation (that he rebuked the people angrily) and Rashi’s explanation (that he struck the rock instead of speaking to it) as two sides of the same coin. If a leader’s influence on the community is achieved through harsh words of rebuke, than his relationship with the environment is likewise: he will have to forcefully impose his will on it to get it to serve his people’s needs and their mission in life. If, however, he influences his community by lovingly uplifting them to a higher place so that they, on their own, will desire to improve themselves, the world will likewise willingly yield its resources to the furtherance of his goals.
But had not Moses previously said something that was worse than this? For he said (Numbers 11:22): "If flocks and herds be slain for them, will they suffice them? Or if all the fish of the sea be gathered together for them, will they suffice them?" Faith surely was wanting there too, and to a greater degree than in the present instance. Why then did G-d not make the decree against him on that occasion?
Let me illustrate. To what may this be compared? To the case of a king who had a friend. Now this friend displayed arrogance towards the king privately, using harsh words. The king, however, did not lose his temper with him. After a time he rose and displayed his arrogance in the presence of his legions, and the king passed a sentence of death upon him. So also the Holy One, blessed be He, said to Moses: "The first offence that you committed was a private matter between you and Me. Now, however, that it is done in the presence of the public, it is impossible to overlook it." Thus it says: "[Because you did not believe in Me] in the eyes of the children of Israel."
[At the burning bush,] Moses said: "But, behold, they will not believe me" (Exodus 4:1). Said G-d to him: "They are believers, the sons of believers; you, however, will ultimately fail to believe. They are believers, as it is written, ‘And the people believed’ (ibid. v. 31); the sons of believers, as it is written ‘And [Abraham] believed’ (Genesis 15:6); whereas you will ultimately fail to believe, as it is written: ‘Because you did not believe Me.’"
(Talmud, Shabbat 97a)