I have created a linked server by using the following script:
exec sp_addlinkedserver @server='MyConnection',@srvproduct=',@provider='SQLOLEDB',@provstr=
'user=sa;pwd=XXXX;Integrated Security=SSPI;Initial Catalog=master;Server=DOMAIN\PCNAME'
I would like to retrieve data by using a Sql server agent job. And basically, I have used a t-sql script such as:
insert into RPDW2010.dbo.dimClass_MAX
I have tried to run this job by using different user combinations:
- NT Authority / System
- DOMAIN NAME/ Administrator
I get always the same error even I change the user name:
Executed as user: dbo. The server principal "sa" is not able to access the database "RPDW2010" under the current security context. [SQLSTATE 08004] (Error 916). The step failed.
All the local and remote server in the same domain and I have googled several days to solve this problem.
View Complete Post