SSIS and Disaster Recovery Planning in multi-user environment
I have had an interesting discussion in SSP World about how to grant access & privileges to handle SSIS packages in a high availability and Disaster Recovery scenario. As you may be aware that privilege involves having access to Distributed COM components as well including MsDtsServer.
Few points you need to consider before proceeding in granting access to the users, within a distributed environment the general practice would be scheduling the SSIS packages in order to avoid any sort manual intervention. So if you are going to schedule that package then grant the appropriate permissions for that SQL Server Agent service account on that SQL instance to meet the security context requirement for the package, KBA912911 speaks about it more.
Interestingly SQL Server 2005 has got introduced new fixed database roles that are specific to SSIS, review MS141053 MSDN link in this case, I'm sure after reading this you will be able to resolve most of the access issues within your environment for SSIS. IMHO I can see this is one of the best practices too. Also refer to the notes below when you need additional privileges to grant for a user:
When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.
.
.
.
.
SQL Server 2005 Integration Services (SSIS) does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot pass your credentials on from the second computer to the third computer on which SQL Server is running.
The discussion on SSP has been resolved when the following notes has been referred in connection to AA337083 MSDN link:
PROBLEM:
Unable to connect to Integration Services from Remote Clients after applying SP1 with a Non-Admin account:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS
CAUSE:
Missing permissions on DCOM.
RESOLUTION:
- Checked that "Enable Distributed COM on this computer" is enabled at both server and client computers
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