To be clear, I don't believe this is an "Aulani" activity but rather in independent company that operates off the beach in the lagoon at night. We saw them out there when we were doing the Aulani stargazing activity. They were set up towards the Four Seasons end of the lagoon. They looked pretty cool out on the water but I can't comment on whether they attract fish, though the fish activity in the lagoon may be different at night then during the day anyway (there isn't much during the day as PP mentioned).
As far as needing reservations, I'm not sure but given the way they seem to operate (bringing in the SUPs and work off the beach in the evening) I'd think it would be wise to have reservations since they don't bring tons of boards. But I'd bet you don't need to make reservations that far in advance. I also only saw SUPs, not kayaks, so its possible they don't bring those unless someone books them?