Here are some things that go wrong. We already mentioned power supply.
When a Drive is failing it can casue another drive on the same cable to fail. Windows may see the wrong drive. The failing drive may be the one the sows in Windows.
When a cable is failing almost anything may happen. One drive works, the other doesn't or both do not work.
If either drive has the cable inverted, you might damage the drive, the cable and the mother board. And of course, it does not work.
Simple rule for those that need it.: "Red goes with red." Meaning the red stripe on the data cable is near the red wire on the power connector.
And never plug / unplug anything with power on. (USB is exception.) Not just to spare the machine, you can hurt yourself very badly with a 700 watt power supply.
Is it all right if I ask why you need to CD-ROM drives?