Exactness of the Tensor Functor
Ok so we have an exact sequence
$$ M''' \to M' \to M \to M'' \to 0 $$
Let's break it up a bit. I'm going to denote by $\alpha$ the map $M' \to M$ and by $\beta$ the map $M \to M''$. Then we can break up the exact sequence above as two exact sequences
$$ M''' \to M' \to \operatorname{im} \alpha \to 0 $$
and
$$ 0 \to \ker \beta \to M \to M'' \to 0. $$
Tensoring, we see that the first becomes the exact sequence
$$ M''' \otimes N \to M'\otimes N \to \operatorname{im} \alpha \otimes N \to 0 \enspace \enspace \enspace (*) $$
and the second becomes the exact sequence
$$ \ker \beta \otimes N \to M \otimes N \to M'' \otimes N \to 0 \enspace \enspace \enspace (**). $$
From exactness of the original sequence we know that $\operatorname{im} \alpha = \ker \beta$ so that $\operatorname{im} \alpha \otimes N = \ker \beta \otimes N$. However, it is no longer necessarily true that this is a submodule of $M\otimes N$ as the tensor product with $N$ is not necessarily left exact.
By exactness of $(*)$, $\operatorname{im}(M''' \otimes N \to M' \otimes N) = \ker(M'\otimes N \to \operatorname{im}\alpha \otimes N = \ker \beta \otimes N)$. However, now the map $\ker \beta \otimes N \to M \otimes N$ might not be an inclusion, there might be a nontrivial kernel. Thus, $\ker(M'\otimes N \to \operatorname{im}\alpha \otimes N = \ker \beta \otimes N)$ might not necessarily be equal to the kernel of the composition to $M \otimes N$, $\ker(M' \otimes N \to M \otimes N)$. Therefore, the longer sequence might not be exact even though the two short exact sequences are.
This is the general idea even if we try to extend it. A long exact sequence is really just a bunch of short exact sequences at each step and tensoring won't always preserve the inclusion of that kernel and so it won't always preserve the long exact sequences.
A more concise way to say this is that tensoring does not preserve kernels, $\ker \beta \otimes N \neq \ker (\beta \otimes \operatorname{id}_N)$ in general so in general it can't preserve longer exact sequences where we need something mapping to a kernel to still stay exact.
Some Counterexamples
There are many counterexamples to this statement - clearly, there has to be, for otherwise flat modules would be everywhere. And perhaps the best way to see why it fails is to examine some examples. Here is a class of examples that have two advantages: they are easy to compute with, and show something a little different than working over principal ideal domains (it's safe to ignore the parenthetical remarks):
Consider $\mathbb{Z}[x]$-the integer polynomial ring. Here is an example of an exact sequence of $\mathbb{Z}[x]$-modules:
$$ 0\to \mathbb{Z}[x]\xrightarrow{\alpha} \mathbb{Z}[x]^2\xrightarrow{\beta}\mathbb{Z}[x]\xrightarrow{\gamma} \mathbb{Z}/2\to 0 $$
The maps are defined by
- $\alpha(f) = (2f,xf)$
- $\beta(f,g) = 2g - xf$
- $\gamma$ the quotient map via the ideal $(2,x)$.
(This is just a special example of something called the Koszul complex given by the regular sequence $(2,x)$ - any two relatively prime elements generating a proper ideal will work, since $\mathbb{Z}[x]$ is a GCD domain.) Here are two examples to show why tensoring does not keep this exact:
Example 1
One can apply the functor $\mathbb{Z}/2\otimes_{\mathbb{Z}[x]}-$ to get the complex $$ 0\to \mathbb{Z}/2\xrightarrow{\alpha} \mathbb{Z}/2\oplus \mathbb{Z}/2\xrightarrow{\beta}\mathbb{Z}/2\to\mathbb{Z}/2\to 0 $$ Here, $\mathbb{Z}/2$ is a $\mathbb{Z}[x]$ module via the quotient map $\mathbb{Z}[x]\to \mathbb{Z}/2$, so $2$ and $x$ act trivially, and hence $\ker\beta = \mathbb{Z}/2\oplus\mathbb{Z}/2$ whereas $\operatorname{Im}\alpha = 0$. In other words, $\operatorname{Tor}_1^{\mathbb{Z}[x]}(\mathbb{Z}/2,\mathbb{Z}/2) = \mathbb{Z}/2\oplus\mathbb{Z}/2$. Note, to compute derived functors we "should have" chopped off the $\mathbb{Z}/2$ but that doens't matter since we are not computing the zeroth homology.
Example 2
Here is a more interesting example, still working with $\mathbb{Z}[x]$-modules: Consider the following $\mathbb{Z}[x]$-module: the underyling abelian group structure is given by $V = \mathbb{Z}/4\times\mathbb{Z}/4$. There is an endomorphism of $V$ as abelian groups given by the matrix
$$T= \begin{pmatrix} 2 & 2\\ 0 & 1 \end{pmatrix} $$
Letting $x$ act via $T$ makes $V$ into a $\mathbb{Z}[x]$-module. Applying $V\otimes_{\mathbb{Z}[x]}-$ to the exact sequence gives a chain complex $$ V\xrightarrow{\alpha} V^2\xrightarrow{\beta} V\to V\otimes_{\mathbb{Z}[x]}\mathbb{Z}/2\to 0 $$
Now $\beta([(1,0),(1,0)]) = (2,0) - (2,0) = 0$. However, $\alpha(a,b) = [ (2a,2b), (2a + 2b,b)]$. So the complex is not exact at $V^2$.
To Produce Your Own...
...find rings that have non-flat modules and modules with free resolutions of length at least two. Most of the time you should get a winner.
If I understand the question correctly, you already knew that, when $$ M'''\to M'\to M\to M''\to 0 $$ is exact, $$ M'''\otimes_AN\to M'\otimes_AN\to M\otimes_AN\to M''\otimes_AN\to 0 $$ could fail to be exact when $M'''=0$, and you tried to avoid that difficulty by assuming that $M'''\neq0$. Unfortunately, that doesn't really avoid the problem. If you have a counterexample with $M'''=0$, then you can manufacture a counterexample another with any non-zero $M'''$ that you want. Just put your desired $M'''$ in place of the $0$ and define the map $M'''\to M'$ to be the zero map.
OK, so probably the next thing to try is to assume that not only the module $M'''$ but also the map $M'''\to M$ has to be non-zero. That avoids the construction I just described, but it still doesn't avoid the problem. Take your counterexample for the case where $M'''=0$, and modify it as follows. Put any desired non-zero $M'''$ in place of the $0$, and also replace $M'$ with $M'\oplus M'''$. Then define the map from $M'''$ to (the new) $M'$ to be the inclusion of the second summand. EDIT: I should have also said that the map from the new $M'$ to $M$ is the composition of the projection from the new $M'$ to the old with the original map from the old $M'$ to $M$.
In effect, what I've done here is to splice together your original short exact sequence with the (even shorter) exact sequence $M'''\to M'''\to 0$. That's the simplest case of (the reverse of) what Dori Bejleri explained about breaking longer exact sequences into several short exact sequences.