Failing to run SSIS Package from Job, running as Proxy

I have been having trouble running a simple SSIS package using a Proxy account.  The package always works when I run it from within Visual Studio but consistently fails when I run it from SSMS as a job step using a proxy.  

I have an SSMS job with a single step: to run the SSIS package.  In the "Run as:" dropdown, the package runs successfully when I choose the "SQL Server Agent Services Account", but not as "TestProxy", which I created.  I also created a credential, called "Test_Credential", and gave it my own account's identify: a SysAdmin role.  I assigned "Test_Credential" as the credential name for "TestProxy".  For TestProxy's Principals, I added everything there was to add in the three available categories: SQL Login, msdb role, and Server role.  In short, in spite of my having given the proxy every privilege I know to give, the job fails.  And switching back and forth, between running as"SQL Server Agent Services Account" and running as "TestProxy", I proved that something in the proxy is causing the failure.  

I have a paying client for whom proxies may wind up playing a role; and I'd like to figure this out before meeting with my client again.  Your feedback, as always, is most welcome.

~Peter Ferber
PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thekneeCommented:
Hi

Is your package read file on the disk, if so you need to make sure your using a windows user who have read or write to this file / folder.

can you provide the error message you have for this sql JOB?

0
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
A most excellent suggestion.  The error, shown below, is, I must say, a great deal more specific and pointed than the more generic messages I've received in the past.  (The messages have been so generic and indecipherable in the past, I hadn't even bothered to look this time.)  

To answer your question, I have tried running this three ways: using SQL Server, File System, and SSIS Package Store as my Package Source.  In this particular case, the package source is SQL Server.  I ran the SSISDeploymentManifest file and so it was available in the MSDB directory within Integration Services.  I have not done as meticulous a study of the Package Source as I have the Proxy question.  What are the implications of each when regarding proxies?  And, with the information in hand, shown below, what's do suggest I try next?

Best, ~Peter Ferber

Unable to start execution of step 1 (reason: Error authenticating proxy CUSTOMIZE\Peter, system error: Logon failure: unknown user name or bad password.).  The step failed.

Open in new window

0
Molly FaganApplications Team SupervisorCommented:
Is your SSIS package trying to access a file on the server or creating a file on the server?  If so, make sure the folder where the file is located has the correct permissions (that's what theknee was asking/suggesting).

Also, I just ran into this recently, make sure the account on your SQL Server isn't requiring a password change.  I just had that happen on a job that was running fine for months--all of a sudden it failed and here it was because someone changed it to enforce password rules (there's a checkbox--I unchecked it and it ran fine).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
The package migrates data from a file in Excel to a table in SQL Server.  I've checked the folder where the Excel file resides, and I just took the liberty of granting Customize\Peter full access to the folder (see image below).  I hadn't done this before, but having done so makes no difference when using a job to run the package.

In the connection manager within Visual Studio, the destination database uses Windows authentication to make its connection.  (I believe that means that the system provides a password instead of the user having to do so.)

Where, exactly, is the checkbox regarding passwords changes?  Is it in the SSMS job step?  In the SSIS OLE DB Destination Editor?  In the Server properties?  Or somewhere else?  (Don't hesitate to employ a screenshot to enhance clarity.)

I'm beginning to appreciate the number of moving parts this machinery has. Oy!

Best, ~Peter Ferber

Adding-Directory-Permissions.bmp
0
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
I just got it to work, although I'm not positive why!

The first change I made was to change the authentication on the job step to SQL Server Authentication, and I then typed in the password associated with that account.  The password took, but I got the error shown in the code block below.  Then I changed it back to Windows Authentication; and, by gosh, it worked!  I've a hunch that chaning the authentication, and then changing it back, somehow reset the setting, allowing the process to work!  Hallelujah!

I hope this helps someone else with the same problem.  Sometimes, even when everything has been set correctly, a shakeup is required to reallign all the connections.

~Peter Ferber

...Could not load package "<PackageName>" because of error 0xC0014062.  Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4d (Login failed for user 'CUSTOMIZE\Peter'.)...

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
I finally happened on a solution.  I got lucky, pure and simple.
0
Jason Yousef, MSSr. BI  DeveloperCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.