0

I'm trying to execute a batch file in SSIS. This batch file is located on a server on the network. I need to also pass in different user and password.

I've tried to setup the executable to be the following \networkservername\location and I've tried the cmd.exe and put the runas command on the argument. Neither worked and I'm not finding clear instructions on how to accomplish this. Any help would be greatly appreciated.

This is the error that I'm receiving so I don't think it is setup accurately enter image description here

prinkpan
  • 2,117
  • 1
  • 19
  • 32
Pam
  • 1
  • 1
  • Why do you need to run in it under a different user account? – holder Aug 20 '20 at 13:04
  • Because the bat jobs does inserts into production sql tables. I can run the bat job when I'm logged in the server with no problem. – Pam Aug 20 '20 at 13:09
  • How're you executing the ssis packages? SQL agent job? – holder Aug 20 '20 at 13:10
  • Right now I'm testing on my local but eventually it will execute as an SQL agent job – Pam Aug 20 '20 at 13:12
  • Ok. I guess your user account doesn't have access to the database. you need to set that up as well as for the sql server agent account(when you start executing the ssis package via sql agent job) – holder Aug 20 '20 at 13:16
  • True. So can this be done within SSIS or not? – Pam Aug 20 '20 at 13:19
  • As far as I know you cannot impersonate the way you want in SSIS. It's better to set the rights on the database for those accounts who're actually executing the file – holder Aug 20 '20 at 13:32
  • This link shows you how to do it in a script task. https://stackoverflow.com/questions/4624113/start-a-net-process-as-a-different-user – KeithL Aug 20 '20 at 14:00

1 Answers1

0

I don't take credit for this but I took this from another stackoverflow answer.

Here's the script task way to execute a process as someone else:

System.Diagnostics.Process proc = new System.Diagnostics.Process();
System.Security.SecureString ssPwd = new System.Security.SecureString();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.FileName = "filename"; //use the fullpath
proc.StartInfo.Arguments = "args...";
proc.StartInfo.Domain = "domainname";
proc.StartInfo.UserName = "username";
string password = "user entered password";
for (int x = 0; x < password.Length; x++)
{
    ssPwd.AppendChar(password[x]);
}
password = "";
proc.StartInfo.Password = ssPwd;
proc.Start();
KeithL
  • 5,348
  • 3
  • 19
  • 25