Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best practice - SSIS package question Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 4:49 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
Consider the following SQL Server setup:

Trusted Domain:

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
Post #1373127
Posted Tuesday, October 16, 2012 4:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
What error message did you get when it failed the second time (after you had added the SQL Server Agent account to the target servers)? What does the package do?

John
Post #1373129
Posted Tuesday, October 16, 2012 5:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
Thanks for the speedy response John.

The SQL Agent login doesn't have SELECT permissions on msdb or master tables. That's all it needs to do. Just read data from system tables.


---------------------------------------------------------


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
Post #1373138
Posted Tuesday, October 16, 2012 5:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
Grant SELECT permission on the objects it needs to read from, then. Depending on exactly what data you're trying to read, the VIEW DEFINITION permission might be appropriate.

John
Post #1373161
Posted Tuesday, October 16, 2012 5:45 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
Yes, I see what you mean. It's going to take more time to set these up but in terms of security then it's much better than granting the SQL Agent login sysadmin server role. That's just too much for what it needs to do on the server.

I actually have 26 servers (trusted domain and DMZ). It's going to take some time to set this up so the package can gather all the information required but as long as I know I'm on t right track then that's the main thing.

arrghhh.. just noticed my package creates a working table in master DB. Even though I run GRANT CREATE TABLE to SQLAgent the package still fails to create the table.... booohooo


---------------------------------------------------------


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
Post #1373166
Posted Tuesday, October 16, 2012 5:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
If you have a Central Management Server, you can run the same GRANT statement on all servers at the same time. Don't know whether it would work for the ones in the DMZ, but it would save you some work on the domain servers at least.

John
Post #1373174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse