Run a ssis package on SQL Server 2005 Error

[Jerry C]

I am getting Error.

Non-SysAdmins have been denied permission to run DTS Execution job steps
without a proxy account. The step failed.

How can I fix this.

Thank you

[Jerry C]

OK I set the job owner as a administrator of the machine and this error went
away now I have this error in the server log when I try to run the job.

Date 11/2/2006 7:48:13 AM
Log SQL Server (Current - 11/2/2006 7:48:00 AM)

Source Logon

Message
Login failed for user 'cdaccess'. [CLIENT: <local machine>]

cdaccess is the SQL server account that I am using in the connection string.
This ssis package runs in the Business Intelengence Developer and when put
in a ssis package it also runs. When I schedule a job with this package the
error occurs.
I am sure that this user is OK with SQL server since I also run a
application with it in the connection string. It only has a problem when used
in a ssis package and the package is run as a job in SQL server Agent.

Thank you
--
Jerry

Post by Jerry C
I am getting Error.
Non-SysAdmins have been denied permission to run DTS Execution job steps
without a proxy account. The step failed.
How can I fix this.
Thank you
--
Jerry

[Charles Wang[MSFT]]

Hi Jerry,
My understanding of your issue is that:
Your SSIS package failed to run from a SQL Server Agent job step, but it
could run from Development Studio. The error was:
Login failed for user 'cdaccess'. [CLIENT: <local machine>]
If I have misunderstood, please let me know.

This is most likely a known issue in SQL Server 2005, and a KB article has
been released for this issue. There are five approaches available to fix
this issue:
Method 1: Use a SQL Server Agent proxy account
Create a SQL Server Agent proxy account. This proxy account must use a
credential that lets SQL Server Agent run the job as the account that
created the package or as an account that has the required permissions.
This method works to decrypt secrets and satisfies the key requirements by
user. However, this method may have limited success because the SSIS
package user keys involve the current user and the current computer.
Therefore, if you move the package to another computer, this method may
still fail, even if the job step uses the correct proxy account.


Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Change the SSIS Package ProtectionLevel property to ServerStorage. This
setting stores the package in a SQL Server database and allows access
control through SQL Server database roles.


Method 3: Set the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword
Change the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword. This setting uses a password for encryption.
You can then modify the SQL Server Agent job step command line to include
this password.

Method 4: Use SSIS Package configuration files
Use SSIS Package configuration files to store sensitive information, and
then store these configuration files in a secured folder. You can then
change the ProtectionLevel property to DontSaveSensitive so that the
package is not encrypted and does not try to save secrets to the package.
When you run the SSIS package, the required information is loaded from the
configuration file. Make sure that the configuration files are adequately
protected if they contain sensitive information.

Method 5: Create a package template
For a long-term resolution, create a package template that uses a
protection level that differs from the default setting. This problem will
not occur in future packages.

For more detailed information, please refer to this article for the
resolution:
An SSIS package does not run when you call the SSIS package from a SQL
Server Agent job step
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

If this issue persists, I recommend that you describe me your process
detailed so that I can reproduce your issue for further research.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Partner Support

PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!

Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Jerry C]

Charles,

Thank you for the reply.

I tried fix number 1 and in the proxi wizard there are no credentials so I
could use some how-to on that.

on fix number2 I get this error in BID.


TITLE: Microsoft Visual Studio
------------------------------

Failure saving package.

------------------------------
ADDITIONAL INFORMATION:

Failed to apply package protection with error 0xC0014061 "The protection
level, ServerStorage, cannot be used when saving to this destination. The
system could not verify that the destination supports secure storage
capability.". This error occurs when saving to Xml.
(Triveni)

------------------------------

Failed to apply package protection with error 0xC0014061 "The protection
level, ServerStorage, cannot be used when saving to this destination. The
system could not verify that the destination supports secure storage
capability.". This error occurs when saving to Xml.
(Triveni)

------------------------------
BUTTONS:

OK
------------------------------
--
So fix number 2 has problems. I ran out of time to try the other three. If
there are any how-to to implement these it would help. I will try the rest of
the fixes as soon as I can.

Thank you,
Jerry

