KB2216489 - FIX: Error message when you try to run an SSIS package that contains a SQL Server Destination component in a Dataflow task

Symptoms

Consider the following scenario:

• You create a SQL Server 2008 Integration Services R2 (SSIS) package that has a SQL Server Destination component in a Dataflow task.

• You try to run this package on systems where User Account Control (UAC) is enabled (for example, in Windows Vista or in Windows 7) by using one of the following methods:

  • Business Intelligence Development Studio (BIDS)

  • SQL Server Management Studio (SSMS) Object Explorer

  • DTExec.exe

  • DTExecUI.exe

In this scenario, you may receive an error message that resembles one of the following:

Notes

• You do not encounter these errors if you run the package under the built-in Administrator account that is created during operating system installation. But you will receive this message for any other user, including those who are members of the Local Administrators group.

• The problem does not occur when you execute the same SSIS package as a SQL Server Agent job.

• The second error message is generated only after SQL 2008 Service Pack 2 (SP2) is installed.

Cause

This issue occurs on systems where UAC is enabled. When an application (such as SSIS) is started by an account that is a member of the Administrators group, it receives two security tokens: a low-permissions token and an elevated-permissions token. The elevated-permissions token is used only when the application is explicitly run under an administrator account by using the Run as Administrator option. By default, SSIS always uses the low-permissions token, and this causes a failure when SSIS connects to a SQL Server Destination component.

Note When you use an account that is not a member of local Administrators group, UAC does not appear.

Resolution

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This problem was first corrected in SQL Server 2008 R2 Service Pack 1 for SQL Server 2008 R2.

Workaround

To work around this problem, use one of the following methods:

• If you are running the package from SSMS, from BIDS, or from DTExecUI.exe, start those tools from the administrator account. To do this, click Start, point to All Programs, point to SQL Server 2005 or SQL Server 2008, right-click the tool that you are using, and then click Run as administrator. This starts the application by using the elevated permissions of the built in Administrator account, and the package executes successfully.
  
  Similarly if you are running the package by using DTExec.exe, start it from an elevated command prompt. To do this, click Start, click All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.
  
  Note If you do not log on to the computer as an administrator, you are prompted to provide the administrator account. In response, type the administrator user name and password in the User Account Control dialog box, and then, click OK.

• Replace the SQL Server Destination components in the Dataflow Tasks that are failing with OLE DB Destination components that point to the same SQL Server connection manager.

• Use an account that is not a member of the local Administrators group after you assign Create Global Objects permissions to that account.
  
  To do this, follow these steps:

  1. Click Start, point to Administrative Tools, and then click Local Security Policy.

  2. Expand Local Policies, and then click User Rights Assignment.

  3. In the details pane, double-click Create global objects.

  4. In the Local Security Policy Setting dialog box, click Add.

  5. In the Select Users or Group dialog box, click the user accounts that you want to add, click Add, and then click OK two times.