"current security context is not trusted" for job running as a SQL login accessing linked server

Question

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?

Answer 1

I fixed this by 1) removing the WITH EXECUTE AS 'user1' clause from the stored procedure and 2) removing user1 from the job -- it is no longer the owner or the "Run as user" of the job's step.

My final configuration is:

• SQL Server Agent job owner is a sysadmin, not user1
• The job's step "Run as user" is left blank
• The step calls a stored procedure which user1 has rights to execute
• 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".
• user1 has the appropriate permissions to query on the remote server.
• user1 does not need to have the sysadmin role or any such administrative role