2

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?

  • user1 is a SQL user not Windows Authentication yes? – Josh Simar Sep 10 '15 at 22:03
  • @JoshSimar Yes, it's a SQL user. No Windows auth. – Keith Sep 10 '15 at 22:23
  • If you login as user1 and run the portion of the Agent Job in a T-SQL window do you still receive the same response? – Josh Simar Sep 11 '15 at 0:52
  • @JoshSimar If I remove WITH EXECUTE AS 'user1' from the stored proc, I can run it without a problem when logged in as user1. But when I leave that statement in, I do get the same error as stated in the question. – Keith Sep 11 '15 at 12:36
2

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 access 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
  • Removing "Run as user" did it for me! – pim Dec 12 '17 at 17:04
  • Well put, sir. Led me to my solution. Much appreciated. – Lopsided Mar 14 '18 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.