Tag Archives: CmdExec

SQL Job using LINK Server using Service Account

There are usual errors when using a LINK Server while also using a Service account on SQL Job. Usual errors are as follows:

  1. Access to the Remote Server is Denied Because the Current Security Context is not Trusted
  2. EXECUTE AS USER failed for the requested [user] dbo in the [database]
  3. After resolving those 2 issue its still failed

Assumptions of your setup:

  1. You have created a credential object under Security > Credentials
  2. You have created a proxy that is using that credential under SQL Server Agent > Proxies > Operating System (CmdExec)

Here are the steps you need to do:

  1. Change database TRUSTWORTY to ON

  2. Reapply database owner. This is needed because the SID recorded is now mismatch. c/o Rob Reid on this post.

    ALTER AUTHORIZATION ON Database::[database] TO [domain\user]
  3. Update your SQL Job step to use CmdExec instead of T-SQL

    Job Step Properties
    – Type: Operating system (CmdExec)
    – Run as: Your service account credentials using a proxy
    – Command
    SQLCMD -I -b -S $(ESCAPE_DQUOTE(SRVR)) -d DatabaseName -Q"EXEC usp_YourStoredProcedure"