December 7, 2012 at 1:16 pm
Hello:
Here is my problem, Create a new project in BIDS, new package, go into Control Flow, Create an execute SQL Task, create a Connection manager to DB2, test the connection, works fine. In the SQL Task, I set up a CALL to a store Procedure in DB2 LUW. If I start the debug with an open BIDS, it works good.
Now I want to schedule as a SQL Server Agent job, Create a new job and in Type chose SQL Server Integration Services Package, package source: File system, then I specify where the package is.
Schedule to run and get the following error:
Error: 2012-12-07 14:15:40.60 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". 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: 2012-12-07 14:16:14.63 Code: 0xC0202009 Source: ONickname2 Connection manager "CPICFEDT.db2admin" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "IBM OLE DB Provider for DB2" Hresult: 0x80040E4D Description: " SQL30082N Security processing failed with reason "3" ("PASSWORD MISSING"). SQLSTATE=08001 ". End Error Error: 2012-12-07 14:16. The step failed.
Tried also as SSIS Package Store, and use the MSDB after I imported the package to the SSIS engine, set up the password, and still does not work..
Help.....
Thanks
Pablo
December 7, 2012 at 1:23 pm
You have different security rights to the DB2 components then the SQL Agent does. You need to find out what login the Agent is running under and change its access rights to the DB2 engine and whatever filepath the XML file is in (my guess is there's a call to it from the proc).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 10, 2012 at 6:57 am
Thank you Craig for your response.
What it puzzles me is that I can execute with no problems when I have it open the package in BIDS, it looks that it keeps the password on the connection namager, but when I set up as a SQL Server agent JOB, it looks that it does not carry the password to the job.
The error SQL30082N is a DB2 error that it is missing the password.
How come the password that I type and "save" in BIDS, it gets lost when I set up in the SQL Server Agent Job.
Please help....
Thanks
December 10, 2012 at 7:09 am
SQL Agent jobs run under a service account on the server, and if it uses the saved password together with that service account as credentials to DB2, then yes, it will likely have a problem. SQL Agent jobs are NOT goiing to remember your credentials. They use the service account that is set up for that purpose.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 7:35 am
Pablo Campanini-336244 (12/7/2012)
...Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". 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...
Actually from this error it sounds like the problem may be the ProtectionLevel of the SSIS Package. In the properties of the package in BIDS, check the ProtectionLevel. Here's an article describing what these values are:http://msdn.microsoft.com/en-us/library/ms141747(v=SQL.90).aspx
If you're using EncryptSensitiveWithPassword or EncryptAllWithPassword, there's a little trick to get the password into a SQL Agent job. When you create the SSIS step, you'll have to change tabs in the Job Step Properties dialog from Package to any other tab, then another small dialog box will popup propmpting for the package password, which should match what you put into BIDS when you set the ProtectionLevel of the SSIS Package. Hope this helps, I remember this process was clear as mud when I first started using SSIS.
December 10, 2012 at 3:28 pm
+1 Chris. Another option is to not store the connection information in the package and set it up as a configuration, so if it ever changes you merely need to change the configuration component and not redeploy the package with the new password(s). It's been a while since I've worked with DB2 and I've gotten far too used to everything being an NT login.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 12, 2012 at 7:09 am
Thank you all for your help. The suggestion from Chris worked fine....:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply