Re: more detail on issue

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Matt,

Just a quick question. Would you mind eloborating little more what are the
exact step you followed to get around with this problem. Just being very new
to SSIS, not getting where to change the security protection level.
Thank you very much for your help.
SP

"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

  • [NEWS] Xpede Found to Contain Multiple Vulnerabilities
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Intellisol Xpede ... anyone with a valid Xpede user account to issue requests to the Xpede's ... name used by Xpede to perform all its SQL queries. ...
    (Securiteam)
  • Re: ASP.NET Process Identity???
    ... In the application I not need/want to create user accounts into SQL Server. ... To control the security I have created a personalized security system. ... you can switch back to normal ASPNET machine account for the ... >> Public Class Personificacion ...
    (microsoft.public.dotnet.security)
  • Re: Windows vs SQL
    ... I would also add that with the sql security, ... account is a "known" entity in that a hacker knows that it exists and there ... >>> im always hearing that ms recommends trusted security ...
    (microsoft.public.sqlserver.security)
  • Re: How to use EFS to encrypt SQL DB file
    ... You want to make sure that SQL is starting here with an ... account that has the right to decrypt the mdf file. ... For information about the Microsoft Strategic Technology ... Protection Program and to order your FREE Security Tool Kit, ...
    (microsoft.public.sqlserver.security)
  • Re: Microsoft Informational Alert
    ... > PSS Security Response Team Alert - SQL Security Recommendations ... > PRODUCTS AFFECTED: SQL Server ... Secure your SA login account with a non-NULL password. ...
    (microsoft.public.security)