The hadeeth referred to was narrated by Abu’l-Jawza’ Aws ibn ‘Abd-Allaah who said: The people of Madeenah were faced with a severe drought. They complained to ‘Aa'ishah who said: “Look at the grave of the Prophet (peace and blessings of Allaah be upon him) and make an opening facing the sky so that there will be no barrier between him and the sky.” They did that, then it rained until the plants grew and the camels grew exceedingly fat, and it was called the year of the exceedingly fat animals.  

Narrated by al-Daarimi (1/56), no. 92, in the chapter: How Allaah honoured His Prophet after his death. 

Al-Daarimi said: Abu’l-Nu’maan told us, Sa’eed ibn Zayd told us, ‘Amr ibn Maalik al-Nakri told us, Abu’l-Jawza’ Aws ibn ‘Abd-Allaah said: … then he quoted the hadeeth. 

This report is da’eef (weak) and is not saheeh. The scholar al-Albaani stated that it is weak and said in his book al-Tawassul (p. 128): 

This is a weak isnaad which cannot be used as evidence for three reasons: 

1 – Sa’eed ibn Zayd, who was the brother of Hammaad ibn Yazeed, has some weakness. Al-Haafiz said concerning him in al-Taqreeb: he is sadooq (truthful) but confused. Al-Dhahabi said in al-Mizaan: Yahya ibn Sa’eed said: (he is) weak. Al-Sa’di said: It is not evidence; they regard his hadeeth as weak. Al-Nasaa’i and others said: He is not strong. Ahmad said: There is nothing wrong with him; Yahya ibn Sa’eed did not like him. 

2 – The report is mawqoof and stops at ‘Aa’ishah, and it is not attributed to the Prophet (peace and blessings of Allaah be upon him). Even if it were saheeh it would not count as evidence, because it may be that it comes under the heading of ijtihaad of some of the Sahaabah who may make a mistake or get it right, so we are not obliged to follow it. 

3 – Abu’l-Nu’maan is Muhammad ibn al-Fadl who is known as ‘Aarim. Although he is thiqah (trustworthy), he became confused towards the end of his life. Al-Haafiz Burhaan al-Deen al-Halabi narrated it in al-Ightibaat bi man rumiya bi’l-Ikhtilaat following Ibn al-Salaah who mentioned him among the confused in his book al-Muqaddimah and said: The ruling concerning them is that the hadeeth of those who learned from them before they became confused may be accepted, but it may not be accepted from those who learned from them after they became confused or those concerning whom it is not known whether they learned from him before he became confused or after. 

I (al-Albaani) say: it is not known whether al-Daarimi heard this report before he became confused or after, so it is not acceptable and cannot be quoted as evidence. 

Shaykh al-Islam Ibn Taymiyah said, refuting al-Bakri: The report narrated from ‘Aa’ishah (may Allaah be pleased with her) about the opening of a hole towards the sky so that rain could fall (onto the grave of the Prophet (peace and blessings of Allaah be upon him)) is not saheeh, and its isnaad is not proven. Among the things which prove that it is false is the fact that during the lifetime of ‘Aa’ishah the house did not have any opening,  rather it remained as it was during the life of the Prophet (peace and blessings of Allaah be upon him): some of it was roofed over and some of it was open, and the sun used to shine into it, as it is proven in al-Saheehayn from ‘Aa’ishah that the Prophet (peace and blessings of Allaah be upon him) used to pray ‘Asr with the sun shining into her room when the shadow had not yet appeared. The apartment remained like that until al-Waleed ibn ‘Abd al-Malik expanded the mosque when he was governor, at which time the apartments were added to the mosque of the Prophet (peace and blessings of Allaah be upon him). At that time the room in which the Prophet (peace and blessings of Allaah be upon him) is buried was incorporated into the mosque and a high wall was built around the apartment of ‘Aa’ishah which contains the Prophet’s grave. After that the opening was made so that people could enter it when necessary in order to clean it. As for the opening being made during ‘Aa’ishah’s lifetime, this is an obvious lie. End quote. 

Secondly: 

This hadeeth is not proof for what the extreme Sufis believe about it being permissible to seek rain by the help of the Prophet Muhammad (peace and blessings of Allaah be upon him). You cannot find in the hadeeth anything which indicates that, by any stretch of the imagination. The most that can be said is that it shows how Allaah honoured his Prophet (peace and blessings of Allaah be upon him) after he died, as al-Daarimi described in his Musnad in the chapter heading under which he included this hadeeth. This is the blessing of his pure body and his honour before Allaah. But that does not mean that it is permissible for the Muslims to go to him and seek his help whilst he is in his grave. The Sahaabah (may Allaah be pleased with them) did not do that, rather they opened a hole in the roof of his room so that it might be directly open to the sky, but none of them asked the Prophet (peace and blessings of Allaah be upon him) for rain, and they did not speak to him concerning that. 

Shaykh al-Islam Ibn Taymiyah (may Allaah have mercy on him) said in Iqtida’ al-Siraat al-Mustaqeem (p. 338) 

Going to graves to offer supplication beside them, and seeking a response to du’aa’ offered there more than du’aa’s offered elsewhere, is something that was not prescribed by Allaah or His Messenger, and was not done by any of the Sahaabah, Taabi’een or imams of the Muslims. It was not mentioned by any of the earlier scholars or righteous men. Rather most of what has been narrated concerning that comes from some of the later figures, after the second century (AH). 

The companions of the Messenger of Allaah (peace and blessings of Allaah be upon him) were faced with drought several times, and other calamities befell them, but did they come and seek rain and seek help at the grave of the Prophet (peace and blessings of Allaah be upon him)? 

Rather ‘Umar went out with al-‘Abbaas and sought rain through him, i.e. through his du’aa’; he did not pray for rain by the grave of the Prophet (peace and blessings of Allaah be upon him). 

Indeed it was narrated from ‘Aa’ishah (may Allaah be pleased with her) that she uncovered he grave of the Prophet (peace and blessings of Allaah be upon him) so that rain might fall on it, because it (rain) is a mercy, but she did not pray for rain there, or seek divine help there. End quote. 

Thus it is clear that there is no evidence in this report for the Sufis that it is permissible to seek help from the Messenger (peace and blessings of Allaah be upon him) or to seek to draw closer to Allaah by means of him or his status.  

And Allaah knows best.