none
Executing SSIS packages as another user

    Question

  • The scenario:

    I have a package that first needs to copy some files from a workstation computer up to a server. If I set the package execution up as a job, the packages executes under a domain account (<domain>\<sql agent>). This domain account does not have access rights to the directory on the workstation, therefore the package can not see or copy the files in the directory. I have tried to execute the package from the command line using 'RUNAS' with 'dtexec.exe' (runas /user:<domain>\<user> detexec.exe ...) using my own user name and apparently, because you have to wait for the password prompt, this doesn't work either.

    So, is there a way for me to set up a package to run under a specific set of credentials so that I am able to copy the files from the workstation up to the server? I realize that just setting up the current <sql agent> user with the proper credentials would be the easiest, but our network admin group won't hear of it. They want one user id that SQL Server, SSIS, SSAS and SSRS run under (the <sql agent> account) and another user id that has the file system access that I described above.

    Any help would be much appreciated.

    Thank you.

    Wayne E. Pfeffer
    Sr. Systems Analyst
    Hutchinson Technology Inc.

    Friday, February 09, 2007 7:43 PM

Answers

  • I actually found a way around this.

    By using the 'ActiveX Script Task' in control flow, you create the following script:

    function fnCopyFile()
    {
         var net = new ActiveXObject ("WScript.Network");
         var DriveLetter = "X:";
         var RemotePath = "<<Some UNC Path>>";
         var Connected = false;
         var AllDrives = net.EnumNetworkDrives();
         for (i = 0; i <= (AllDrives.Length - 1); i += 2)
         {
              if (AllDrives.Item(i) == DriveLetter)
              {
                   Connected = true;
              }
         }
         if (!(Connected))
         {
              net.MapNetworkDrive(DriveLetter, RemotePath, false, <<Username to connect as>>, <<Password for Username>>);
         }
         fso = new ActiveXObject("Scripting.FileSystemObject");
         fso.CopyFile(<<Path  to source file or directory of files>>, <<Path to destination directory>>);
    }

    I hope people find this useful.

    Wayne

    Friday, March 23, 2007 2:44 PM

All replies

  •  Wayne Pfeffer wrote:

    The scenario:

    I have a package that first needs to copy some files from a workstation computer up to a server. If I set the package execution up as a job, the packages executes under a domain account (<domain>\<sql agent>). This domain account does not have access rights to the directory on the workstation, therefore the package can not see or copy the files in the directory. I have tried to execute the package from the command line using 'RUNAS' with 'dtexec.exe' (runas /user:<domain>\<user> detexec.exe ...) using my own user name and apparently, because you have to wait for the password prompt, this doesn't work either.

    So, is there a way for me to set up a package to run under a specific set of credentials so that I am able to copy the files from the workstation up to the server? I realize that just setting up the current <sql agent> user with the proper credentials would be the easiest, but our network admin group won't hear of it. They want one user id that SQL Server, SSIS, SSAS and SSRS run under (the <sql agent> account) and another user id that has the file system access that I described above.

    Any help would be much appreciated.

    Thank you.

    Wayne E. Pfeffer
    Sr. Systems Analyst
    Hutchinson Technology Inc.

    Wayne,

    I'm racking my brains around this one but to be honest I think you might be stuck. Do you have another scheduling engine that you can use other than SQL Agent?

    I'm surprised SQL Agent doesn't have the option to execute jobs as someone else.

    Perhaps you could change to use SQL auth rather than Windows auth inside the package. I suspect you really don't want to do that though - I know I wouldn't.

    -Jamie

     

    Friday, February 09, 2007 7:55 PM
  • Runas.exe requires console input for the domain account password. Cpau, which is a runas replacement, and far more functional, does not. (http://www.joeware.net/win/free/tools/cpau.htm).

    I've used cpau on occasion for impersonation. Not with dtexec per se, but I see no reason why it wouldn't work here too.
    Monday, February 12, 2007 5:59 PM
  • I actually found a way around this.

    By using the 'ActiveX Script Task' in control flow, you create the following script:

    function fnCopyFile()
    {
         var net = new ActiveXObject ("WScript.Network");
         var DriveLetter = "X:";
         var RemotePath = "<<Some UNC Path>>";
         var Connected = false;
         var AllDrives = net.EnumNetworkDrives();
         for (i = 0; i <= (AllDrives.Length - 1); i += 2)
         {
              if (AllDrives.Item(i) == DriveLetter)
              {
                   Connected = true;
              }
         }
         if (!(Connected))
         {
              net.MapNetworkDrive(DriveLetter, RemotePath, false, <<Username to connect as>>, <<Password for Username>>);
         }
         fso = new ActiveXObject("Scripting.FileSystemObject");
         fso.CopyFile(<<Path  to source file or directory of files>>, <<Path to destination directory>>);
    }

    I hope people find this useful.

    Wayne

    Friday, March 23, 2007 2:44 PM