.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Errors when using Proxy/Credentials to execute SQL Agent jobs that access SSIS packages

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Running SQL Server 2008, on Windows Server 2008.

I'm having a problem trying to execute a job.  The job has executed successfully when I configure the SQL Agent account (domain account with sysadmin permissions in the instance) as the owner/executor of the job.  However, I want to tighten down security so I'm trying to use a proxy/credential.

Here are the steps I've followed:

1) create a non-privileged SQL account called "SQLJobOwner" that owns the job.

2) create a stored credential (domain account) that has an account on the local machine where the instance resides, and has "log on as batch" permissions.

3) created a login to the instance for the domain account that is stored as a credential, and granted sysadmin permissions to the account.

3) create a new proxy called "SSISProxy", and granted access to the SSIS subsystem.

4) Added job owner "SQLJobOwner" as a principal in the SSISProxy configuration window.

5) changed the job step that executes the SSIS package to "Run as" the "SSISProxy".


When I execute the job, the job fails with the following error:

Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).  The step failed.

In addition, I get

View Complete Post

More Related Resource Links

SSIS Execute Process Task Give "Access is Denied" error but it is not related to access control.

Hello All - I have a very weired SSIS problem. I am using BIDS in two machines - Windows 7 Professional Edition x64 bit (development workstation) and Windows 2003 Standard Edition x32 bit SP2 (server) . The visual studio version on both the machines are Version 2.0.50727 SP2. I found that my package's ExecuteProcessTask fails with "Access is denied" error in Windows2003 but runs ok in Windows 7 dev box. I then tried to debug the package in the Windows 2003 environment itself. The package failed in debug mode too with the same message. After looking for a while I found that when I shorten the Argument length of the failing ExecuteProcessTask the task did seem to work. So in Windows2003 environment if my argument exceeds 1846 characters the ExecuteProcessTask fails with message "Access is Denied". If the argument string is less than 1846 it works ok. In Windows 7 environment there is no problem at all. It does not seem like access problem but I may be wrong. Has anyone seen this? I will appreciate any reply. Thanks, Niben  

how do i do a minimal client tool install to execute SSIS packages


I am trying to migrate our processing from command line based scripts and foxpro to SQL so I need to run the SSIS packages using dtexec. I copied the dtexec file and a few dll's that are missing to our production servers but i cant execute the packages. I dont want to install the full client tools (particularly managment/business inteligence studio) on our production servers due to the overhead and limited system disk space.

Can somebody tell me what the minimum install would be so I would be able to run SSIS packages using the dtexec or dtexecui tools? I would also like to install some of the other command line client tools like osql etc.

SSIS Jobs VS SQL Server Job Agent


Which one of them is better to just run a set of Strored Procedures on a daily basis.

Can I have some Adv and Dis advantages. Please.


SSIS packages run slow under Execute Package Utility


I have developed a dtsx package to load data from xml load table into a dozen of flat tables. I found the SSIS is running slow (almost 10 times slower) under Execute Package Utility than running under visual studio IDE.

Does anyone have same experience?


SSIS on Cluster also 2005 packages and 2008 db connections and jobs?


Hi, multipart questions.

We have a SQL 2008 Cluster where we do not want to install SSIS.  We have a SQL 2005 SSIS instance on another server  for packages.

here goes

1. Can we configure jobs on the SQL 2008 custer to point to the 2005 SSIS instance? and do we need SSIS installed on the SQL 2008 cluster to do this?

2. Can we run jobs on a SQL 2005 instance, pointing to a SQL 2005 SSIS instance/packages on a third server that connect to a SQL 2008 DB instance . I know that sounds crazy, but where exactly is the CPU and memory load in that design.. job on one server, package a seperate server and db on a third server.?

3. As mentioned, I have SQL 2005 SSIS instance.  I have local admin on that server and can connect to the ssis instance from my sql 2005 sql client on my desktop.  I added a new user to the server and added them to the SSIS and users group. I also made sure they could write to the folder where they will deploy packages. However, they get some kind of access denied while trying to connect, unless I give them local admin on the server.  What more access do they need than to be in the users and SSIS group and have write access to an SSIS deployment folders?


Many Thanks.

SSIS Package fails with Execute Process Task when run from SQL Agent Job


I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing "C:\WINNT\system32\cmd.exe" "/C net use
\\servername\foldername /USER:userid password" at "", The process exit code was "1" while the expected was "0".

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from

SQL Agent Roles and the Ability of an AD Group to Execute Jobs

I would like to give an AD group the ability to execute Agent Jobs using the 2005 Agent roles (SQLAgentUserRole, etc) but you can not assign ownership of a job to a group. I have seen some work arounds and the only one I am considering is using a proxy account. Does anyone have any insight on doing this? This is certainly a fairly common thing to do, give support technicians the ability to run a job.  Running sql server 2005 standard ed, sp3 on a windows server 2003 standard r2 ed, thanks

Sybase Access SSIS Packages


I am working on accessing data from our Sybase Databases to pull data over and place it in our MS SQL Server. We may not be able to install the needed Sybase driver and datasources on the SQL Server. If that is the case, I am not sure if this alternative would work, but wanted to ask the question.

