SATA drive not spinning, but powers on with USB adapter

I verified that a 128 GB SSD as well as a 2 TB HDD (SATA 3Gb/s, 512e) are well recognized with the same power and data cables.

Was the sata cable also always connected to the same place? Because your motherboard is probably very similar to mine and when a nvme is plugged into the motherboard it disables a sata port Example: with my nvme on slot 1 of the motherboard (which has 3 Z370 asrock taïchi) the port sata 0_3 is disabled.

This issue is caused by older power supply units providing 3.3 V power on SATA pin 3 which collides with the Power Disable feature of drives implementing SATA revision 3.3.

I had the same issue with a Seasonic PSU "Prime Modular 80+ Platinum, 750 Watt" and a modern Western Digital DC HC510 (10 TB "data center drive") that refused to spin up when connected to the PC-internal power supply, but ran without problems when connecting it to an external USB-to-SATA adaptor with an external power supply.

Installing a Molex to SATA power adaptor and by that eliminating voltage on pin 3 solved the issue and made the HDD spin up again.

You can find details on this at en.wikipedia.org: Serial ATA: SATA revision 3.3 and on hgst.com: Power Disable Feature Tech Brief (archived at web.archive.org).