Access Denied error when running SQL Server Transfer Objects Task with a Proxy Account

  • Article

Should you try to configure an SSIS package which uses the Transfer Objects Task, and then execute this package through SQL Agent, when you choose to run the task as a proxy credential, as opposed to the normal SQL Agent user, you may encounter a failure. The typical full text of the error message from the SQL Agent history is shown here.

Executed as user: GRAHAMK20\SSIS_Proxy. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:55:19 PM Error: 2010-02-04 16:55:19.65 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-02-04 16:55:19.67 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-02-04 16:55:20.00 Code: 0xC002F325 Source: Transfer SQL Server Objects Task Transfer SQL Server Objects Task Description: Execution failed with the following error: "Access to the path 'C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo' is denied.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:55:19 PM Finished: 4:55:20 PM Elapsed: 0.562 seconds. The package execution failed. The step failed.

Depending on how you view this message, you could decide it as being a number of different causes, but the actual important point to note is the red highlighted section. This is the lowest level error and the actual cause of the failure, the other errors are thrown further up the stack. You can confirm this by running Process Monitor on the server where the package is executing. You’ll see failures here

- C:\Users\Default\AppData\Local\Microsoft\SQL Server (create file)
- C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo (create file)

However if you run the same package, but in the context of the SQL Agent account, you will notice that the same create file runs on a different directory:

- C:\Users\<sqlagentuserid>\AppData\Local\Microsoft\SQL Server\Smo

The package requires a temporary location to process some files when it prepared to run and there is a bug in the way the proxy account credential decides where it should place these files. This has been passed to the product group and should be fixed in SQL 11.

The reason that it is not being fixed immediately is that there is a relatively simple workaround which is to manually create the directory structure in the default user profile directory, and grant write modify permissions to the user or group which represents the proxy credential account. So in my specific example I logged on with an adminstrator account to the server, created the following directory

C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo

and then granted the users group (of which my proxy credential account is a member of) the following permissions:

Modify, Write, Read & Execute

The package will then execute successfully under the proxy credential. This problem occurs on both SQL 2005 and SQL 2008.