Maimonides gives a particularly compelling explanation. Maimonides explains the sin of Moshe was that he got angry and called the Children of Israel rebels. This gave the impression that G-d was angry with the Jewish people for their complaints about their lack of water, which in fact was not the case. At that point in the development of the Jewish people, they were only on a level to see water come from the rock after it had been physically hit. It was for this brief lapse into anger that Moshe is punished.
Anger is one of the worst of all character traits.
The Talmud says that if someone breaks something in anger, it is as if he has worshipped a strange god. The opposite trait of anger is intellect. Someone who loses his temper to such a degree that he would risk breaking something is no longer under the dominion of his intellect, which is a manifestation of his G-dly soul. Thus he is serving a strange god. The lesson which we can apply to ourselves from this episode is to learn to nurture our G-dly soul, i.e. to be in complete control of our mental facilities.