Of course there is some truth in it, but the myths get in the way of the story.
There is good interview on the SJFA site: http://www.scottishfa.co.uk/sjfa/scottish_football.cfm?page=1711
According to the Bonnyrigg Rose history, there was indeed a goal scored by Connelly (sic) in a Junior Cup tie v Broxburn on 13 October 1951, a match which Broxburn won 3-1. No other goals or appearances by Connery/Connelly are mentioned, but the book does say that T Connery was released by Bonnyrigg Rose in the summer of 1953 (along with nine other players).
There are no relevant newspapers online, as far as I can see, that would provide any further info.