Fans with lights are a bit confusing, both because there are at least three versions, and because they use some of the same names as plain fans, but use those same names to mean different things!
The first lighted fans were the type you have had already called LED Fans. They have only one cable from them, and the LED's mounted in the frame are simply connected in parallel with the fan motor. Usually they are of one colour only, and they always are on when the fan is running. On many, if the fan is fed a low-speed voltage signal the LED's also will be dimmer. Now, fan motors come in two dominant versions. The older type uses three wires and pins, and its speed is controlled only by sending it supply voltages from 12 VDC (full speed) to 5 VDC (minimum speed - less voltage may stall the fan). The newer design provides full 12 VDC supply to the fan at all times, plus the new PWM signal on Pin #4. Inside the fan there is a small chip that uses the PWM signal to alter the flow of current from the fixed supply through the windings to achieve speed control. LED fans have been sold with both 3-pin and 4-pin fan motor types.
The first version of RGB fans have two devices in one unit - a fan motor, and some lights in the frame. The lights are three colours of LED - Red, Green, and Blue. Along a light strip (or around a fan frame), all the Red LED's are on one circuit, all the Greens on another, etc. The Controller uses 4 pins to send out a constant 12 VDC power supply and then manipulates each of three separate Ground lines, one for each colour, to vary the intensity (brightness) of each colour and produce a huge range of colours and brightness. However, at any one moment the entire light system can be only one colour until they all change. This system usually is called plain RGB or 12 V 4-pin RGB. Its female connector on the end of a light cable has 4 holes in a straight line, with the +12 VDC line marked so you MUST align those at each connector junction.
The more complex system is Addressable RGB (or ADDR RGB or ARGB), and it uses the same basic three LED colours along the strip, but organized differently into NODES. Each Node contains one LED of each colour plus its own controller chip for that group only. The master controller provides common +5 VDC and Ground lines, and a Digital Control Line. Thus it is often called the 5V 3-pin system. Its common female connector looks very like the 4-pin one with one hole blocked off, so you can only plug it in one way, and you can NOT plug it into a 4-pin male header. The master controller sends along the digital Control Line a series of instruction packets each with its own address. Along the strip, all the Node control chips listen to that line and do only what their addressed packet says. Thus at any one moment all of the Nodes along the strip can be different colours and brightnesses. This allows more complex displays like rainbows, static or chasing each other along the strip. An ADDR RGB Fan simply has the lights mounted in its frame, rather than on a strip.
Both the voltage supplied and the methods of light display control are very different between these two, so they can NOT be mixed on one circuit.
Plain RGB or ADDR RGB can be built into frames of either 3-pin or 4-pin fan motors. And THAT is where the confusion really hits us, because there are 3- and 4-pin fans AND 3- and 4-pin RGB light systems, and the two types of devices really are separate but stuck together in one unit. Any of the possible four pairing are possible, but many are done solely with 4-pin PWM fan motors since they are the newer design and slightly better from a technical standpoint. The key thing to note is that the new RGB fans all have TWO separate cables on them - one ending in a standard fan connector for the motor to plug into a mobo fan header, and a second ending in a wider connector to plug into a plain RGB (4-pin) or ADDR RGB (3-pin) controller port or mobo header.
As it happens, OP, the fan you got has a 3-pin (Voltage Controlled) motor and a 3-pin (ADDR RGB) light system in its frame. You have connected the FAN cable correctly to a mobo 3-pin header, but your mobo has no plain RGB or ADDR RGB header to plug in the other cable. Since it has NEITHER type, you cannot get ANY fan to plug into a mobo lighting header. Your only option would be to buy and install a separate third-party ARGB or RGB controller. IF you plan to do that, since you already have a light system of the ADDR RGB (or ARGB) type (3-pin, 5 VDC), just get that. Changing to the other (plain RGB) lighting system would not remove your need for a lighting controller.
By the way, I think you bought the right fan. Your mobo's CHA_FAN headers all are of the 3-pin type that use ONLY Voltage Control Mode (even the 4-pin CHA_FAN header does this despite the fact is has 4 pins). This Mode is required for 3-pin fans; although a 4-pin fan CAN be controlled this way also, technically it is not ideal. Do not attempt to use the PWR_FAN header for any fan. The CPU_FAN header should be used only for the CPU cooler.
Let me undo your confusion about use of a USB2 header on the mobo. Such a header can NOT be used to power and control any RGB lights directly. When you install a third-party separate RGB or ARGB light controller it needs two inputs besides its outputs to light strips (fan frames). It requires a power supply from the PSU directly, either 4-pin Molex or SATA. Then most also have no real display control logic in them, and depend on a software utility you get free with the controller. That must be loaded and running, and it communicates its instructions to the Controller box using a connection by cable to a USB2 mobo header. The connection between Controller box and USB2 header is for communication only, and the header iself has no ability to power or to control RGB lights.