June 9, 2008 at 1:44 pm
I'm new to SSIS, but have numerous packages that are SQL2000 based. I plan to rewrite them as dtsx and that I've begun. I have successfully built a simple package and tested it manually and it runs fine. However, I have been unable to schedule the job as part of the SQL Agent process receiving the following error (I'm at a loss):
PM Error: 2008-06-09 15:36:17.74 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: 2008-06-09 15:36:18.07 Code: 0xC0202009 Source: ESD_EID_Build Connection manager "IWANT10.Work_InSync.sa" 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 Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2008-06-09 15:36:18.19 Code: 0xC00291EC Source: EID Build Execute SQ. The step failed.
June 9, 2008 at 3:54 pm
You probably saved the package with the default protection level EncryptAllWithUserKey. That means that only the user who created the package can load and execute the package. When you schedule a package in a SQL Agent job, the account the starts SQL Agent is trying to load and execute it.
You don't say if you deployed the package to SQL Server or to the file system. If it's in SQL Server, you can set the protection level to ServerStorage and use the msdb database roles. If you want save it as a file, you should set the protection level to EncryptAllWithUserKey or EncryptSensitiveWithUserKey, which allows you to set a package password that can be entered when the package is executed. It works like the owner password in SQL 2000 DTS packages.
See BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d4b073c4-4238-41fc-a258-4e114216e185.htm
Greg
June 10, 2008 at 12:37 am
Hi,
If you set password for your package then you have to set appropriate package encryption level as it has been recommended in previous post.
Sani.
June 16, 2008 at 8:35 pm
Thanks..... found that using Windows Authentication resolved the situation enough that I can get the packages scheduled... not sure where things were awry, but that can wait for another day.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply