Job execution and Linked Servers

Question

Job execution and Linked Servers

Nisha-475382

Hall of Fame

Points: 3535
More actions
March 31, 2008 at 12:43 am

#204833

Server Name: ServerA
DB Name: Live_DB
Server Name: ServerB
DB Name: Legacy_DB
Linked Server A_Link on ServeA is linked to ServerB
The following SP on ServerA Live_DB works fine
ALTER PROCEDURE [Live_DB].[dbo].[usp_TestSP]
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE TestTable (ID INT)
INSERT INTO TestTable
SELECT User_UID FROM A_Link.Legacy_DB.dbo.crew_master_import
END
However, when I create a Job (ImportJob) on ServerA to trigger this SP, I get the following errors (ImportJob>>View History):
Executed as user: NT AUTHORITY\ NETWORK SERVICE
Msg 7399, Sev 16, State 1, Line 13 : The OLE DB provider "SQLNCLI" for linked server "A_Link" reported an error. Authentication failed. [SQLSTATE 42000]
Msg 7303, Sev 16, State 1, Line 13 : Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "A_Link". [SQLSTATE 42000]
Msg 7412, Sev 16, State 1, Line 13 : OLE DB provider "SQLNCLI" for linked server "A_Link" returned message "Invalid authorization specification". [SQLSTATE 01000]
Where am i going wrong?
In Job properties>> Steps >> Edit >> Advanced>> Run as user.... what am I supposed to have here?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 1

Msg 15274, Sev 16, State 1, Line 13 : Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000]
Job 'ImportJob' : Step 1, 'Step1' : Began Executing 2008-03-29 15:39:01

When I set execute as user to a valid user (that I see in Live_DB>> Security>> Users), I get the above message!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

You need change the Linked Server definition properties to give access on the remote server whatever context your SQL Agent Jobs run under. I usually do this by creating a special SQL Login for this purpose on the remote server.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Sponge-592981 Ten Centuries Points: 1225 More actions · Answer 3

Check to see what the gentelemen prior has indicated and let us know if it worked. If it did not work you can try to see the new TRUSTWORTHY property is enabled. By default this is set to false on all user databases. SQL Server tighten up the link server security.

Here is the syntax

ALTER DATABASE

SET TRUSTWORTHY ON

Note: I have read a few articles indicating that this must be set to the local database as well as the remote source.

I love it when a plan comes together! (A-Team) 😉

kris386 SSC Eights! Points: 842 More actions · Answer 4

I was having the same problem even when executing the job as a user mapped to a valid login on the remote server. Changing the

ALTER DATABASE < DATABASENAME>

SET TRUSTWORTHY ON

setting on my local databases fixed the problem.

Thanks Sponge.

sunkara_satya SSC Rookie Points: 40 More actions · Answer 5

Dear Friends

It is the most valuable/most appropriate post for a novice who is tangled with security steps at every code step.

Thanks for resolving my really blocked head.

Kind regards

satya

eschupp Valued Member Points: 73 More actions · Answer 6

Thank you, this was the perfect answer for me as well.