I take mine either way....nite/day/lights on/lights off...but the biggest thing is see if your camera has a macro setting..which is a little icon that looks like a flower that's where I get the best shots
All lights off except for the tank light. That will help cut down on reflections against the front glass. You may even need to blackout any windows. Macro is a wonderful tool for close up work on your fish. Using the flash for a higher shutter speed is also a good thing if your camera does not have a manual shutter speed adjustment. With the flash on you will need to take pics on an angle to the glass so that the flash does not just reflect back at the camera & ruin the shot.
Agree, wait until dusk/night, turn room lights and other lights off. Leave tank light on. AI'm the camera with a slight downward angle towards the subject, this helps reduce flash feedback off the back glass. Use rocks, wood, and plants in the tank to your advantage for a flash absorbing background.