Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Agent Job Authentication error accessing linked server Expand / Collapse
Author
Message
Posted Thursday, February 13, 2014 9:40 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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:

Date 2/13/2014 9:45:38 AM
Log Job History (Export GiftOrders and GiftCerts to OWDC)

Step ID 1
Server VSQL2
Job Name Export GiftOrders and GiftCerts to OWDC
Step Name Gather Recent Gift Orders and Send Detail
Duration 00:00:00
Sql Severity 16
Sql Message ID 7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 1

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."
Post #1541303
Posted Friday, February 14, 2014 8:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 610, Visits: 433
What is the Security context configured in the Linked server.

Post #1541626
Posted Monday, February 17, 2014 6:33 AM This worked for the OP Answer marked as solution


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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."
Post #1542080
Posted Wednesday, February 19, 2014 8:51 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 610, Visits: 433
Good to know !
Post #1543079
Posted Wednesday, April 23, 2014 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:26 PM
Points: 7, Visits: 22
What is the server missing if my MSDTC properties looks like this ? :


  Post Attachments 
MSDTC - doesn't have all options.JPG (1 view, 192.04 KB)
Post #1564460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse