Sql-server – How to secure SQL Agent Job that INSERTS to a linked server

sql serversql-server-agentt-sql

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?

Best Answer

SQL Express doesn't have SQL Agent, so I'm assuming you're running the job on the EE Server.

In that case, you should be able to simply use a TSQL job step on the SQL EE box The SQL Server Agent Account on the EE server already has rights to the local SQL Server. You can grant it access to the remote SQL Express server. If running under Network Service or a local virtual account, you would grant the rights to the computer account of the EE server. EG

CREATE LOGIN [MyDomain\SQLEEComputerName$] FROM WINDOWS

Pushing data over a linked server is not very fast, however. Pulling is faster. So you'll need to test the performance.