What is the proper way secure a T-SQL agent job that moves data between linked servers?
Setup:
- Microsoft SQL Server 2012 (SP3) Enterprise Edition
- SQL Server Agent running as
NT Service\SQLSERVERAGENT
- Microsoft SQL Server 2008 R2 (SP3-GDR) Express Edition
- Servers are linked to each other
- Link security context set to "Be made using the login's current security context."
My goal is to select data from the Enterprise server and insert it into a table on the Express server. The Express server is running vendor software that needs integration with our main database.
I'm trying to use a dedicated account with minimal permissions for this job because the Enterprise server is our core server with a variety of workloads, and I don't really trust the Express server hosting the vendor software will always be secure.
I tried creating an Active Directory user with the necessary database and table permissions on each server, but then I found that Agent Job steps of type T-SQL cannot use proxies to run as another user.
What's the correct way to do this? The solutions I'm finding look silly or risky, like lowing the security context of the server link, making a PowerShell wrapper, or putting credentials in the T-SQL itself. Surely there's an elegant, secure way to move data between linked servers on a schedule? Or is SQL Server lacking in this area?