Best practice - SSIS package question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Best practice - SSIS package question

Best practice - SSIS package question

Rate Topic

Display Mode

Topic Options

Author

Message

Abu Dina

Posted Tuesday, October 16, 2012 4:49 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:44 AM
Points: 503, Visits: 2,219

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.

-----------------------------------
http://www.SQL4n00bs.com

Post #1373127

John Mitchell-245523

Posted Tuesday, October 16, 2012 4:56 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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

Abu Dina

Posted Tuesday, October 16, 2012 5:04 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:44 AM
Points: 503, Visits: 2,219

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.

-----------------------------------
http://www.SQL4n00bs.com

Post #1373138

John Mitchell-245523

Posted Tuesday, October 16, 2012 5:36 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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

Abu Dina

Posted Tuesday, October 16, 2012 5:45 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:44 AM
Points: 503, Visits: 2,219

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

-----------------------------------
http://www.SQL4n00bs.com

Post #1373166

John Mitchell-245523

Posted Tuesday, October 16, 2012 5:53 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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 »

Permissions