Solved

SQl Agent job fails--SSIS package looses password

Posted on 2016-09-27
6
53 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

839 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