Good day,
I've got 2 jobs on a SQL 2005 instance that run one identical T-SQL step. That step executes a cross server query to a remote 2005 instance. The only difference between the jobs is one is owned by a sysadmin and one is not.
The job that is owned by a sysadmin executes under the Agent's startup account and the one that is not owned by a sysadmin executes under the job owner's account. So far so good.
However, the job owned by a sysadmin attempts to connect to the linked server using TCP/IP, the other using Named Pipes. I know this because the non-sysadmin job fails with a Named Pipes connection error. I don't want to connect over Named Pipes because it does not support Kerberos. There is only one linked server configured so both queries are using it and there is no property to set in the linked server that relates to network protocol.
Note, I understand the remote server needs to listen on Named Pipes if I want the connection to succeed, but that's not my question. My question is why does the sysadmin account attemtpt to connect using TCP/IP and the non-sysadmin account attempt to connect using Named Pipes?
I continue to reproduce this, so it's not a fluke (at least not in my environment). Am I missing some configuration setting? Can anyone explain this?
Thanks so much.