Whether something is an insult or not is more to do with context than actual words.
We can tell you that weak means:
lacking in rhetorical or creative force or effectiveness: a weak reply to the charges; one of the author's weakest novels
deficient in mental power, intelligence, or judgment: a weak mind
deficient, lacking, or poor in something specified: a hand weak in trumps; I'm weak in spelling
so it's definitely a negative word. Probably "lacking in effectiveness" is the most apt for the Ms. Yates quote.
What makes it an insult is saying something negative about an area somebody thinks (or would like to think) they are good at. If somebody said "Andy is rubbish at table football" I wouldn't take it as an insult, because I agree with them; but if someone said "Andy is rubbish at throwing frisbees" I might be slightly more insulted, because I think I'm quite good at it. Even then, if someone I knew and liked was saying it as a joke, I wouldn't be insulted as I would enjoy the joke. But if someone I didn't like said it, I would be insulted.
In the words of Mari-Lou A (from a comment) the statement about Ms. Yates:
"is insulting because the insinuation is that Sally Yates was an incompetent, unprofessional and unqualified Deputy Attorney General. It is an insult on her years of service, her professionalism and career."
Note that it is insulting because it says she was no good at her job. Replacing "weak" with "no good at" doesn't stop it being an insult.