Ok, I have a new important step. I had to use MySQL 8.0.28, newer versions generate the database with a different charset (https://community.powerbi.com/t5/Desktop/Refresh-data-error-Character-set-utf8mb3-is-not-supported-by-Net/td-p/2298674) and metadata server silently gives exception and give 404 when opening api/latest. I had to run metadata server from VS and enable exceptions to catch all the database related issues (Debug ASP.NET Exceptions - Visual Studio (Windows) | Microsoft Learn). It was difficult to identify the issue as ugs_db and all the tables were actually created successfully just that API was giving 404.
Additionaly you always must do the “Disabling ONLY_FULL_GROUP_BY” step from UGS Reference | Unreal Engine 4.27 Documentation
and write the change to the MySQL config file.
Also, these IIS App Pool permission users are not listed in any user lists, not even if you use advanced user search. You really need to type it manually using the whole path, including the slash.
After these steps, I was finally able to start the metadata server successfully. Best way to track you progress is to first don’t do anything with IIS. Start the Metadata Server directly from visual studio and test & troubleshoot it there until you can get the /api/latest working. The correct format is just domain:port/api/latest. Just then move everything to IIS and continue there.
I had also various other issues but these were resolved by installing missing visual studio ASP web tools, reparing visual studio (!) as apparently it did not install all of them for whatever reason, install IIS from windows get features (but just clicking the upper level checkbox is not enough, you need also a bit more stuff from deeper levels which are unchecked by default).
I used the default metadata server from Epic’s engine source.