The relationship between the two representations is discussed in a number of textbooks, such as Weinberg's Quantum Theory of Fields, Ch. I.5 or Duncan's The Conceptual Framework of Quantum Field Theory, Ch. 7. The relation between the two representations comes from assuming that the fields are built out of creation and annihilation operators in the appropriate way, which connects equations (1) and (2). Once we make this assumption, equations (1) and (2) do constrain the possible choices of $S_{ij}$ and $D_{\sigma\sigma'}$. Here's a sketch of how it works.
Suppose that we have a family of fields $\phi_{n}$ that can be expressed as linear combinations
$$
\phi_{n} = A_{nm}\phi^{+}_{m} + B_{nm}\phi^{-}_{m}
$$
where
$$
\phi^{+}_{n}(x)
=
\sum_{s, \sigma} \int d^{3}q\, u_{n}(x; q, s, \sigma)\, a(q, s, \sigma)
\quad
\phi^{-}_{n}(x)
=
\sum_{s, \sigma} \int d^{3}q\, v_{n}(x; q, s, \sigma)\, a^{\dagger}(q, s, \sigma)
$$
where the label $s$ runs over some particle species, the label $\sigma$ runs over the dimensions of the spin representation, and $u_{n}$ and $v_{n}$ are some families of functions of position, three-momentum $q$, and the $s$ and $\sigma$ indices. If we suppose that the ladder operators and fields transform under translations as
$$
[P^{\mu}, a(p)] = -p^{\mu}\, a(p)
\qquad\qquad
e^{-i\epsilon_{\mu}P^{\mu}}
\phi_{n}(x)
e^{i\epsilon_{\nu}P^{\nu}}
=
\phi_{n}(x + \epsilon)
$$
then we can conclude that $u_{n}$ and $v_{n}$ are Fourier transforms
$$
u_{n}(x; q, s, \sigma)
\propto
e^{iq_{\mu}x^{\mu}}u_{n}(q, s, \sigma)
$$
Suppose that proper orthochronus Lorentz transformations act as
$$
U(\Lambda)\, a(q, \sigma)\, U^{-1}(\Lambda)
=
\sqrt{\frac{(\Lambda q)^{0}}{q^{0}}}\sum_{\sigma'}
D^{-1}_{\sigma\sigma'}(W(\Lambda, q))\, a(\Lambda q, \sigma')
$$
on ladder operators and
$$
U(\Lambda)\, \phi_{n}(x)\, U^{-1}(\Lambda)
=
\sum_{m} M_{nm}(\Lambda^{-1})\, \phi_{m}(\Lambda x)
$$
on fields, where $W(\Lambda, q)$ is the little group element from your question, with reference momentum $k$. I've also used $M_{nm}$ instead of your $S_{ij}$, because I will probably make a typo and forget to capitalize $S$ and confuse things, sorry. Then we can conclude that
$$
\sum_{m} M_{nm}(L(p))\, u_{m}(k, s, \sigma)
=
\sqrt{\frac{p^{0}}{k^{0}}}
u_{n}(p, s, \sigma)
$$
with $L(p)$ as in your question, so the compatibility of (1) and (2) reduces to a condition on how the coefficients at the reference momentum transform.
So let's suppose we're interested in a particle of mass $m$, and take the reference momentum $k$ to be its momentum in its rest frame. Then the little group is $SO(3)$, and we know that the finite-dimensional representations of interest are the spin representations. For any rotation $R$ the Wigner rotation $W(R, p)$ is just $R$, so the compatibility of (1) and (2) reduces to
$$
\sum_{m} (J_{i}^{(j_{M})})_{nm}u_{m}(0, s, \sigma)
=
\sum_{\sigma'} u_{n}(0, s, \sigma')\, (J_{i}^{(j_{s})})_{\sigma'\sigma}
$$
where $J^{(j_{s})}$ are the rotation generators in the spin-$j_{s}$ representation and $J^{(M)}$ are the generators of the spin-$j_{M}$ representation that's part of $M_{nm}$. So the question becomes: what choices of $j_{M}$ and $j_{s}$ are compatible with one another? This is where the rubber really hits the road, so to say, because this equation tells us how the two different representations act on the same object $u_{n}(0, s, \sigma')$. This is possible because this object has two different kinds of indices for the two different representations to act on. And so this will constrain how the two representations must be related. This is also why your approach fails: you have $D_{\sigma\sigma'}$ and $M_{nm}$ acting on the same indices, but they should be different: while $D_{\sigma\sigma'}$ should indeed act on the spin index, the indices on $M_{nm}$ should match the indices on the field $\phi_{n}$, and the coefficient $u_{n}(0, s, \sigma)$ on the creation operator should have both kinds of distinct indices.
We can list them by going through all possible $j_{M}$ and asking which $j_{s}$ are compatible with that. If $M_{nm}$ is the trivial representation and we have a single particle species then the rotation condition is
$$
0 = \sum_{\sigma'} u(0, \sigma')\, (J^{(j_{s})}_{i})_{\sigma'\sigma}
$$
which implies that $u(0, \sigma) = 0$ when $\sigma \not= 0$. This implies that $j_{s} = 0$.
If $M_{nm}$ is the bispinor representation then the rotation condition has the form
$$
\sum_{m} (S^{ik})_{nm} u_{m}(0, \sigma)
=
\sum_{\sigma'} u_{n}(0, \sigma')\, (J^{(j_{s})}_{ik})_{\sigma'\sigma}
$$
where $S^{ik}$ are the generators of the bispinor representation.
The first two components of $u_{n}$ form a $2 \times (2j_{s} + 1)$ matrix that intertwines the $(\frac{1}{2}, 0)$ block of the bispinor representation and the spin-$j_{s}$ representation furnished by the $J^{(j_{s})}$. Since this block is irreducible, Schur's lemma tells us that $j_{s} = \frac{1}{2}$ and each coefficient has only two nonzero components for each spin.
If $S_{nm}$ is a vector representation then we have a set of four fields $\phi^{\mu}$, and the rotation condition becomes
$$
0 = \sum_{\sigma'} u^{0}(0, \sigma')\, (J^{(j_{s})}_{i})_{\sigma'\sigma}\qquad\qquad
i\epsilon_{ki\ell}u^{\ell}(0, \sigma)
=
\sum_{\sigma'} u^{k}(0, \sigma')\, (J_{i}^{(j_{s})})_{\sigma'\sigma}
$$
Meaning that there are two possibilities: either $j_{s} = 0$ or $j_{s} = 1$. If $j_{s} = 0$ then we can forget about the $\sigma$ label. The rotation condition imposes no constraints on the timelike components of $u^{\mu}$ and $v^{\mu}$, but the spacelike components must all vanish. It follows that $\phi^{\mu} = \partial^{\mu}\phi$ for some scalar field $\phi$. If $j = 1$ then the rotation condition requires the timelike component to vanish and the
other three components to decompose into longitudinal and transverse polarizations.
Things are a bit trickier in the massless case. The little group is a bit more complicated, but in the $j_{M} = 0$ case that doesn't matter, and we find the same result as in the massive case. The bispinor representation also goes through fine. However, it turns out that when $M_{nm}$ is the vector representation, equations (1) and (2) can't be satisfied at zero mass.
