I visited Mada'in Saleh in 2011 whilst working in KSA. I made phone contact with a guide I'd found online (trip advisor??), flew from RUH to MED, and we drove up from there. I believe you can now fly to much closer airports (Al 'Ula ??).

The problem will be getting a visa, as you seem to be aware. If you do manage to get a transit visa, there shouldn't be any restrictions on domestic travel once you're in the country. I doubt you'll get a tourist visa unless you're Muslim and visiting for Umrah. If you know someone working in the country they may be able to sponsor a business visa, but it will be v expensive now.

It is definitely worth a visit and is almost deserted compared with Petra, which is overrun by tourists. The Nabbateans are indeed an interesting lot.