MS SQL - Problem running SQL Server Agent Job via service account credentials

Question

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

Answer 1

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.