Sheaf cohomology is the right derived functor of the global section functor, regarded as a left-exact functor from abelian sheaves on a topological space (more generally, on a site) to the category of abelian groups. In fact, one can regard this functor as $\mathcal{F} \mapsto \hom_{\mathrm{sheaves}}(\ast, \mathcal{F})$ where $\ast$ is the constant sheaf with one element (the terminal object in the category of all -- not necessarily abelian -- sheaves, so sheaf cohomology can be recovered from the full category of sheaves, or the "topos:" it is a fairly natural functor.
de Rham cohomology can be made to work for arbitrary algebraic varieties: there is something called algebraic de Rham cohomology (which is the hyper-sheaf cohomology of the analog of the usual de Rham complex with algebraic coefficients) and it is a theorem of Grothendieck that this gives the usual singular cohomology over the complex numbers. Incidentally, sheaf cohomology provides a very simple proof that de Rham cohomology agrees with ordinary cohomology (at least when you agree that ordinary cohomology is cohomology of the constant sheaf, here $\mathbb{R}$) because the de Rham resolution is a soft resolution of the constant sheaf $\mathbb{R}$, and you can thus use it to compute cohomology.
Sheaf cohomology is quite natural if you want to consider questions like the following: say you have a surjection of vector bundles $M_1 \to M_2$: then when does a global section of $M_2$ lift to one of $M_1$? The obstruction is in $H^1$ of the kernel. So, for instance, this means that on an affine, there is no obstruction. On a projective scheme, there is no obstruction after you make a large Serre twist (because it is a theorem that twisting a lot gets rid of cohomology). Sheaf cohomology arises when you want to show that something that can be done locally (i.e., lifting a section under a surjection of sheaves) can be done globally.
$H^1$ is also particularly useful because it classifies torsors over a group: for instance, $H^1$ of a Lie group on a manifold $G$ classifies principal $G$-bundles, $H^1$ of $GL_n$ classifies principal $GL_n$-bundles (which are the same thing as $n$-dimensional vector bundles), etc.
Also, sheaf cohomology does show up in algebraic topology. In fact, the singular cohomology of a space with coefficients in a fixed group is just sheaf cohomology with coefficients in the appropriate constant sheaf (for nice spaces, anyway, say locally contractible ones; this includes the CW complexes algebraic topologists tend to care about). For instance, Poincare duality in algebraic topology can be phrased in terms of sheaves. Recall that this gives an isomorphism
$H^p(X; k) \simeq H^{n-p}(X; k)$ for a field $k$ and an oriented $n$-dimensional manifold $X$, say compact. This does not look very sheaf-ish, but in fact, since these cohomologies are really $\mathrm{Ext}$ groups of sheaves (sheaf cohomology is a special case of $\mathrm{Ext}$), so we get a perfect pairing
$$ \mathrm{Ext}^p(k, k) \times \mathrm{Ext}^{n-p}(k, k)\to \mathrm{Ext}^n(k,k)$$
where the $\mathrm{Ext}$ groups are in the category of $k$-sheaves. This can be generalized to singular spaces, but to do so requires sheaf cohomology (and derived categories): the reason, I think, that for manifolds those ideas don't enter is that the "dualizing complex" that arises in this theory is very simple for a manifold. You might find useful these notes on Verdier duality, which explains the connection (and which mostly follow the book by Iversen).