Post by Charles Wang[MSFT]
Hi Jerry,
Your SSIS package failed to run from a SQL Server Agent job step, but it
Login failed for user 'cdaccess'. [CLIENT: <local machine>]
If I have misunderstood, please let me know.
This is most likely a known issue in SQL Server 2005, and a KB article has
been released for this issue. There are five approaches available to fix
Method 1: Use a SQL Server Agent proxy account
Create a SQL Server Agent proxy account. This proxy account must use a
credential that lets SQL Server Agent run the job as the account that
created the package or as an account that has the required permissions.
This method works to decrypt secrets and satisfies the key requirements by
user. However, this method may have limited success because the SSIS
package user keys involve the current user and the current computer.
Therefore, if you move the package to another computer, this method may
still fail, even if the job step uses the correct proxy account.
Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Change the SSIS Package ProtectionLevel property to ServerStorage. This
setting stores the package in a SQL Server database and allows access
control through SQL Server database roles.
Method 3: Set the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword
Change the SSIS Package ProtectionLevel property to
EncryptSensitiveWithPassword. This setting uses a password for encryption.
You can then modify the SQL Server Agent job step command line to include
this password.
Method 4: Use SSIS Package configuration files
Use SSIS Package configuration files to store sensitive information, and
then store these configuration files in a secured folder. You can then
change the ProtectionLevel property to DontSaveSensitive so that the
package is not encrypted and does not try to save secrets to the package.
When you run the SSIS package, the required information is loaded from the
configuration file. Make sure that the configuration files are adequately
protected if they contain sensitive information.
Method 5: Create a package template
For a long-term resolution, create a package template that uses a
protection level that differs from the default setting. This problem will
not occur in future packages.
For more detailed information, please refer to this article for the
An SSIS package does not run when you call the SSIS package from a SQL
Server Agent job step
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760
If this issue persists, I recommend that you describe me your process
detailed so that I can reproduce your issue for further research.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Charles Wang[MSFT]]

Hi Jerry,
I am sorry for not bringing you more detailed information in my first
reply. Unfortunately I could find very few how-to documents regarding these
methods.
I will try my best to assist you on any issue that you encountered.

For the first method, a simple way you can try is that you can assign the
SQL Agent service account with a domain user account which is a member of
local administrators group on the related servers in your DTS package;
however this requires your SQL Servers are under mixed authentication mode.
If your Windows operating systems are not in a domain, please ensure that
the logon account is created as a local administrator on the all related
servers with the same name and same password (for remote access other
computers with NTLM authentication).
You can change the SQL Agent Service logon account via the following steps:
Click Start, click Run..., enter services.msc, click OK, right click the
SQLSERVERAGENT in the service list, click Properties, switch to the Log On
tab and specify the "This account".
This method should work when you run this pakckage through a SQL job.
If you want to use credential, first you should create a credential via
CREATE CREDENTIAL and then use sp_add_proxy to add a proxy. You may refer
to:
How to: Create a Proxy (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms190698.aspx

For the error that you encountered when used the second method, it is most
likely caused by permission. Please assign your database login account with
the permissions: db_dtsadmin, db_dtsltduser, and db_dtsoperator.
If that does not work, please check if the current Windows logon account is
a member of local administrators group. If not, please add it to the local
administrators group and run the package again.
When you run the package through a SQL Server Agent job step, the default
account is the SQL Server Agent Service account. So if you run the package
through a job, please also check if the SQL Agent service start account is
a local administrator.

Please try the suggestions and let me know the result. I will be more than
happy to be of assistance.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

[Charles Wang[MSFT]]

Hi Jerry,
How about this issue? Could you please let me know the issue status?
If you need further research on this issue, please post back here at your
convenience. I will be more that happy to be of assistance.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

[MK]

I am passing some records read from flat file to script component and output
it after making some changes. I am missing the last record because I am not
able to identify the last record. The row.Endofrowset is never becoming true.
Can anyone provide the code to output the records along with the last record
from script component
--
Thanks
MK

Post by Jerry C
I am getting Error.
Non-SysAdmins have been denied permission to run DTS Execution job steps
without a proxy account. The step failed.
How can I fix this.
Thank you
--
Jerry