Nowadays the expression “son of a gun” is likely to be a respectable or nice-Nelly substitute for an epithet of the when-you-call-me-that-Smile variety, a male offspring of the female of the canine family.
But it is also used as a term of affectionate regard, as between pals.
In general, however, it is the opposite extreme, accompanied usually by a derogatory adjective and used as a term of contempt.
It has been in the language for at least two and a half centuries, and if one is willing to accept the story of its origin given by Admiral William Henry Smyth of the British navy in The Sailor’s Word-Book, written about 1865, here it is:
“An epithet conveying contempt in a slight degree, and originally applied to boys born afloat, when women were permitted to accompany their husbands to sea; one admiral declared he literally was thus cradled, under the breast of a gun-carriage.”