Job that include Linked server failed

Question

Job that include Linked server failed

Kiat Tang

SSChasing Mays

Points: 629
More actions
October 18, 2006 at 9:37 am

#192722

Hi guys,
I have a job that's created in SQL Server 2000 which executed a stored procedure. The stored procedure will retrieves data from a table from a linked server (SQL Server 2005).
If I execute the stored procedure directly everything works fine. But I notice that it fails when it runs in a job. The linked server (2005) impersonates the same user account on the main server(2000).
I'm suspecting this is a permission privilege problem. But I'm not sure what to look for.
Any idea? Thanks in advance!
Here's the log:
Date10/18/2006 10:03:30 AM
LogJob History (<job name> )
Step ID1
ServerOBERON
Job Name<job name>
Step Name<description>
Duration00:00:00
Sql Severity16
Sql Message ID7300
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: <user>. OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Arman Khan SSC Eights! Points: 864 More actions · Answer 1

Hi Kiat! I think you are rite.I am not very Experience DBA but i guess you have permission privilege problem and you can use your 'SA' Account. Let give a shot ...................

Hopefully it works.

Let me know by the results.

Kiat Tang SSChasing Mays Points: 629 More actions · Answer 2

hi Adil,

thanks for the input. I tried to connect to the linked server as a sa account, the same error happens..

CDBA Hall of Fame Points: 3266 More actions · Answer 3

Hi

Are you using WIndows Authentication Mode on both the boxes? Please cross check if the account which is running the job in your 2K box is having the required permission on the target box?

Regards,

Utsab

CDBA

Kiat Tang SSChasing Mays Points: 629 More actions · Answer 4

Hi Utsab,

no, I'm using a SQL Server Account.

I was doing a test earlier on the SQL Server 2005 by creating a job on it. The test job failed, with the following message:

The job failed. The owner ( <user name> ) of job Test does not have server access.

This is a sysadmin account. I'm not sure what does the "server access" refer to...

CDBA Hall of Fame Points: 3266 More actions · Answer 5

Assuming your job configurations as below now:

U r job is at 2K box and 2K5 is a linked server

Ur job works on a 2K5 table of that linked server

U uses SQL Authentication on the 2K5 and 2k Box

Please try below:

Add the same SQL Account with same password in 2K box also and then run the job using that users credential. Alternately better to go for a domain account using Mixed Mode authentication. This should resolve the issue.

Pl. let me know if you face any issue.

Regards

Utsab

CDBA

Kiat Tang SSChasing Mays Points: 629 More actions · Answer 6

Hi Utsab,

thanks for your reply.

your assumptions are accurate.

Both the 2K and 2K5 are in Windows and SQL Server authentication mode.

I created a "test" job on the 2K5 box that was doing the SELECT query on the table of the local database. It failed, with message saying that the owner of the job does not have server access. The account is a sysadmin account, and I even included it with SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole from the msdm database and still doesn't work.

It only work when I changed the owner of the job to "sa"... What permission do I overlook?

Thanks for your help!

CDBA Hall of Fame Points: 3266 More actions · Answer 7

Please grant permission to the account on the selected database specifically and ensure that there is no deny on that user to that db/table. Deny overrides grant.

As you told, you cannt run the test job on 2k5 box locally only and the error tells it is a permission issue.. so i suggetst u to login to the db using query manager with the test account. The same error will be thrown there also when u ll execute query.

Else.. is it possible to create a new SQL account and plcae it in both the boxes [must be with the same password] and then fire the job? Job must be running with that ac. It should work in that way too...

Regards

Utsab

CDBA

Nancy Forbes SSC-Addicted Points: 487 More actions · Answer 8

I had the same problem you described with permission issues. I was able to resolve it using a non-administrative account. (As Utsab Chattopadhyay suggesed in the else paragraph)

1. Add a SQL account on the local server (Security - Logins) that has the same name as an account on the remote server. This remote account must have the necessary permissions to the objects you will be accessing. I gave it the same password as well.

2. Add the Linked servers. Use the account above as both the Local Login and the remote user. Select "Be made using this security context" and select Impersonate.

3. Add the Login as a user in the database (local server) and give permissions to the objects that it needs to access. In my case this was Exec permissions on specific stored procedures.

I'm not sure you have to name them the same but I tried using a different local account and couldn't get it to work. It appeared to me to be using the local account name to select the remote data instead of doing the impersonate.

yana-632027 Grasshopper Points: 19 More actions · Answer 9

I have same problem try to run a job that uses linked server from SQL2K to SQL2005 server.

I've created the same user account on both servers with all the permission on all tables and gave it the right to execute the sp that needs to run as a job.

I've mapped logins to each other in the linked server. They have the same password.

The job is setup to run as that account.

Still, the error message that I get tells me that the account does not have remote access privileges.

Please advise what else I need to look at. Your help is greatly appreciated.

Yana

ladymiion99 Newbie Points: 7 More actions · Answer 10

Thank you so much. This post really helped me because I have been having this problem for over a week. I felt like I'd tried everything and I had tried all of these things, but not all together.

Very helpful!!! Praise the Lord!