It turns out that Glen Bredon's book does answer this question, but to me it seems very difficult to understand the map just from what's in the book. Theorem V.9.2. states a version of Poincaré duality for any sheaf. Further, Theorem V.12.21 states that there is an isomorphism between "sheaf homology" as defined in Chapter V and singular homology defined using chains. However, understanding this isomorphism seems to require understanding sheaf homology and right now I'm not up for the task.
However, Bredon sketches another answer in IV.2.9. (together with exercises I-12 and II-32), which I'm going to write in more detail here, as I have two issues with it. First, it only proves the duality for constant coefficients, because the general version with coefficients in a local system is a consequence of V.9.2 and V.12.21, which as I said above I don't really understand. Secondly, the sentence in IV.2.9. at the top of page 207 ("If X is a manifold, it is clear that...") is not clear to me.$\newcommand{\cL}{\mathcal L}\newcommand{\qiso}{\overset{\text{qiso}}\cong}\newcommand{\Z}{\mathbb Z}$
Let $M$ be an $n$-dimensional orientable (non-compact) manifold, let $\cL$ be a local system on $M$ and let $p:M\to \text{pt}$ be the map to a point. Our goal is to construct a soft resolution $S^0\to \cdots \to S^n$ of $\cL$ with the property that $p_!(S^\bullet) \cong C_{n-\bullet}(M;\cL)$ is the complex of $\cL$-valued singular chains.
Let us first see why this is what we need. By the softness if $S^\bullet$, this would imply that
$$
Rp_!\cL \overset{\text{qiso}}\cong Rp_!(S^\bullet) \qiso p_!(S^\bullet) \cong C_{n-\bullet}(M;\cL).
$$
So $H^i_c(M;\cL)\cong H_{n-i}(M;\cL)$. On the other hand, $\cL$ is resolved by the de Rham complex $\Omega^\bullet(\cL)$, which is also soft. Therefore,
$$
Rp_!\cL\overset{\text{qiso}}\cong p_!\Omega^\bullet(\cL).
$$
So $H^i_c(M;\cL)\cong H^i_{c,\text{dR}}(M;\cL)$. These two isomorphisms together provide the answer.
Here is how we construct the resolution. $S^{n-i}$ will turn out to be the sheafification of the presheaf that assigns to an open set $U\subset M$ the space $C_i(M,M\setminus U;\cL)\cong \frac{C_i(M;\cL)}{C_i(M\setminus U;\cL)}$. Note that if $V\subseteq U$, there is a map $C_i(M,M\setminus U;\cL)\to C_i(M,M\setminus V;\cL)$.
$S^{n-i}$ is given as follows. We denote
$$
C_i^{\Pi}(X;\cL)=\prod_\phi\Gamma(\Delta_i;\phi^*\cL)$$
Where $\phi:\Delta_i\to X$ ranges over all the singular simplices (so we are using infinite chains as opposed to usual finite chains). For an open set $U\subset M$, we define
$
C_i^U(X;\cL) \subseteq C_i^{\Pi}(X;\cL)
$ as the subgroup of chains $(s_\phi)_{\phi:\Delta_i\to X}$ with the property that every $p\in U$ has a neighborhood intersecting only finitely many chains $\phi(\Delta_i)$ for which $s_\phi\neq 0$ (so if $X=U$ this corresponds to the Borel-Moore chains). We define
$$
S^{n-i}(U) = C_i^U(M|U;\cL):=\frac{C_i^U(M;\cL)}{C_i^U(M\setminus U;\cL)} .
$$
Throughout the coefficients will always be $\cL$, so we will ommit it from the notation.
The first claim is that $S^{n-i}$ is a sheaf. Suppose we are given a covering $U = \bigcup V_j$ and compatible sections $(s^j_\phi)_{\phi}\in S_i(V_j)$. The compatibility means that if $\phi\cap V_j \cap V_{j'}\neq \emptyset$, then $s^j_\phi = s^{j'}_\phi$. Therefore, there is a unique chain $(s_\phi)_{\phi\cap U\neq \emptyset}$, given by $s_\phi = s_\phi^j$ for any $j$ for which $\phi\cap V_j\neq \emptyset$ (it is unique modulo chains contained in $M\setminus U$, as desired). It remains to check that this chain belongs to $C_i^U(M)$: for any $p\in U$, $p$ must be contained in some $V_j$, and the finiteness property follows from $(s_\phi)|_{V_j} = (s_\phi^j)$.
Note that this is not exactly the group of Borel-Moore chains, as the chains in $S_i(U)$ are allowed to accumulate on the boundary of $U$, unlike the case of the Borel-Moore group.
Next, we verify that $S^{n-i}$ is a (c-)soft sheaf: it is easy to see that sections on a compact set $K$ are simply given by $S^{n-i}(K) = C_i(M,M\setminus K)$, which has a surjective map from $S_i(M) = C_i^{BM}(M)$. From the definition, we can check directly that the compactly supported global sections of $S_i$ are isomorphic to $C_i(M)$.
Finally, we want to show that the complex $S^0\to S^1\to\cdots \to S^n$ resolves $\cL$ (here if $M$ was non-orientable we would have to twist by the orientation sheaf). Let $U$ be a ball around a point in $M$. First of all, we claim that
$$
C_i^U(M|U) \cong \lim_{\substack{\gets \\K \text{ compact}\\ K \subset U}} C_i^U(M|K).
$$
There is a map going from left to right. Suppose a chain $(s_\phi)$ maps to $0$. Then its support does not intersect any compact $K\subset U$, i.e. it's supported on $M\setminus U$. This shows the map is injective. To see it is surjective, an element on the right hand side is a compatible sequence of chains $(s_\phi^K)_\phi$, which glue to a chain $(s_\phi)\in C_i^U(M|U)$ by the same argument that we used to show that $S_i$ is a sheaf.
Next, we check that excision works in this situation. Give two sets $A,B$ with $\overline A\cap \overline B = \emptyset$, we have that the inclusions induce a homotopy equivalence $C_\bullet^U((X\setminus A)|B) \cong C_\bullet^U(X|B)$. We can verify this by looking at the proof in Hatcher [2]: it shows that barycentric subdivision provides a homotopy inverse for the chain map. Barycentric subdivision is defined at the level of chains, and can be extended to infinite chains with the same formulas.
Now, applying excision to the sets $M\setminus U$ and $K$ tells us that there is a homotopy equivalence $ C_\bullet^U(M|K) \cong C_\bullet^U(U|K)$, and further shrinking $U$ to a compact neighborhood $K_2$ of $K$ in $U$ we have that we can work with usual chains:
$$
C_\bullet^U(U|K) \qiso C_\bullet^U(K_2|K) = C_\bullet(K_2|K) \qiso C_\bullet(U|K).
$$
As far as I know, we should be careful that the inverse limit of the homotopy equivalences we have constructed stays a quasiisomorphism after taking the limit. Consider the cone of the map $C_\bullet(U|K)\to C_\bullet^U(U|K)$, let's call it $N_\bullet^K$. For every $K$, its homology is trivial, as it is the cone of a quasiisomorphism. Since all the maps involved in the limit are surjective, Stacks Project 0918 suffices to show that $\lim_{\gets,K} N_\bullet^K$ has vanishing homology, and therefore the limit map $\lim_\gets C_\bullet(U|K)\to \lim_\gets C_\bullet^U(U|K)$ is a quasiisomorphism, as its cone's homology vanishes. We have shown that the inclusion maps induce a quasiisomorphism:
$$
\lim_{\substack{\gets \\K \text{ compact}\\ K \subset U}} C_\bullet(U|K) \overset{\sim}{\to} S^{n-\bullet}(U) = C_\bullet^U(M|U).
$$
Now, if $U$ is a ball and $K\subseteq K'$ are closed balls (possibly of radius $0$), the map $C_i(U|K')\to C_i(U|K)$ is a surjective quasiisomorphism, since homology is homotopy invariant. Any compact set is contained in a closed ball, so the limit can be computed using just closed balls. Another application of Stacks 0918 shows that for any point $p\in U$, there is a quasiisomorphism
$$
\lim_{\substack{\gets \\K \text{ compact}\\ K \subset U}} C_\bullet(U|K) \overset{\sim}\to C_\bullet(U|p).
$$
Now, $U$ is a ball, so we can canonically identify $\cL|_U$ with the constant sheaf $\underline{\cL_p}$. Finally, Hatcher 3.3 tells us that $H_i(U|p,\underline{\cL_p})\cong \widetilde H_{i-1}(S^{n-1},\cL_p)$. Since $\cL_p$ is torsion-free (it is a vector space over $\mathbb R$ or $\mathbb C$), the universal coefficient theorem says that $H_{i-1}(S^{n-1},\cL_p)\cong H_{i-1}(S^{n-1},\Z)\otimes_\Z \cL_p$. The term $H_{i-1}(S^{n-1},\Z)$ is the orientation sheaf, as a section corresponds to an orientation of $S^{n-1}$ at every point. Further, if we go around a loop the homology class will change according to the monodromy of $\cL_p$, and possibly by a sign if $M$ is not orientable and the loop reverses the orientation. This shows that $S_i$ is a resolution of $\cL$ (in the orientable case), and we are done. Tracing the above proof, the map $\cL\to S^0$ is given by mapping a section $s$ to a chain $\sum \phi_i^*(s)$, where $\phi_i:\Delta_n\to M$ are simplices such that $\sum \phi_i = [M]$, the (Borel-Moore) fundamental class of $M$.