Praise be to Allah, the Lord of the Worlds; and may His blessings and peace be upon our Prophet Muhammad and upon all his Family and Companions.
The Hadith with the wording you mentioned in the question is not in Sunan al-Tirmizi, but it is reported in Mu’ajam al-Tabrani (al-Sagheer and al-Kabeer). Sheikh al-Albani classified it as weak in his book "al-Targheeb wa al-Tarheeb". He also discussed the Hadith thoroughly in his book "al-Tawassul: its rulings and kinds". He said in this book: ‘…In brief the story is very weak and not true', and then he brought forward the technical reasons, due to which the Hadith becomes an unauthentic one’.
As for the narration of al-Tirmizi, it is with the following words:
Imam al-Tirmizi transmitted it, saying this is a Hasan, Saheeh and Gharib tradition. Imam Ahmad also reported this Hadith, Sheikh al-Albani graded it as Saheeh, and Sheikh Shuaib al-Arnaut said: 'its chain is Saheeh and the narrators are trustworthy.'
The Hadith of al-Tirmizi is not like the Hadith of al-Tabrani there are many differences between the two of them.
1. The last Hadith is sound while the first one is weak. Don’t believe those who consider the first Hadith Saheeh and also do not be deceived by the ruling of al-Tabarani that the Hadith is correct, since al-Tabarani did not issue the ruling about all the story that is mentioned in the Hadith; rather he gave ruling about the Hadith only. For more details refer to the book of al-Tawassul of Sheikh al-Albani.
2. A blind man came to the Prophet (Sallallahu Alaihi wa Sallam) and made this request. “Ask Allah to grant me cure”. He asked the Prophet (Sallallahu Alaihi wa Sallam) to make Du’a for him in his lifetime which is permissible. On the other hand, the story in the Hadith of al-Tabarani proves that the man took the Prophet (Sallallahu Alaihi wa Sallam) as mediator after his death which is forbidden.
3. The last words in the Hadith, “O Allah, accept his Shafa’h (intercession), his supplication in my favor” does not mean at all that he made the Prophet himself as mediator in his supplication.
Therefore, you may find out that appointing the Prophet’s (Sallallahu Alaihi wa Sallam) person, as mediator in his life or after his death is not permissible. However, it was permissible to ask the Prophet (Sallallahu Alaihi wa Sallam) in his lifetime to make Du’a for someone.
Allah knows best.