Actually, it would be easier to look at the other end of the exact sequence. Namely, your map $f^*$ is trivial implies the map $g^*: Hom_S(T,Q) \to Hom_S(M,Q)$ is an isomorphism.
Now, since $M$ is projective, the support of $Hom_S(M,Q)$ is equal to the support of $Q$. Thus we have $Supp(Hom_S(T,Q)) = Supp(Q)$, which implies $$Supp(T) \supseteq Supp(Q) \ \ (1)$$
When $Q$ is simple, (so $Q=S/m$ where $m$ is a maximal ideal) as you allude to in the last paragraph, then $(1)$ is also sufficient, provided that the surjection $M \to T$ is minimal when localizing at $m$, as you can easily check for your self.
Another situation when $(1)$ also suffices is when $T,Q$ are both cyclic $S$ module and $M=S$ (you always need the map $M\to T$ to be minimal, may be that what you meant by "non-trival" surjection?)
Other than what described above, I think what you want will fail most of the times, even with $(1)$.