We don't really know; I've seen Scottish and (less often) Irish postulated. He's Canadian, and we don't even know where in Canada he's meant to be from. His sister goes by Jeannie, and she named her daughter Madison. I don't think that tells us much. :/

Incidentally, now I have a scene from The Sentinel running through my head.
Blair: *in stolen labcoat* Hello, I'm Doctor McKay.
Jim: *on examination bed* Your nametag says 'McCoy'.
Blair: Uhhhh, yes -- but McKay is the correct Welsh pronunciation of my name.
Jim: *clearly not buying this*