Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

There are 5 steps in this job. First job is an SSIS Package store, second to fifth are file system jobs. We configured all jobs to use Windows Authentication. Under Run As, we specified a user account which was created under Security>Credentials and SQL Server Agent>Proxies>SSIS Package execution. The job runs without any problems with this user account. We then proceeded to configure the job to use a service account instead. Service account was specified under Security>Credentials and SQL Server Agent>Proxies>SSIS Package Execution. The job fails with this error.

Executed as user: domain\serviceaccount. ....00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:37:57 PM Error: 2010-03-09 15:37:57.95 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "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 error. Verify that the correct key is available. End Error Error: 2010-03-09 15:38:01.19 Code: 0xC0047062 Source: Get CONT_VIEW_LADDER in latest 45days OracleFMDatabase [1] Description: System.Data.OracleClient.OracleException: ORA-01005: null password given; logon denied at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boo... The package execution fa... The step failed.

Based on some research, I then go into MS Visual Studio and Open the project. I change the property of the package security from "EncryptSensitiveWithUserKey" to "DontSaveSensitive" but i still get the above error. I am new to this so any help will be very much appreciated. Thanks in advance

share|improve this question

1 Answer 1

up vote 1 down vote accepted

The problem is related to the encryption warning but that is not the cause of the failure. When you encrypt with a user key then only that user can decrypt the password in the connection string, otherwise the connection string will have no password. When you don't save sensitive data the password will not be stored in the connection string so you need to set up an SSIS configuration to supply the proper connection string. Open the package and select SSIS, Package Configurations. In the next screen you will select new and then create an XML based configuration file. When asked which properties you want to configure select the connectionstring property of your oracle connection manager and create the dtsConfig file somewhere on your file system. After you create the file and the configuration you can remove it since you may not want the package to look in the same path for the file.

Edit the dtsConfig file and add the password to the connectionstring and then save the file. Put the file where you want it to live and then go into thte properties of the job step that runs the SSIS package. Select the configurations tab and then browse to the dtsConfig file. This should apply the configuration at runtime and the error about the null password should go away and be be replaced by either a different error or it will work.

share|improve this answer
    
Jason, thanks for your reply. It solved our problem. Thanks for putting down your answer in a way that beginners would understand! –  molecule Mar 10 '10 at 23:58
    
np, thanks for the answer! –  Jason Cumberland Mar 11 '10 at 18:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.