Re: more detail on issue

Hi Matt,

Sorry to bother you but I still couldn't make my job run. I went to SSIS
package, on the package properties i selected 'EncryptallwithPassword', gave
a password 'pumba123'. I saved the package, build the project and deployed
the package again. i created a job with 'CMD Exec'
as

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS
"\MSDB\package1" /SERVER "s-us01-sqldev01" /DECRYPT "pumba123" /MAXCONCURRENT
" -1 " /CHECKPOINTING OFF /REPORTING V


I get the following error as before.

Executed as user: 'Domainname'\SQLAccount. ... 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
1:49:32 PM Error: 2006-05-02 13:49:32.29 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: 2006-05-02 13:49:32.30 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
Info: 2006-05-02 13:49:32.63 Code: 0x4004300A Source: Data Flow Task
DTS.Pipeline Descr... Process Exit Code 1. The step failed.

I have created the job and the package with my domain account but execution
of job is from server/sqlaccount. IS that the problem. Not sure where i am
going wrong. Any help is really appreciated.

Thank you
SP


"Matt Yeager" wrote:

It isn't that it is "hard". It's a matter of information in connection
strings not being visible to the SQL Agent user. This was the solution that
Microsoft gave me. There may be other solutions, but the Microsoft support
technican I spoke with gave me this as the solution. This area of SSIS is
extremely undocumented. I'm sure in time we'll find better solutions, but
this appears to be the fix for now.


"SP" <SP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A48246D1-9EF5-4059-95C3-14AD35F840EB@xxxxxxxxxxxxxxxx
Hi Matt,

I was just going put the same question where i found this discussion. I am
facing the same as CP-Buildog. Is it the only way to get around with this
problem. Then for each package we have to create a diffrent passoword. How
do
we remeber so many passowrords for which we have to create password
repository. Is it that complicated just to fire a SSIS package from sql
agent.

Thanks in advance
Swayam


"Matt Yeager" wrote:

I posted this in another thread, but this looks like the same issue so
I'm
copying the message here:

I encountered the same problem, even on the same server upon deployment.
I ended up contacting Microsoft and opening a support case. After a
couple
of hours on the phone, we found that if the SSIS Package's Security
setting
"ProtectionLevel" was set to EncryptAllWithUserKey or
EncryptSensativeWithUserKey that the passwords would actually be lost.
This
has to do with the fact that the SQL Server Agent process on your server
is
running as a different user and cannot validate the user key basically.
What
I ended up having to do is switch the Security ProtectionLevel to use
EncryptAllWithPassword or EncryptSensativeWithPassword and specify a
password for the package. I then re-deployed to SQL.

How I scheduled the Job also had to change. I could no longer specify my
package as a SSIS Step in a Job. I had to make my Job execute an
"Operating
System (CmdExec)". The command line was :

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS
"\MSDB\YOURPACKAGEHERE" /SERVER Q /DECRYPT YOURPASSWORDHERE
/MAXCONCURRENT
" -1 " /CHECKPOINTING OFF /REPORTING V

It seems like very much a work-around, but that's roughly the way I was
told
to keep the protected passwords. My support case person spoke with the
engineers and that was the desired result evidently. They are working on
documenting the Security Levels more though, as this seems to be coming
up a
lot. I honestly wouldn't be suprised if something in Security levels
changed
in SP2.


Hope this helps.

-Matt Yeager




"cp-bulldog" <cpbulldog@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6398C4B9-74A5-4258-924B-C0183B9DA201@xxxxxxxxxxxxxxxx
probably should have put all this info in the first post. we are using
SQL
2005. the sql job owner is set to my account right now for testing
which
is a
domain admin account. what is interesting is that although the job
owner
is
my account when the SQL agent tries to run it is tries to use the
server\SYSTEM account.

is this a known issue with a work around?

"cp-bulldog" wrote:
i have a dtsx package that runs fine from the the bus intelligence
studio
and
runs fine from within SQL environment when run manually. however,
when i
try
to shedule it via SQL Server Agent it fails. looks like it is trying
to
run
it as server\SYSTEM which is probably why it is failing.

any ideas on this?






.

Relevant Pages

  • Re: Cannot Schedule a SSIS Job
    ... Try also to add a job with SSIS step through script. ... it loads package from the file system, ... Dejan Sarka, SQL Server MVP ... This message does not imply endorsement from Solid Quality Learning, ...
    (microsoft.public.sqlserver.setup)
  • Re: DTS to SSIS lost functionality
    ... Try this with both a wizard in DTS and a wizard in SSIS: ... Crate a package that exports data from a SQL table and dumps into an Excel ...
    (microsoft.public.sqlserver.dts)
  • Re: Editing an SSIS package created by Import/Export Wizard
    ... SQL 2000 and migrated to a SSIS object in SQL 2005 or did you simply ... migrate it to a legacy DTS package within SQL 2005? ... I created the SSIS package using the BIDS ...
    (microsoft.public.sqlserver)
  • Re: Cannot Schedule a SSIS Job
    ... Could you pherhaps be more specific on how i use your script since I am not ... Should I try and run the package form a SQL Job using ... Try also to add a job with SSIS step through script. ...
    (microsoft.public.sqlserver.setup)
  • Re: more detail on issue
    ... then have SQL and SQL Agent running as an ... When you installed the package to your SQL ... Server, did it prompt you for the password you packaged with? ... strings not being visible to the SQL Agent user. ...
    (microsoft.public.sqlserver.dts)