Walter Barrie, from Glasgow, was playing in a match with members from the Afghan National Army (ANA) and one other British soldier at his base last November 11 when he was shot at close range in the Nad-e Ali district of Helmand Province, Oxford Coroner's Court heard.
Mohammad Ashraf was dressed in full ANA uniform when he approached the pitch and fired about 10 shots towards Captain Barrie using an M16 rifle.
As the other soldiers fled, Ashraf moved towards a welfare tent for British troops and continued firing, the hearing was told.
The attack was stopped when the Afghan soldier was shot dead by International Security Assistance Force (Isaf) soldiers, with wounds to his thigh, shoulder and head, the inquest heard.
It is believed Ashraf carried out the fatal assault after his brother was wounded while he tried an insider attack on Spanish forces in Afghanistan a day earlier. One ANA soldier, referred to as Private A, said Ashraf had spoken of concerns for the welfare of his brother on the day of the shooting spree.
Intelligence officers had warned British troops of another possible "green-on-blue" attack on November 10, stressing that 60% of insider attacks are followed by another within 72 hours.
It led to the cancellation of a football match at Captain Barrie's base on the day before he was killed.
However, there had been no "specific intelligence" before Captain Barrie's death linking Ashraf with the earlier attack on Spanish troops in the Badghis Province, the inquest heard.
Captain Barrie, who was married with a teenage son, Callum, had been mentoring and advising a brigade of the ANA to take over security in southern Afghanistan.
The 41-year-old, of The Royal Scots Borderers, 1st Battalion The Royal Regiment Of Scotland, had served for 25 years, including tours of Iraq, Bosnia, Kosovo, Northern Ireland and Afghanistan in 2008.
A post mortem examination found he died from a gunshot wound to the chest.
In a statement read to the court, consultant forensic pathologist Dr Nicholas Hunt said the injury had caused "extremely rapid loss of consciousness, which led to death".
Private Ryan Houston, who was on duty as a "Guardian Angel" to patrol the base, told the hearing he was alerted to the attack when he heard "a burst of fire".
"I turned in the direction of the football pitch but I did not see the person holding the weapon," he said. "Captain Barrie was not moving and appeared motionless. I think I shouted, 'Man down'."
A smoke grenade was thrown on the pitch to conceal Captain Barrie's body from the killer and allow medics to reach him, the inquest heard.
Colour Sergeant George Parker told the inquest that, after hearing the shots, he exchanged fire with the Afghan soldier, who then turned and ran off.
"I believe because he was firing from the hip, he was not accurate at all," he said.
Darren Salter said Captain Barrie was unlawfully killed while on active service and his killer acted alone.
Mr Salter said although finding the motive behind the killing involved "a degree of speculation", it was thought to have been linked to the earlier insider attack on Spanish troops in which the rogue soldier's brother was wounded.
The fatal shot was most likely to have been inflicted while Captain Barrie was lying face down on the ground, causing an "extremely rapid loss of consciousness" that led to his death, the coroner said.
The verdict was unlawful killing.
Mrs Sonia Barrie, who broke down in tears, said: "Captain Walter Barrie was a great man, a doting and amazing father and a fantastic husband. He was much loved and will be missed by many."