Here is a simple counterexample (simplified and generalised after Mohan's comment): let $R$ be a domain with a non-free finite projective module $P$, and let $Q$ be a finite projective module with $P \oplus Q \cong R^n$. This gives an idempotent matrix $A \in M_n(R)$ corresponding to $(p,q) \mapsto (p,0)$.
For each prime ideal $\mathfrak p \subseteq R$, the modules $P_{\mathfrak p}$ and $Q_{\mathfrak p}$ are free. If $S \colon P_{\mathfrak p} \oplus Q_{\mathfrak p} \stackrel\sim\to R_{\mathfrak p}^n$ is the isomorphism above and $D \colon P_{\mathfrak p} \oplus Q_{\mathfrak p} \to P_{\mathfrak p} \oplus Q_{\mathfrak p}$ the diagonal matrix $(p,q) \mapsto (p,0)$, then $A_{\mathfrak p} = SDS^{-1}$ is diagonalisable. (We can really think of $D$ as a matrix, at least after choosing bases for $P_{\mathfrak p}$ and $Q_{\mathfrak p}$.)
But $A$ is not globally diagonalisable since $P$ is not free: if $A = SDS^{-1}$ for some diagonal idempotent matrix $D$ and some invertible matrix $S$, then $S$ induces an isomorphism $S \colon \operatorname{im}(D) \stackrel\sim\to \operatorname{im}(A)$. But $\operatorname{im}(D)$ is free whereas $\operatorname{im}(A) \cong P$ is not free.
Example. Carrying this out for $R = \mathbf Z[\sqrt{-5}]$ and $P = (2,1+\sqrt{-5}) \subseteq R$ produces the matrix
$$A = \begin{pmatrix} -2 & -1-\sqrt{-5} \\ 1-\sqrt{-5} & 3 \end{pmatrix}.$$
The image contains the vectors $\left(\begin{smallmatrix} -2 \\ 1-\sqrt{-5} \end{smallmatrix}\right)$ and $\left(\begin{smallmatrix} -1-\sqrt{-5} \\ 3\end{smallmatrix}\right)$, which are linearly dependent but not common multiples of some other vector in $R^2$.
Motivation. If $A_{\mathfrak p}$ is diagonalisable for all $\mathfrak p \subseteq R$, then there exists an open cover $U_1 \cup \ldots \cup U_m = \operatorname{Spec} R$ such that $A|_{U_i}$ is diagonalisable for all $i$. This gives matrices $S_i \in \operatorname{GL}_n(U_i)$ such that $D_i := S_i^{-1}A|_{U_i}S_i$ is diagonal.
Assuming for simplicity that $R$ is a domain, we conclude in particular that all eigenvalues $\lambda_1,\ldots,\lambda_r$ of $A$ lie in $R$, since they lie in $R_{\mathfrak p}$ for all prime ideals $\mathfrak p$. Choose some diagonal matrix $D \in M_n(R)$ with the same eigenvalues/multiplicities as $A$, and assume without loss of generality that $D_i = D|_{U_i}$ for all $i$. Then $S_j^{-1}S_i \in \operatorname{GL}_n(U_i \cap U_j)$ is a matrix commuting with $D$, giving a cocycle in $H^1(\operatorname{Spec} R,G)$, where $G = C_{\operatorname{GL}_n}(D)$ is the centraliser of $D$. This cocycle is a coboundary if and only if $A$ and $D$ are globally conjugate matrices.
Under the additional hypothesis that $\lambda_i - \lambda_j \in R^\times$ whenever $i \neq j$, a straightforward computation shows $G = \prod_{i=1}^r \operatorname{GL}_{m_i}$, where $m_i$ is the algebraic multiplicity of the eigenvalue $\lambda_i$. Thus, in this case the only obstruction is in $\prod_{i=1}^r H^1(\operatorname{Spec} R, \operatorname{GL}_n)$, leading to the example above.
But in general, $G$ need not be a flat group scheme over $R$, so the situation is considerably more complicated. For instance, if $D$ is the diagonal matrix $\left(\begin{smallmatrix}2 & 0 \\ 0 & 5\end{smallmatrix}\right)$, then $G \times \operatorname{Spec} \mathbf F_3$ is the full group $\operatorname{GL}_{2,\mathbf F_3}$, so $G$ has a vertical component above $p=3$. Even in the case of a PID, it is not so clear to me what is going on...
Remark. If $R$ is no longer a domain, it is probably more natural to look at the centraliser of $A$ instead of $D$ (which is some sort of 'Zariski inner form' of $C_{\operatorname{GL}_n}(D)$ in the domain case, so it should have the same $H^1$). I expect there might be other global obstructions coming from the non-injectivity of the maps $R \to R_{\mathfrak p}$: perhaps there are too many global choices of $D$ to run the argument above.
You could also imagine an obstruction in $H^1(\operatorname{Spec} R, S_n)$ for globally ordering the eigenvalues in the matrix $D$, although all examples I know where $H^1(\operatorname{Spec} R, S_n) \neq 1$ also have $H^1(\operatorname{Spec} R, \operatorname{GL}_m) \neq 1$ for $m \gg 0$. (An irreducible scheme has no higher cohomology in constant sheaves, even for $H^1$ with non-abelian coefficients, so we didn't see this problem in the case above.)