May 25, 2011 at 10:56 am
I have a sp, tested with no any problem, I packed it into a job, failed with the following reason:
failed with the following error: "The OLE DB provider "SQLNCLI" for linked server "10.40.1.103" does not contain the table ""TSFSREPORTING"."dbo"."ISC_OrgUnit_Address"". The table either does not exist or the current user does not have permissions on that table.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
However, I double checked the user's privilege, it is the owner on the linked server, how to explain this error message?
Thanks.
May 25, 2011 at 11:02 am
If you change the job owner to youself do you still get the same error?
Did you change environements between the testing of the sp and conversion to job?
May 25, 2011 at 11:06 am
It is under my own name - dbo of that database
May 25, 2011 at 11:07 am
Ninja's_RGR'us (5/25/2011)
If you change the job owner to youself do you still get the same error?Did you change environements between the testing of the sp and conversion to job?
This is the same thing on the same server
May 25, 2011 at 11:15 am
How's the linked server configured? Use current connection?
Can you create a specific login for that linked connection and give explicit permissions?
For a TEST you can use SA for the remote login just to see if that fixes the issue and then do it right.
May 25, 2011 at 11:34 am
Let's say my login is 'aaa\myname', this login has neccessary privilege on the linked server, this login is also the one I used to create the sp, run it without problem; when packed into job, it failed.
However, I do have more clues here: it seems the job is executed as some built-in account, I tried to execute it as 'aaa\myname', I don't see any setting in the "job"'s property.
I then tried to see if I can add the "exec as" to the sp, it was rejected saying the user doesn't exist
I then tried to add the user to the database, it was rejected saying the user already (on the server)
I then went to the server level's security, tried to map it to the database, still got rejected.
What should I do now?
Thank you very much.
May 25, 2011 at 2:21 pm
I think you misunderstood Ninja's question. There are several security contexts in question here. On a linked server the second tab or item is usually security. What the settings there? That controls the login used to connect to the remote server and if it is wrong no matter what you do on the local side it won't work..
CEWII
May 25, 2011 at 3:42 pm
Elliott Whitlow (5/25/2011)
... On a linked server the second tab or item is usually security. What the settings there? That controls the login used to connect to the remote server and if it is wrong no matter what you do on the local side it won't work..CEWII
The currently setting (I can change if need) is to use a special login (SQL authentication: username + password - I have password), which means not the same as the one to create the sp and job (my domain id)
Thank you.
May 26, 2011 at 8:55 am
As I read your response, you are using the last item on that tab, which typically reads: "Be made using this security context:" followed by username/password boxes.
Then the question is does THAT user have the required rights, because when the linked server is used those credentials are used to login to the remote server and whatever rights that user has are what YOU have.
CEWII
May 27, 2011 at 6:09 am
thank you for your reply.
i created a new one w/ same setting, it works
May 27, 2011 at 6:11 am
halifaxdal (5/27/2011)
thank you for your reply.i created a new one w/ same setting, it works
Awesome, now just make sure you created the new user with the minimum rights in needs to make the link. It's a big security risk to use sa for the login of the linked server.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply