I've tried many different combinations, and there's no doubt that a dedicated single-focal length macro lens will give you the sharpest results in general, though you can often get quite acceptable results with a conventional lens and tubes or bellows, or a reversed lens (depending on the lens and the magnification ratio). Reversed enlarging lenses are often very good as well, and some enlarging lenses are designed to work well in the normal orientation. You can add tubes or a bellows to a macro lens, too.

If you reverse a lens, be extra careful about shading the exposed lens elements, which will be very prone to flare.

If the magnification ratio is high enough, you can use macro lenses designed for 35mm on medium format and even larger, if you have a way of attaching it to the camera. I have a Canon FD-Bronica S2a adapter that Frank Marshman made me for this purpose, and I've used my Canon FD 35/2.8 Macrophoto even on 4x5". I think it's sharper than my 25mm Luminar, probably because of better coatings. The Tamron SP 90/2.5 Macro is amazingly sharp on 6x6.