SSIS and Disaster Recovery Planning

I was showing a DBA how to grant someone access to SSIS and realized I don't know how to account for SSIS permissions in a disaster recovery plan. Assigning access involves adding a user or domain group to Distributed COM Users. It also involves permissions being granted within Component Services - MsDtsServer. In a typical disaster recovery test I would install SQL Server and restore the databases from tape. In the case of SSIS I'm guessing the permissions must be manually recreated, unless it's possible to backup local groups and Component Services.

If you are going to schedule that package then grant the appropriate permissions for the SQL Server Agent service account so that the SQL Server Agent service account meets the security context requirement for the package.
See relevant information on this KBAhttp://support.microsoft.com/kb/912911 link.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Within our environment based on the work profiles I have designed the following list (also extracting few from BOL)

-Permissions to connect to the source and destination databases or file shares. In Integration Services, this requires server and database login rights.

-Permission to read data from the source database or file. In SQL Server 2005, this requires SELECT permissions on the source tables and views.

-Permissions to write data to the destination database or file. In SQL Server 2005, this requires INSERT permissions on the destination tables.

-With the SSIS package if you want to create a new destination database or table or file, permissions sufficient to create the new database or table or file. In SQL Server 2005, this requires CREATE DATABASE or CREATE TABLE permissions.

-If you want to save the package created by the wizard, permissions sufficient to write to the msdb database or to the file system. In Integration Services, this requires INSERT permissions on the msdb database.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hey no worries, I don't think it is a duplicated one.
SO do you think the above list is any good to you?

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

quote:
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.

I would like to work on that route as well.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I think those comments are additional things to consider, but do not necessarily apply to the remote server issue. In our case we are not making three hops, but two by going from a desktop directly to the database server containing Integration Services. I do recall our first attempt to use SSIS also involved the use of xp_cmdshell so I needed to configure a proxy account. Microsoft's documentation was incorrect so I called them for help. They created a KB article about the bad documentation and helped me with the "Access Denied" issue. I just assumed I always needed to follow these steps for every person needing to create an SSIS package. Is this not the case?

Here is the email I received from Microsoft.

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