Connection between Hamiltonian version of the least action principle and probability amplitude in the Schrödinger equation
What I don't get is how he ended up with an equation that is a probability distribution.
He ended up with Schroedinger's equation, but he did not think it describes probability. He thought more along the lines of de Broglie idea, that the electron is some kind of wave, and $\psi$ - solution to the equation - expresses mathematically shape and other properties of this wave.
Probabilistic interpretation of $\psi$ was introduced by Max Born based on his theoretical study of electron scattering experiments. He found that the electrons always appear as dots on the picture, but when many dots are recorded, their density is close to magnitude of the $\psi$ function.
Intro:
Ján Lalinsky's answer outlines the standard story, which basically follows Max Born's recount in his 1954 Nobel lecture, but the details are a bit more complicated. For instance, to say that Schroedinger simply followed de Broglie's wave idea in deriving his equation is to strip the basis of his original approach to a cryptic phrase that removes its entire logic. And then we are left with the mystery of the Schroedinger equation that, unintuitively, "pops out of nowhere" .
In a way, Schroedinger himself is partly responsible for this view, due to the way he structured and submitted for publication his four seminal papers of 1926, "Quantisation as a Problem of Proper Values", parts I-IV (for English reprints see for instance Stephen Hawking's collection "The Dreams That Stuff Is Made Of"). His celebrated equation first appears, as a time-independent version for an electron in a central electric field, in Eq.(5) of Part I. Contrary to common standards, the justification given there is indeed stripped down to bare essentials: a variational formulation, eq.(2), based on a Hamilton-Jacobi equation, eq.(1). This is probably why the "derivation" is commonly equated to more or less glorified handwaving. But the detailed basis for Schroedinger's approach - both formal and intuitive - is in fact the subject of Part II, where the stated purpose is
"to throw more light on the general correspondence which exists between the Hamilton-Jacobi differential equation of a mechanical problem and the "allied" wave equation, i.e. equation (5) of Part I […]"
As Schroedinger points out himself,
"So far we have only briefly described this correspondence on its external analytical side by the transformation (2), which is in itself unintelligible, and by the equally incomprehensible transition from the equating to zero of a certain expression to the postulation that the space integral of the said expression shall be _stationary_$^1$." ( Footnote $^1$ proceeds to explain that "The procedure […] was only intended to give a provisional, quick survey of the external connection between the wave equation and the Hamilton-Jacobi equation," where "$\psi$ is not actually the action function of a definite motion in the relation stated in (2) of Part I," but "On the other hand the connection between the wave function and the variation problem is of course very real: the integrand of the stationary integral is the Lagrange function for the wave process.")
To answer the question:
The 2nd paragraph of Part II then proceeds to outline Schroedinger's starting point, which he says was in fact Hamilton's own "starting-point for his theory of mechanics, which grew out of his [Hamilton's] Optics of Non-homogeneous Media":
"Hamilton's variational principle can be shown to correspond to Fermat's Principle for a wave propagation in configuration space (q-space), and the Hamilton-Jacobi equation expresses Huygens' Principle for this wave propagation. Unfortunately this powerful and momentous conception of Hamilton is deprived, in most modern reproductions, of its beautiful raiment as a superfluous accessory, in favour of a more colorless representation of the analytical correspondence."
This analogy between dynamics in configuration space and wave optics in inhomogeneous media is, by all means, the guiding intuition behind Schroedinger's approach.
What he does technically is to reformulate Hamilton's approach with help from a method developed by Hertz. The latter basically equips the configuration space with a non-Euclidian metric defined by the kinetic energy, $ds^2 = 2 {\bar T}(q_k, {\dot q}_k) dt^2$ (another footnote reveals that the problem was thoroughly studied by Felix Klein as early as 1891, and was well-known to Sommerfeld). The dynamics is now described not by paths in the (multidimensional) configuration space, but by the "wave-like propagation" of equi-action surfaces normal to these paths. It is this "propagation" that is analogous to that of optical wavefronts in an inhomogeneous medium, where "paths" correspond to ray optics, while wavefronts obviously correspond to wave ("undulatory") optics. Schroedinger then pushes the conjecture that this analogy must be complete. That is, he assumes that just as the necessity of wave optics follows from the breakdown of ray optics for short paths of large curvature (interference and diffraction around obstacles smaller than or comparable to the wavelength), in exactly the same way the breakdown of classical mechanics, also for short paths of large curvature, calls for a wave mechanics based on equi-action wavefronts. He then infers the simplest "wave mechanics" on configuration space that has as "ray limit" the usual classical mechanics.
But, he does not draw on de Broglie to do so. In fact, he doesn't have to. He first derives de Broglie's relation between particle velocity and (configuration space) wave group by using Planck's relation $E = h\nu$ in the equation for the action wavefronts. Nevertheless, he does emphasize the consistency of his approach with de Broglie's hypothesis of "phase waves".
As for the statistical interpretation, it is noteworthy that Schroedinger concluded Part IV with Section §7, On the Physical Significance of the Field Scalar (wavefunction). He observed that $\psi\psi^*$ must be "a kind of weight-function in the system's configuration space", which indicates how likely the system is to be found in that particular configuration. He actually comes very close to stating the statistical interpretation himself:
"[…] we may say that the system exists, as it were, simultaneously in all positions kinematically imaginable, but not "equally strongly" in all."
Furthermore, Schroedinger notes that, consistent with the weight interpretation, the configuration space integral of the weight function is conserved, and the weight function itself satisfies a continuity equation with the current density we all know. From this he concludes that charge density must satisfy a continuity equation as well, and electric charge is therefore conserved.
So all that was eventually left for Max Born, was to draw the well-known conclusion.
''why exactly reality is organized in such a way that the solution to the schrodinger equation works only when viewed as a probability amplitude.''
That it cannot be an ordinary wave can be seen when considering N electrons. Then the wave function is an equation in $3N$-dimensional configuration space rather than in physical 3-space. Thus the interpretation as a wave in 3-space only works in the 1-particle sector. Schroedinger accepted that but (like Einstein) was never fully satisfied with the probabilistic interpretation.
However, that the probability interpretation is sound can be seen from the well-known fact that statistical mechanics based upon it takes almost the same form as in classical statistical mechanics. A derivation of the Born rule from the statistical mechanics setting can be found in Chapter 10.5 of my online book.