Sql Server Agent

Hi,
I have SSIS package in sql server 2005, it  works fine, but in sql server agent gives me error
that SSiS Package has script task and FTP task, Thanks
Executed as user: t1\SYSTEM. ...on 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  4:14:58 PM  Error: 2011-10-04 16:14:58.49     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Property" 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: 2011-10-04 16:14:59.18     Code: 0xC001602A     Source: Members Connection manager "FTP Connection Manager"     Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed  .  End Error  Error: 2011-10-04 16:14:59.20     Code: 0xC002918F     Source: FTP M1XML FTP Task     Description: Unable to connect to FTP server using "FTP Connection Manager".  End Error  DTExec: The pa...  The package execution fa...  The step failed.
samprgAsked:
Who is Participating?
 
Simone BSenior E-Commerce AnalystCommented:
From MSDN, EncryptAllWithUserKey means "Encrypts the whole package by using a key based on the user profile. Only the same user using the same profile can load the package. The package is encrypted by using a key that is based on the user who created or exported the package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility."

SQL Agent is not you, and so it doesn't have your user information when it tries to run the job. Try using the protection level DontSaveSensitive, and add the configuration files. That way, SQL Agent always uses the connection strings you set within the config files to run the package.
0
 
Kent DyerIT Security Analyst SeniorCommented:
Here is where you get into the "sticky wicket" scenario.  If you had a 3rd party FTP setup, which I do know is available for SQL 2000 and you brought this forward to SQL 2005/2008, you will most likely run into issue.  The other issue is that if you are running into a Security policy  where you cannot use the XPCMD in SQL server (it is a vulnerability, by the way)..  I would check change controls and security to see what has gone in lately to address issues such as this.

HTH,

Kent
0
 
samprgAuthor Commented:
I created it in sql 2005, It works fine in BI
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Simone BSenior E-Commerce AnalystCommented:
If it runs fine in BIDS, but not in SQL Agent, then it could be the protection level in your package. Have a look at this link, it's very helpful:

http://msdn.microsoft.com/en-us/library/ms141747.aspx
0
 
samprgAuthor Commented:
Buttercup1:
I want to use "DontSaveSensitive" ,If I save the Password in variable , how can I make ftp connection  use a password variable
0
 
samprgAuthor Commented:
Which ProtectionLevel I need to use it, I need just the package work in sql agent
0
 
Simone BSenior E-Commerce AnalystCommented:
Yes, use Don't Save Sensitive, and deploy the package to SQL server. Then, go back to the package, right click and add whatever configurations are required, and save. Each configuration is saved in a separate file. You will need a configuration file for each connection that requires a login. It's a good idea to double check each file in Notepad to make sure the string is correct. Also you may need to type in the password in the config file.

Then in your Solution properties under Deployment Utility, make sure that Allow Configuration Changes is set to True. Then you won't have to rebuild the configurations every time you have to redeploy.
0
 
samprgAuthor Commented:
I did not create  configurations.
I import the package in sql integration,, I can run the package in sql Integration, but still sql agent gives me rror?!
I use protictionLeve :EncryptAllWithUserKey

Message
     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Property" 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: 2011-10-05 12:27:13.31     Code: 0xC001602A     Source: Members Connection manager "FTP Connection Manager"     Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed  .  End Error  Error: 2011-10-05 12:27:13.31     Code: 0xC002918F     Source: FTP  FTP Task     Description: Unable to connect to FTP server using "FTP Connection Manager".  End Error  DTExec: The p...  The package execution fa...  The step failed.



0
 
samprgAuthor Commented:
If administrator add the package in sql , will it be work?
0
 
samprgAuthor Commented:
Awesome,
 I need your answer for my last question please.
Thanks
0
 
Simone BSenior E-Commerce AnalystCommented:
Sorry for the delay, too many meetings today. Regardless of who deploys the package you should have the same result.
0
 
samprgAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.