October 14, 2010 at 11:21 pm
Hi,
I am getting one issue when one dot net application executing the SSIS package (MasterData_Import).
The ssis package owner is "sa". package source is "File System".Its running as "SQL Agent Service Account".
when dot net application executing the ssis package thorugh sql server agent,its failing.
application is executing the ssis package with user (ram), which is having server roles of public and sysadmin.
my sql server 2005 agent is running on Log on Built-in account as "NT AUTHORITY\NETWORK SERVICE".
here is the log below which is showing in the history of the ssis package.
10/14/2010 13:55:59,MasterData_Import,Error,0,sys1\SQL2005,MasterData_Import,(Job outcome),,The job failed. The Job was invoked by User ram. The last step to run was step 1 (MasterData_Import).,00:00:01,0,0,,,,0
10/14/2010 13:55:59,MasterData_Import,Error,1,sys1\SQL2005,MasterData_Import,MasterData_Import,,Executed as user: NT AUTHORITY\NETWORK SERVICE. ... Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:55:59 PM Error: 2010-10-14 13:55:59.96 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-10-14 13:56:00.39 Code: 0xC0202009 Source: MasterData_Import Connection manager "sys1\SQL2005.PROUTILITY_ML.ram" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'ram'.". End Error Error: 2010-10-14 13:56:00.39 Code: 0xC0. The step failed.,00:00:01,0,0,,,,0
can anybody please help me to resolve my issue and why I am getting this error.
October 15, 2010 at 10:52 am
You have been hit with the most common issue with Reporting Services. Reporting Services encrypt all passwords by default. Otherwise it won't store them. The default behavior is to use a key associated with the user creating the SSIS package to encrypt the password. That means any user other than the developer will get a blank password. That includes the user account of your SQL Agent.
Check this out for more information: http://msdn.microsoft.com/en-us/library/dd440760(SQL.100).aspx
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply