SQL Server Performance

SSIS and Disaster Recovery Planning

Discussion in 'SQL Server 2005 Integration Services' started by DBADave, Jun 20, 2007.

  1. DBADave New Member

    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.

    Any thoughts on how to approach SSIS for DR?

    Dave
  2. satya Moderator


    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 & Moderator
    http://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.
  3. DBADave New Member

    Hi Satya,

    I'm referring to the permissions associated with someone who can create or maintain SSIS packages. Those permissions correspond to local server groups and Component Services settings.

    Dave
  4. satya Moderator

    THat is clear now, from SQL 2005 there are few fixed database roles are for SSIShttp://msdn2.microsoft.com/en-us/library/ms141053.aspx for more information.

    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 & Moderator
    http://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.
  5. DBADave New Member

    Thanks Satya. Sorry for the duplicate responses. My Blackberry was having problems yesterday.
  6. satya Moderator

    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 & Moderator
    http://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.
  7. DBADave New Member

  8. satya Moderator

    From that link see this
    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 & Moderator
    http://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.
  9. DBADave New Member

    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


    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


    Dave

Share This Page