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].