Think about it in the context of a two-way ANOVA, with main effect factors A and B, and an interaction factor AxB.
B could be a moderator of A, in that the effect of A could be changed when you take into account the level of factor B. This is simply a significant AxB interaction.
B itself could be correlated with the dependent variable (i.e., there could be a significant main effect of B), but to run an ANOVA, you wouldn't want B to be correlated with A.
Now, in the more general case of regression, it's not always possible to have the independent variables totally independent of each other since they are commonly set up as observational studies and not tightly controlled experiments.
So, it's "ideal" for the moderator to be uncorrelated with the other predictors, although not entirely possible or necessary.
And, it doesn't really matter, in my view, if the moderator is correlated with the criterion or dependent variable.