Consider the following SQL Server setup:
Server 1: Utility Server
Server 2: Instance1 (5 Databases)
server 3: Instance 2 (10 databases)
When I installed SQL Server on the Utility Server I used 4 domain accounts one for each of the following (SQL Server, SSIS, SSRS and SQL Agent).
I then created SSIS package on the utility server to loop through my other SQL Servers in the trusted domain (Server 2 & 3).
My OLE DB connection string looks like this Data Source=;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;
Then I scheduled a SQL Agent Job to run the package using the SQL Agent Account to run the SSIS package.
When I ran the package it didn't work as the SQL Agent domain account doesn't exist as a login on Server 2 or 3. So I went ahead and created a login for the SQL Agent account on each server.
Re-ran the package but still failed so I made a bad decision and gave the new logins sysadmin
server roles. The package ran successfully. I know this is just lazy work. The package only reference the system databases to gather information. So should I just create new user accounts in each of the system databases and grant the appropriate select persmission on the tables I'm using?!
Now I'm looking at this setup and I'm not happy with it. am I going about this the wrong way?
Any advice would be much appreciated.
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn