SQL 2005 Linked Server login
When I query a linked server using in SQL 2005 using SQL Server Managerment Studio the query is executed successfully. However, when I try to schedule a job using the same query I get the following error:
Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "SQLNCLI" for linked server "jgws-pos-lane01" does not contain the table ""lanesql"."dbo"."obj_tab"
How do I configure the permissions to be able to schedule the job?
Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "SQLNCLI" for linked server "jgws-pos-lane01" does not contain the table ""lanesql"."dbo"."obj_tab"
How do I configure the permissions to be able to schedule the job?
This issue is the user does not have permission. The servers are able to communicate. Check the remote user which you used to connect to the Remote Server has the permission to access the database.
Also in management studio can you see the target table when you expand the linked server icon and click on the "Tables" icon.
If you do not specifiy a login for the linked server then it defulats to the "current" profile where the query is being run from, and tries to use those credentials to login...
So, best thing to do is to add a user login to the linked server using sp_addlinkedsrvlogin viz:
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
e.g. EXEC sp_addlinkedsrvlogin 'jgws-pos-lane01', 'false', 'Domain\Myuser', 'Myuser', 'Mypass'
It is described in books online...
So, best thing to do is to add a user login to the linked server using sp_addlinkedsrvlogin viz:
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
e.g. EXEC sp_addlinkedsrvlogin 'jgws-pos-lane01', 'false', 'Domain\Myuser', 'Myuser', 'Mypass'
It is described in books online...
ASKER
I am not sure which user the NT AUTHORITY\SYSTEM is referring to, is it the user currently signed in on the server? Does the same user have to be signed on to the linked server? I cannot see any tables when I expand the linked server icon, I just see the the name of the linked server. I have also tried the sp_addlinkedsrvlogin syntax and that did not work either, my job is still failing.
ASKER
Can anyone assist me based on my last comment please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much Mark Wills, your comment assisted me in resolving my issue. The SQL configuration was actually Windows only and not mixed mode, once I changed it to mixed mode the job completed successfully.
Thanks again
Thanks again
Excellent - that is good news - it is funny, sometimes those little details - the ones where you go "D'oh" afterwards can be the trickiest...