Basically, this issue is all described here…
In a nutshell, a T-SQL job executes in the context of the job owner. If the job owner is sa or another member of the sysadmin role is is able to make a linked server call successfully.
However, if the SQL Agent job T-SQL step is run under a proxy account and that proxy account is not a member of the sysadmin role, it will fail because of impersonation. Part of the linked server call is to use EXECUTE AS when the job owner is not a member of the sysadmin server role. This is by design and a built in security feature. The problem here is that, on one hand Microsoft recommends the use of Windows Authentication yet on the other hand they are saying for this issue to use SQL authentication and login mapping. If you are anything like me, then you don’t have SQL ( a.k.a Mixed Mode) enabled on your server at all.
Without the use of a Linked Server, there are only a few other ways to be able to connect to another server. Those being:
Only SQLCMD and OPENDATASOURCE will let you execute a stored procedure. When it comes to executing T-SQL job step, there is only 1 option – OPENDATASOURCE.
Basically, the syntax is:
EXEC OPENDATASOURCE(‘SQLNCLI’, ‘Data Source=<my SQL Server>;Integrated Security=SSPI’).msdb.dbo.sp_start_job @job_name=‘Brent’
In this example, I have specified SQLNCLI as the provider_name. If you are running, for example, SQL2014, then you will have SQLNCLI11 available as a provider. Therefore, if you always supply SQLNCLI as the provider, SQL Server will always redirect to the latest version of the SQL Native Client.
By using this method you don’t have to use elevated privileges and you shouldn’t have to modify your processes too much.