When is the $(F_!,F^*)$ counit a natural isomorphism?

It appears to me that the condition on $F:\cal C\to\cal D$ would be:

For any morphism $s: a\to b$ of $\cal D$, the following category is connected:

An object consists of a $\cal C$-object $c$ and a factorization $a\to F(c)\to b$ of $s$.

A morphism $c_1\to c_2$ is a $\cal C$-morphism such that the induced map $F(c_1)\to F(c_2)$ is compatible with the maps from $a$ and to $b$.

I don't recall ever having run into this sort of 'two-sided comma category' before, but it seems to be the answer.

I got this by choosing $G$ from $\cal D$ to Set to be represented by the object $a$ and thinking about the fiber of the map $\epsilon: (F_!F^*G)(b)\to G(b)$ over the element $s$.

In general, the counit of an adjunction is an isomorphism if and only if the right-adjoint is fully faithful (dually the unit is an iso iff the left-adjoint is fully-faithful). So, your question is easily seen to be equivalent to asking "When is $F^{*}$ fully-faithful? In topos-theory lingo, when is the induced geometric morphism $\mathbf{F}:Set^{C^{op}} \to Set^{D^{op}}$ satisfies $F^*$ is faithful, then $\mathbf{F}$ is said to be a SURJECTION of topoi. In this setting, this is equivalent to every object in $D$ being a retract of an object of the form $F(C)$.

Ok, so how about asking for $F^*$ to also be full? $F^*$ being faithful AND full means you are looking at what is called a CONNECTED geometric morphism of topoi. What properties $F$ do we need to ensure this? This is in general a hard problem. However, there are at least sufficient conditions. Given $F$, you first construct the category $Ext_{F}$ of "F-extracts"- these are quadruples $(U,V,r,i)$ with $U \in C$, $V \in D$, $r:FU \to B$, and $i:V \to FU$ such that $ri=1$, with the evident morphisms. There is a canonical functor $\tilde F:Ext_{F} \to D$ which sends $(U,V,r,i) \mapsto V$. Denote by $Ext_F(V)$ the fiber over $V$ of this functor. Then if $\tilde F$ is full and each $Ext_F(V)$ is a connected category, then $\mathbf{F}$ is a connected morphism.

This is in "Sketches of an Elephant" C.3.3.