Well, the answer is well known of course. For a finitely generated module over a commutative normal Noetherian domain TFAE
- M is reflexive
- M is torsion-free and equals the intersection of its localizations at the codimension 1 primes
- M satisfies Serre's condition S2 and its support is Spec R.
- M is the dual of a f.g. module N
As you say, a finite projective module is the same as a locally free sheaf on Spec R. Similarly, a finite reflexive module is the same as the push forward of a locally free sheaf from an open subset U of Spec R whose complement has codimension $\ge2$.
So for an easy example take a Weil divisor D which is not Cartier, the associated divisorial sheaf (corresponding to an R-module of rank 1) is reflexive, not projective. Your example with a line on a quadratic cone is of this form.
This stuff is standard and used all the time in higher-dimensional algebraic geometry around the Minimal Model Program. For an old reference covering some of this, see e.g. Bourbaki, Chap.7 Algebre commutative, VII.