We help IT Professionals succeed at work.

Failing to run SSIS Package from Job, running as Proxy

4,033 Views
Last Modified: 2013-11-10
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
Comment
Watch Question

Commented:
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?

PeterFrbData anslyst, tableau visualization developer, vb certified

Author

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

Molly FaganApplications Team Supervisor

Commented:
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).
PeterFrbData anslyst, tableau visualization developer, vb certified

Author

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
Data anslyst, tableau visualization developer, vb certified
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
PeterFrbData anslyst, tableau visualization developer, vb certified

Author

Commented:
I finally happened on a solution.  I got lucky, pure and simple.