February 13, 2014 at 9:40 am
I have a SQL agent job which has been running daily for a year. It fires a stored proc which populates a table then runs this statement:
Exec Xp_Cmdshell 'bcp Sfidata.dbo.tblOrderDetail IN "C:\OWDCGiftOrderTransfer\tblorderdetailOWDC.txt" -n -S OWDC -U sa -P password'
OWDC is a linked server at another location.
I made some changes to the proc, but did not touch the line above. Now I'm getting this error when the job runs from the Agent:
Date2/13/2014 9:45:38 AM
LogJob History (Export GiftOrders and GiftCerts to OWDC)
Step ID1
ServerVSQL2
Job NameExport GiftOrders and GiftCerts to OWDC
Step NameGather Recent Gift Orders and Send Detail
Duration00:00:00
Sql Severity16
Sql Message ID7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted1
Message
Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "SQLNCLI10" for linked server "owdc" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "owdc". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI10" for linked server "owdc" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
However, I can run the proc from SSMS and it runs fine and gives no errors. Anyone have any ideas what could have changed and caused this error?
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 14, 2014 at 8:33 am
What is the Security context configured in the Linked server.
February 17, 2014 at 6:33 am
there was an "sa" local login with the Impersonate box checked. This makes sense because that login obviously exists on the linked server. There was also my windows login with the Impersonate box unchecked, but the "sa" remote user and password were populated. I added the NT Authority\System as a local login with the "sa" remote user login and it worked.
The statement which was causing the failure was a BCP command which sent data to a table on the linked server. apparently even though the BCP was called from a stored proc, it runs under the local system (server which SQL resides on) login of NT Authority\System. The BCP command uses the "sa" login for the remote server. So, I REALLY couldn't understand why there would be a login problem.
The weird thing is that this job has been running every day for almost a year. When I implemented it, I never looked at the linked server logins because we query it all the time. If we had Windows auto updates turned on I could blame it on an update changing the linked server logins, but we don't keep auto updates turned on for servers. I don't know what changed but it's working now.
Thanx for your reply. You were right on the money for the cause of the problem!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 19, 2014 at 8:51 am
Good to know !
April 23, 2014 at 2:54 pm
What is the server missing if my MSDTC properties looks like this ? :
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply