A “pain in the neck” is not an actual physical pain, nor is it located so much in the neck as in the head.
That is, the pain is mental, rather than physical.
One who is the cause, who is the pain or gives the pain, is just persona non grata to the recipient.
Others may consider him or her a pleasant and entertaining person, even well informed, but to the sufferer he or she is an unmitigated bore.
The cause of the pain may also be inanimate; that is, a house or a picture or a newspaper, or anything which one regards as contemptible or lacking in merit.
The expression is American slang of the past fifty years, a polite variation of a pain previously associated with another part of the anatomy.