Execute a T-SQL job with a different connection string

Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string?

The reason I want to do this is is to a avoid a trigger on the database that specifically distinguished on the Application Name used by the connection string.

I know you can execute an integration package with a different connection string, but I hope this is possible for T-sql jobs as well.

I don't think there's a built-in way to do this, but here are a few alternatives:

-
Use OPENDATASOURCE like this:

EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=master;Application Name="test app"')
    .master.sys.sp_configure;

Note that using OPENDATASOURCE requires you to turn on the Ad hoc Distributed Queries sp_configure setting (as noted in the Remarks section of the documentation) even though you won't be writing code that accesses another server (SQL Server doesn't know that).

-
Create a wrapper SSIS package that specifies the application name in a Connection Manager, and then uses an Execute SQL task to run the stored procedure.

-
Create a wrapper command-line app that takes a connection string and a command as parameters; execute it using an Operating System (cmdexec) job step.

StackExchange Database Administrators Q#42066, answer score: 5