The veteran Algerian diplomat had long threatened to leave, just as his predecessor, former UN Secretary-General Kofi Annan, did in 2012. Annan quit after six months as the UN and Arab League joint special representative on Syria, slamming the UN Security Council for failing to unite behind his efforts.
Brahimi is due to brief the 15-member Security Council later on Tuesday.
"It's not very pleasant for me. It's very sad that I leave this position and leave Syria behind in such a bad state," Brahimi told reporters after Ban announced his departure.
"Everybody who has responsibility and an influence in the situation has to remember that the question is how many more dead? How much more destruction is there going to be before Syria becomes again the Syria we have known," he said.
More than 150,000 people have so far been killed in the Syrian conflict. Some 2.5 million people have fled abroad, while 9 million people inside the country need help - including nearly 3.5 million who have no access to essential goods and services.
Ban praised Brahimi's perseverance in the face of "impossible odds, a Syrian nation, Middle Eastern region and wider international community that have been hopelessly divided in their approaches to ending the conflict."
"That his efforts have not received effective support from the United Nations body that is charged with upholding peace and security, and from countries with influence on the Syrian situation, is a failure of all of us," Ban told reporters.
The UN Security Council has been split on how to deal with the war in Syria. Syrian ally Russia, supported by China, has blocked three resolutions that would have condemned President Bashar al-Assad's government, threatened sanctions and called for accountability.