Home > SSIS Best Practices > SSIS – How to change the default SQL Agent login

SSIS – How to change the default SQL Agent login

Challenge:
In our production environment, we have a dedicated ETL server, while the target database is on a different server.

    server 1 = ETL server
    Server 2 – production DB server

How do SSIS jobs on server 1 access the target database on server 2?

As an ETL developer and data analyst, I don’t do this every day. So I am writing down my notes here.

Goal:
1) need to run SQL Agent job from server 1 to process data on server 2
2) do not want to have a SQL account to access server 2
3) need to have Windows Authentication account with Integrated Security when accessing server 2

Solution:

  1. create a domain account SVC-SQLAgentAccount in domain myDM (where server 1 and server 2 reside)
  2. add account SVC-SQLAgentAccount as a user to both server 1 and server 2
  3. grant account SVC-SQLAgentAccount appropriate role and access on both server 1 and 2
  4. stop the SQL Agent service, change the service’s login from LocalSystem to myDM\SVC-SQLAgentAccount
  5. restart the SQL Agent service
  6. If the service fails to start, it probably means myDM\SVC-SQLAgentAccount doesn’t have the “run as a service” right on the box
  7. For the job on server 1, set the data source to (use Integrated Security)
    Data Source=Server2;Initial Catalog=myTargetDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
  8. After the job ran successfully, you should see the step 

Executed as user: myDM\SVC-SQLAgentAccount
not   
Executed as user: server1\SYSTEM

Categories: SSIS Best Practices
  1. No comments yet.
  1. February 22, 2011 at 11:36 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: