Solved

SQl Agent job fails--SSIS package looses password

Posted on 2016-09-27
6
46 Views
Last Modified: 2016-09-30
FTP task executes ok in BIDS.  Password for FTP server is hard coded in the FTP task .
When I scheduled this package as a job  in SSMS it fails  because the password blanks out . SQL agent errors out with The login request was denied . Any inputs how to keep the password  in FTP within SQL Agent?
0
Comment
Question by:Nana Hemaa
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 41818663
"When I scheduled this package as a job  in SSMS it fails " - where is the SSIS package? imported in SQL store or as a DTSX file in a folder? I believe you'll need to change PackageProtectionLevel in bids like described here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a1f455fb-c75b-4470-a862-d5d89e9a4da6/ssis-package-does-not-remember-password-ole-db-connection-adonet-connection?forum=sqlintegrationservices
0
 

Author Comment

by:Nana Hemaa
ID: 41818744
Saved SSIs on Local SQl server and as a file.  None works in SQL Agent.  I have already played with link above.  Any other inputs from other experts who has  encountered this issue before please?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41819029
Which account is used to run that package ?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Nana Hemaa
ID: 41819064
domain sql  agent service account used to execute all other SQl  jobs
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41819822
Can you please post the actual error from the SQL Job history?
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 41820086
Crap....this may be one of those "feature by design" and the save password option is only good for the duration of the BIDS session at least when ProtectionLevel=DontSaveSensitive

The KB below describes few ways to set password at runtime:

https://support.microsoft.com/en-us/kb/918760
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 42
Refresh Dev server with Production database 8 29
insert wont work in SQL 14 22
Update a text value in another table 10 40
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question