|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 8:56 AM
Points: 6,
Visits: 95
|
|
We are in the process of migrating from SQL Server 2000 to SQL Server 2005. In SQL 2000 we had non-sysadmins who created DTS jobs and were able to call those DTS jobs from within SQL Agent jobs without issue. In SQL 2005, we get the error message: "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account".
Now, I think I understand the "standard" way around this by using credentials and proxies. However, one of the things we need to avoid, in order to maintain our internal security compliance, is that users run jobs as themselves in all occasions. So when User1 runs the SSIS package that he created, that package can only access objects for which he has permission. By setting up a credential, say to a service account, and then a proxy uses that credential, when he runs his job step using the proxy, he is now running as a service account and not himself. That seems harmless enough until we have User2 who also needs to use a proxy and the permissions that his job requires are different that the User1's job. So if I grant those permissions to the service account, now, by default, User1 and User2 have permission to each other's objects through the proxy. In order to avoid that, I would need to create a new credential and proxy for each user in my database. If that is not correct, someone explain it to me, PLEASE!
All that being said, is there a way to simply allow Non-SysAdmins the ability to run DTS Execution job steps without the whole mess of proxies and credentials so that when the job step is executed, it is run as the user???
Thanks in advance for your help!
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 187,
Visits: 707
|
|
| I haven't found a way yet but would be interested if there is a solution.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Friday, January 29, 2010 1:51 PM
Points: 915,
Visits: 108
|
|
| I'm looking for an answer to this too. I guess I'll try Google...
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 187,
Visits: 707
|
|
| I'm pretty sure it's not possible.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 1,990,
Visits: 1,612
|
|
You need to set up proxy accounts for each user.
The SQL Server FineBuild Reference document has a detailed description on how to do this, the access rights given, and the advantages of this approach. You can get FineBuild by clicking the link below or direct from CodePlex.
Author: SQL Server FineBuild 1-click install and configuration of SQL Server 2005 and 2008.
1 December 2009: now over 8,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 8:56 AM
Points: 6,
Visits: 95
|
|
| Thanks for your responses! Ed, thank you for that fantastic reference. It would have been EXTREMELY helpful about 3 months ago! :) But it will be useful now also. I guess there is no way around it other than forcing the users to have the DBA's run their SSIS/SQL Agent jobs.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 187,
Visits: 707
|
|
Chris Conner (5/7/2009)
Thanks for your responses! Ed, thank you for that fantastic reference. It would have been EXTREMELY helpful about 3 months ago! :) But it will be useful now also. I guess there is no way around it other than forcing the users to have the DBA's run their SSIS/SQL Agent jobs.
I have a similar situation here. My take on it is once the job is created, it shouldn't even need edited. The developers can modify the SSIS package if they need to. They can even start the jobs when they are owned by sa. Even for sql jobs, we don't put the t-sql right in the job but just call a procedure so I still can't see a reason for a developer needing to edit the job itself. Everybody wants sa though.
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 722,
Visits: 414
|
|
this is a tricky topic. connecting to SSIS from SSMS is also another tricky scenario with regards to ssis security.
we like to store all ssis packages within msdb so they def. get backed up with the msdb DB backup, but it's hard for developers to deploy their packages to msdb without the proper rights to connect to the Integration Services. They could probably build the deployment utility and use that, but its a tricky situation, and i've simply added people to the admin group on the development system to get around it.
for all production packages, we have the developers hand them off to us for implementation and scheduling.
Steve
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, January 20, 2010 2:12 AM
Points: 95,
Visits: 475
|
|
Yes I have just hit this issue and am looking for a good way around it...
as for giving them admin we did not allow this - we simply added the users to have permissions on integration services... please my notes below on how to do this..
- Checked that "Enable Distributed COM on this computer" is enabled at both server and client computers via --> Component Services > Computers > My Computer Properties > Default Properties
- Added the failing account/group to the Distributed COM Users Local Group on the SSIS Server
--> Computer Management > System Tools > Local Users and Groups > Distributed COM users
- Granted under "Launch and Activation Permissions": Local Launch, Remote Launch, Local Activation, and Remote Activation to the account/group that was failing permissions in the DCOM package MsDtsServer
--> Component Services > Computers > My Computer > DCOM Config > MsDtsServer pkg > Properties > Security > Launch and Activation Permissions > Customize > Edit...
Grant Local Launch, Remote Lauch, Local Activation, Remote Activation to the account/group
- Granted under "Access Permissions" Local Access and Remote Access to the account/group that was failing permission in the DCOM package MsDtsServer
--> Component Services > Computers > My Computer > DCOM Config > MsDtsServer pkg > Properties > Security > Access Permissions > Customize > Edit...
Grant Local Access, Remote Access to the account/group that is failing
- Restarted SSIS service
- Restarted Client Workstation
This has solved the package store and deploy but now the dev are getting the 'Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account' - so guess proxies will have to be setup...?
Oraculum
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, February 04, 2010 9:05 AM
Points: 372,
Visits: 1,566
|
|
One way we have worked around this is have the job that runs the SSIS package check if a flag in a table is set, if so the run ssis package code is run, if not a print statement, if the SSIS package is run, have the flag reset. We have a trigger on the table(s) where the flag is set to log who inserted the record. The job can run every n number of minutes seconds.
Andrew
|
|
|
|
