0

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?

Improve this question
1
  • 1
    I know you've already accepted an answer for this but since you are talking about pushing data from a Linked server it might be better to simply make an SSIS package and run that instead. It may be faster than the alternative.
    – Zane
    Nov 29, 2018 at 19:39

1 Answer 1

Reset to default
1

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.

Improve this answer
3
  • So I would create the EE server's computer account as a Login and database User on the SQL Express server?
    – bendodge
    Nov 29, 2018 at 17:25
  • Yes. And if you try before you do, you should see a login failure in the SQL Error log of the Express server complaining that a login failed for YourDomain\EEServerName$.
    – David Browne - Microsoft
    Nov 29, 2018 at 17:44
  • The computer account wasn't available to select via the SSMS GUI, but it worked via CREATE LOGIN [MyDomain\SQLComputerName$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]. Thanks very much! Also, I'm only pushing 1k rows, so performance wasn't an issue.
    – bendodge
    Nov 29, 2018 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.