Wednesday, March 21, 2012

Running SSIS/Maintenance packages using Proxies (SQL 2005)

I am trying to run SSIS and Maintenance Packages under a different username.
I have created the AD account, created a credential for that account, create
d
a proxy which uses that credential and given the principal access to the
proxy. However, when I try to run the package, it gives error "Unable to
start execution of step 1 (reason: Error authenticating proxy
Mydomain\Myaccount, system error: Logon failure: unknown user name or bad
password.). The step failed.".
In the sql log, I get this error "[298] SQLServer Error: 22046, Encrypti
on
error using CryptProtectData. [SQLSTATE 42000]"
I have even tried making the principle and the credential account sysadmins,
but it still gives this error. It seems the password for the credential is
stored using the database or server master key, which only the account
running the sql service has access to. I suspect since I am trying to run it
under a different account, it's not able to decrypt the password for the
credential.
The credential works fine when I use EXECUTE AS in sql backup statements, so
there's nothing wrong with the password.
Has anyone tried using a proxy/crendential to run SSIS packages ?PS: The AD account does have the 'Logon as a Batch Job' rights on the server
as per BOL.
"Pranil" wrote:

> I am trying to run SSIS and Maintenance Packages under a different usernam
e.
> I have created the AD account, created a credential for that account, crea
ted
> a proxy which uses that credential and given the principal access to the
> proxy. However, when I try to run the package, it gives error "Unable to
> start execution of step 1 (reason: Error authenticating proxy
> Mydomain\Myaccount, system error: Logon failure: unknown user name or bad
> password.). The step failed.".
> In the sql log, I get this error "[298] SQLServer Error: 22046, Encryp
tion
> error using CryptProtectData. [SQLSTATE 42000]"
> I have even tried making the principle and the credential account sysadmin
s,
> but it still gives this error. It seems the password for the credential is
> stored using the database or server master key, which only the account
> running the sql service has access to. I suspect since I am trying to run
it
> under a different account, it's not able to decrypt the password for the
> credential.
> The credential works fine when I use EXECUTE AS in sql backup statements,
so
> there's nothing wrong with the password.
> Has anyone tried using a proxy/crendential to run SSIS packages ?

No comments:

Post a Comment