Just did a search for more info about this because I know that Scotland voted strongly against Brexit in the referendum, and so I think that would be a big factor in the negative reaction he's receiving. And on a personal level, as someone who is mainly Scottish on my mother's side and mainly English on my father's side (similar to Rod's ancestry, but the other way around), I take an above-average interest in things like this. I found the following article where he both shows his support for Johnson but also says that he's a "remainer" (and therefore not in favour of Brexit) ... he also would prefer to see Scotland continue to be part of the UK, but would also be supportive of the Scots if they decide to leave. And while that might all seem kind of contradictory, I think it may reflect the mixed feelings that I suspect many people there have -- especially if they have a foot in both camps on these issues.
https://www.thescottishsun.co.uk/tvandshowbiz/4959875/rod-stewart-independence-happy-union-split/