Solved

SQl Agent job fails--SSIS package looses password

Posted on 2016-09-27
6
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

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 29

Expert Comment

by:Pawan Kumar
ID: 41819029
Which account is used to run that package ?
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

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

Expert Comment

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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