We have a file server available. So if we load the Sybase driver and datasources on this file server, then I deploy the packages to a loacation on the file server, would I be able use SQL Agent on the SQL Server (located on another server) to run then package and would the datasources be accessed off the fileserver? The problem is we can add the datasources and driver to the file server but not an instance of SQL.

I have not tried this before and wanted to see if it is possible. If not is there another solution that could be used?

Errors when trying to execute output of PowerScript to recreate jobs


I am using this code to create (script out) SQL Agent job create code to one file- I use the Advanced tab in the SQL Agent Step (Powerscript step) to output the results to the file.  I get errors from the output file when I try to parse or run it because the variables keep being redefined for each step. Can anyone help me?


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
$srv.JobServer.Jobs | foreach {$_.Script()}


Msg 134, Level 15, State 1, Line 95

The variable name '@ReturnCode' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 104

The variable name '@jobId' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 243

The label 'QuitWithRollback' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 245

The label 'EndSave' has already been declared. Label names must be

SQL Agent, credentials, proxies and SSIS


We are trying to get SQL Server set up so that non-admin end-users can own and execute jobs that execute SSIS packages. While I understand what I'm about to describe will not pass a good security audit, at this point we're just trying to get it working.

There are three domain accounts involved here:

1. The SQL Agent account: ABC\SQLAgent

2. A domain user account with domain admin priviledges: ABC\Dave

3. A domain user account that is basically just a member of Domain Users, ABC\Mary.

The idea here is that we want Mary to be able to own and run a job, but run it as though she were impersonating Dave. Here's what we've attempted so far and the error we are receiving.


1. Add the domain account ABC\Mary to the SQL Server instance.

2. Map ABC\Mary to the msdb database with the default schema of 'dbo' and membership in the 'SQLAgentUserRole' database role.

3. Create a credential called 'Dave's Credentials' using the identity of ABC\Dave and specify the domain password.

4. Map Mary's account the the credential 'Dave's Credential'.

5. Create a SQL Agent Proxy called 'Proxy via Dave' using the credentials 'Dave's Credentials'. Add the proxy to the

SSIS subsystem.

6. Create a SQL Agent Job owned by ABC\Mary containing one job step that executes an SSIS package.

Give non-sysadmin user access to SQL Agent Jobs


Hi, I have a situation where a group of users (domain ad group) need access to create and edit SQL Agent Jobs. The group can not be sys admin. I tried with the server role SQLAgentOperatorRole. but that doesn't give access to edit all SQL Agent Jobs, only the one that the users owns.

Thanks in advance

Execute SSIS Package PART based on Some Condition

Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

Errors in the back-end database access module. OLE DB reported an overflow of the data type for colu

Getting an error when processing a dimension, Errors in the back-end database access module. OLE DB reported an overflow of the data type for column 0. Errors in the OLAP storage engine: An error occurred while the 'xxxxxx' attribute of the 'Dim XXXXXX' dimension from the 'XXXXXXX' database was being processed. Content of this dimension is very similar to one that is processing no problem. The content of the dimension is one field only of type date.  The original content was imported through SSIS from a DB2 database on an iseries machine.  Thought a first the issue was because of the data contains some null values but the dimension the process OK also has some null  values.  The dimension that processes ok is also a single field of type date. Any help greatly appreciated, Roscoe

Access to SQL Server Jobs

Hello folks, Would like to grant group of users ( windows logins) to CREATE / MODIFY / DELETE / SCHEDULE jobs on SQL Server 2005. But can not grant users SYSADMIN privileges. I know that by granting users SQLAgentOperatorRole, SQLAgenetReaderRole and SQLAgentUserRole the User will be able to create jobs but they will be the owner of the newly created job and other users can not MODIFY / VIEW / RESCHEDULE the jobs. Is there any way to customize permissions to windows logins to have permission to CREATE / MODIFY / DELETE / SCHEDULE jobs without granting them SYSADMIN privilages ? Appreciate your reply   Thank You Arunaroon

Install on New Windows7 - Getting Invalid SQL Server Agent credentials

Hi: I have a Developer License for MSSQL Server 2008.  Have just upgraded to Windows 7 Professional Edition.  When installing MS SQL Server, the setup shows that I have an instance of SQL Server Express already installed.  I am given a choice between Default Instance and Named Instance.  First time I chose named instance and then used the name of my machine - RBS1.  I received the following message:  The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service. Went back and tried Default instance and got same answer.  Can't proceed to install.  Any help in this greatly appreciated. roger  

Transactions in SSIS packages

Hi guys - I've been working in ssis for a bit but never have encountered the need for transactions - until I realized that I really need them. Here's my setup. I have a master package that has a sequence container in it. In that sequence container are 3 execute package tasks (Insert - Update - Delete). Each execute package task points to a child package which contains 50 execute package tasks (to perform the tasks on my different tables). From there - the actual work is done. (default everything on transaction/isolation etc) My problem/question is that I need to make it where if the insert fails, the update/delete do not happen and everything gets rolled back. Right now if something fails it just stops there and gives me half changed records. Is there a way to do this or am I just hoping for un-doable? Thanks!

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend