Should I ask for a recommendation letter from a professor who has a bad non-academic reputation?
To me, the key question seems to be whether the type of misbehavior is something that is likely to be seen as impinging on their judgement as an academic.  

For example, if the person was known to have been prosecuted for fraud in a company they ran, that would be quite likely to affect perception of their honesty and judgement in recommendation letters.  In the example you give, however, I think that an impaired driving conviction is likely to be viewed as a form of misbehavior largely orthogonal to conduct and judgement as an academic, and therefore not likely to be any problem for you.