This is false in general. In fact, it is true if and only if $X=aY+b$ (almost surely) for some fixed constants $a \geq 0$ and $b \in \mathbb{R}$. That is $X$ and $Y$ must be positively linearly related for this to hold. Wikipedia also has a decent page on this.
For a counterexample to your statement, consider any two independent random variables $X$ and $Y$ each with strictly positive variance. Then, $ \mathrm{Cov}(X,Y) = 0 \>, $ but, $ \sqrt{\mathrm{Var}(X)} \sqrt{\mathrm{Var}(Y)} > 0 \>. $
A quick proof (and a slick one, I think; it's not my own) of the if and only if assertion uses the Cauchy–Schwarz inequality and goes as follows. Let $U$ and $V$ be random variables such that $\newcommand{\e}{\mathbb{E}}\e U^2 < \infty$ and $\e V^2 < \infty$. Then, $|\e UV|^2 < \e U^2 \e V^2$ if and only if $\e (t U - V)^2 > 0$ for all $t$. But, if $\e(t_0 U - V)^2 = 0$ for some $t_0$, then $t_0 U = V$ almost surely. Now, set $U = X - \e X$ and $V = Y - \e Y$ to get the desired conclusion.