October 16, 2012 at 4:49 am
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[/url]
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
October 16, 2012 at 4:56 am
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
October 16, 2012 at 5:04 am
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[/url]
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
October 16, 2012 at 5:36 am
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
October 16, 2012 at 5:45 am
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[/url]
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
October 16, 2012 at 5:53 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply