I'm running into the error "Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)" while trying to run a stored procedure from a job, and the stored procedure queries against a linked server. The job is configured to run as a SQL login which I'll refer to as user1, and I have given user1 permission and ownership in every possible place.
Here is how the job is set up:
- A SQL Server Agent job whose owner is user1
- A step in the job is configured to "Run as user" user1 on the Advanced tab (The "Run as" list on the General tab is a blank list -- not sure if that's a factor)
- The step calls a stored procedure which user1 has access to execute
- The stored procedure is defined with
WITH EXECUTE AS 'user1'
- The linked server has a local/remote mapping for user1 with the remote user and remote password entered. The username and password are the same on both servers. The "For a login not defined in the list above, connections will" option is set to "Be made using the login's current security context". I've also tried setting this option to "Be made using this security context", with user1 and its password repeated.
- user1 has the appropriate permissions to query on the remote server.
- I even made user1 a sysadmin on the local server
Despite all of these, I still get the access error. I must be missing something somewhere... Any ideas?