This is complicated by the history of the applicable statutes and regulations involved in all this, as well as the way that a "District Attorney" and a "U.S. Attorney" are related to prosecutorial functions for the state and federal government respectively here.
The legal language in the current regulation changed the title to that of "Special Counsel", but this is not counsel in the sense of a barrister or other lawyer giving legal advice to a client. In the current language of the regulation from 1999, the "Special Counsel" is fully equivalent to a federal "U.S. Attorney" in powers. And by "U.S. Attorney", I do not simply mean any old American lawyer; this is a particular job in the federal government. These are fully empowered federal prosecutors, able to convene grand juries, submit indictments, and all the rest.
This 1999 "Special Counsel" is appointed when the U.S. Department of Justice determines that a particular criminal investigation must take place outside the normal Department of Justice for reasons such as conflicts of interest. Typically this means that the DoJ would otherwise need to investigate someone or something within its own chain of command. There are a few special reporting requirements involved in this position that differ from that of a normal "U.S. Attorney", and their tenure is also different.
CNN has answered your question here:
Is a special counsel and special prosecutor the same thing?
Almost, but there are some key differences. When people were calling for a special prosecutor in this case, they used the term interchangeably with special counsel.
But the former is an expired term. In the past, a special prosecutor was typically appointed by the president, but following the Watergate scandal that brought down President Richard Nixon, the rules changed to remove this direct power from the president.
After several changes in the law, the role of special prosecutor was eventually scrapped, and a new law in 1999 created the role of special counsel.
This role can only be appointed by an Attorney General, or an acting Attorney General.
A special counsel must also be a lawyer, where in the past, some special prosecutors had other professional backgrounds.
So we’re back to the situation where it is once again possible for the President to be able to order someone to fire this "Special Counsel" — even if, just like Nixon, he has to keep forcing the resignation of many of his own direct reports until he comes to someone who will carry out his orders. Although we have reason to believe that scenario is especially unlikely to happen in this case, only time will tell.