What is the classification of this hadith or story? And what book is it found in?
It was narrated that in the days that Moses, peace be upon him, wandered with Bani Israeel (the Jews) in the desert, an intense drought befell them. Together, they raised their hands towards the heavens, praying for the blessed rain to come. Then, to the astonishment of Moses, peace be upon him, and all those watching, the few scattered clouds that were in the sky vanished, the heat poured down, and the drought intensified. It was revealed to Moses that there was a sinner amongst the tribe of Bani Israeel who had disobeyed Allaah, The Exalted, for more than forty years of his life. “Let him separate himself from the congregation,” Allaah, The Exalted, told Moses, peace be upon him. “Only then shall I shower you all with rain.” Moses, peace be upon him, then called out to the throngs of humanity, “There is a person amongst us who has disobeyed Allaah for forty years. Let him separate himself from the congregation, and only then shall we be rescued from the drought.” That man waited, looking left and right, hoping that someone else would step forward, but no one did. Sweat poured forth from his brow, and he knew that he was the one. The man knew that if he stayed amongst the congregation, all would die of thirst, and that if he stepped forward, he would be humiliated for all eternity. He raised his hands with a sincerity that he had never known before, with a humility that he had never tasted, and as tears poured down on both cheeks, he said, “O Allaah, have mercy on me! O Allaah, hide my sins! O Allaah, forgive me!” As Moses, peace be upon him, and the people of Bani Israeel waited for the sinner to step forward, the clouds hugged the sky and the rain poured down. Moses, peace be upon him, asked Allaah, The Exalted, “O Allaah, You blessed us with rain even though the sinner did not come forward.” And Allaah, The Exalted, replied, “O Moses, it is for the repentance of the one due to whom I had forbidden it to you.”

All perfect praise be to Allah, The Lord of the worlds. I testify that there is none worthy of worship except Allah and that Muhammad is His slave and Messenger.

After investigation, we have not come across this story in any books of the Sunnah with the exception of Ibn Qudaamah who reported it in his book Kitaab At-Tawwaabeen. However, he did not state any chain of narrators so that we can know who had narrated it; rather, he narrated it in the passive form, saying, “It was reported,” without mentioning who reported it.

It might very well be from the Israa’eeliyyaat (stories from Jewish sources), which we neither believe in nor deny.

Both Al-Ghazaali in Ihyaa’ ‘Uloom Ad-Deen and Al-Qurtubi in his Tafseer (exegesis) reported a similar story reported by Ka’b Al-Ahbaar; this may strengthen the probability that it is from the stories of the Children of Israel.

Allah knows best.