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 ?

Running SSIS Packages in Management Studio

We are using SQL Server 2005 Standard edition.We have the development studio installed on a developer’s workstation.The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.

The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server.I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.

When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.

I’m sure this is a permission based problem since it runs fine on my workstation.I tried logon onto the SQL Server with my id (I’m an admin to the server).I tried running the agent with my windows id.It looks like I’m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.

In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.

Do you use config files and do not save sensitive? This should take care of this issue.

There are quite a few posts about it in this forum

Philippe

|||The problem I was getting occurred whether I used a config file or not. I had the package set to EncryptSensitiveWithUserKey. I created another thread to get production deployment suggestions. We don't want our DBAs using BIDS to edit SQL connection manager settings when they migrate SSIS packages to production.|||

Then don't let them. Use configurations and the deployment wizard.

-Jamie

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric