0

I made a dtsx package that moves data from one database to another. When I run it in BIDS it runs fine. When I run it from the command line as me it runs fine. When I run it at the command line as the service account I created for it, I get an error that the source DB's local sql server user\password is incorrect. Obviously that can't be true, since the username and password for the source DB's local user is embedded in the project.

I tried giving domain/service account DB admin permissions, as well as full server admin permissions. That didn't do anything. I also tried giving the local account LD_Transfer001 more permissions, but that also didn't do anything. The only way I've been able to get this to work is by running it as my domain admin account at the command line. I have also verified the folder where the packages live has the correct permissions.

Running as Domain/service:

SQL Log: 

Login failed for user 'LD_Transfer001'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

Command line error message: 

Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:47:57 AM Error: 2014-10-15 09:47:57.74 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0 x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic err or. Verify that the correct key is available. End Error Error: 2014-10-15 09:47:57.76 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0 x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic err or. Verify that the correct key is available. End Error Progress: 2014-10-15 09:47:57.88 Source: Data Flow Task Validating: 0% complete End Progress Error: 2014-10-15 09:47:57.92 Code: 0xC0202009 Source: FranchiseeInfo Connection manager "LDHQSQL.001" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0 " Hresult: 0x80040E4D Description: "Login failed for user 'LD_Transfer001'.". End Error Error: 2014-10-15 09:47:57.92 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG ER. The AcquireConnection method call to the connection manager "LDHQSQL.001" f ailed with error code 0xC0202009. There may be error messages posted before thi s with more information on why the AcquireConnection method call failed. End Error Error: 2014-10-15 09:47:57.92 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned err or code 0xC020801C. End Error Progress: 2014-10-15 09:47:57.93 Source: Data Flow Task Validating: 50% complete End Progress Error: 2014-10-15 09:47:57.93 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-10-15 09:47:57.93 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:47:57 AM Finished: 9:47:57 AM Elapsed: 0.266 seconds

Running as me[open command prompt normally No command line error message

Login succeeded for user 'LD_Transfer001'. Connection made using SQL Server authentication. [CLIENT: <local machine>]
Improve this question
7
  • You're using a config file, aren't you? Commented Oct 15, 2014 at 14:05
  • I don't think so. All I have is the dtsx package. Commented Oct 15, 2014 at 14:08
  • What's the value of "ProtectionLevel" in the package properties? Commented Oct 15, 2014 at 14:13
  • EncryptSensitiveWithUserKey Commented Oct 15, 2014 at 14:20
  • Change to DontSaveSensitive and retry! Commented Oct 15, 2014 at 14:20

1 Answer 1

Reset to default
1

Sensitive information (i.e. the password) are encrypted in the package. The encryption key generated is from the user account of the machine the package was developed.

Therefore when running via service, the decryption key is not available and the password cannot be read and used.

There are a couple of options available to resolve:

  1. Use Windows Authentication in your connections in the package.

  2. Use a configuration file to set the ConnectionString. Check the connection string in the config file has a plaintext password.

  3. Set the ProtectionLevel property of the package to either EncryptSensitiveWithPassword or EncryptAllWithPassword and provide the password to DTSExec.

Method 1 is the simplest, but not always appropriate.

Method 2 is a nice workaround, but leaves the password in plaintext. I tend to opt for this method as the location for our DTS packages is secure and we occasionally have to reconfigure stuff. I like config files!

Method 3 is a sound solution too. Have a read here for more information: http://msdn.microsoft.com/en-us/library/ms138023.aspx. You need to pass the password using the /decrypt option of dtsexec

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.