15

I'm curious how to set up a SQL job step to be executed as a different SQL login account. It appears I need to set up a New Proxy Account which requires an existing credential. When I create a credential my only option is to use a Windows Login credential.

The job I'm attempting to run is below. There are other additional statements but when I set the job step to run as the SQL login it fails.

insert into [dbo].[TableA]
SELECT 
       ss.[Ref_ID]
      ,mm.[studentID]
      ,mm.[studentPersonID]
      ,mm.[studentFirstname]
  FROM [dbo].[TableB] mm
  left outer join [dbo].[TableC] ss on ss.parentPersonID=mm.parentPersonID and mm.studentPersonID = ss.studentPersonID
  where ss.Ref_ID is not null;

When this is run through a SQL Job Step it fails.

Executed as user: an_admin_account. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

I'm unclear as to why it's trying to access a remote server when all of these tables exists on the local db.

Improve this question
1
  • A SQL job step already has a connection to the SQL instance. So you need to change the existing security context, rather than making a new connection with a password. If you want to create a new connection, you could use a sqlcmd command, or PowerShell script, which could then use a SQL login and password as parameters within the script. But you should consider what you need to do, and whether a stored proc with its own context will suffice.
    – Rob Farley
    Mar 15, 2014 at 23:02

3 Answers 3

Reset to default
22

If you are configuring a T-SQL job step go to the Advanced Page and configure the "Run as user" to the login of your choice. enter image description here

If you are working with other job step types like PowerShell it will require a proxy account to be configured.

Improve this answer
3
  • I've tried this but now the job fails saying " Access to the remote server is denied because the current security context is not trusted". However if I run the script while logged in as the user, it is able to run it just fine. There is not linked server in the query so I don't understand why it's referring to a remote server. Any thoughts?
    – Geoff Dawdy
    Mar 15, 2014 at 17:20
  • 1
    If you could add to your question what the job is trying to do and exact error would help us point you to a solution.
    – user507
    Mar 17, 2014 at 5:48
  • In my case it ignored the setting. Using SQL 2012.
    – Miguel Sv
    May 2, 2019 at 10:37
1

Use EXECUTE AS at the start, or create a stored procedure which executes in a particular context.

Improve this answer
2
  • This might be bad. If you want call a stored procedure with a restricted user it could REVERT to sysadmin (or job owner) privileges. Workaround would be using a cookie.
    – Jens W.
    Mar 15, 2014 at 17:10
  • A stored procedure that has been created using the EXECUTE AS option will handle the privilege escalation, and not allow someone to change part way through. If there's no stored proc available, then impersonating a different login will do the trick, like with EXECUTE AS at the start of the script.
    – Rob Farley
    Mar 15, 2014 at 22:59
-1

you can create proxy account and run as proxy account .

https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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