DTEXEC problem

• frank-363642

  Old Hand

  Points: 393

  More actions

  August 6, 2007 at 4:10 am

  #118148

  Hi Friends,

  I  am getting a security problem while executing the SSIS Packages.

  Previously, we had created a package in DTS and saved it as .dts structured file.

  I am invoking this package via a stored procedure which dynamcially creates a job

  .

  .

  .

  .

  .

  declare @name varchar(1000)

  set @name='sample'

  set @cmd = '"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /F "D:\test.dts" /N "' + @sample + '" /W "0" /U "test_usr" /P "test_usr" '

  -- set the global vars

  SET @cmd = @cmd + '/A User:8="' + @p_user + '" '

  declare @jname varchar(128)

  set @jname = cast(newid() as char(36))

  exec msdb.dbo.sp_add_job

   @job_name   = @jname,

   @enabled  = 1,

   @category_name   = 'MyJobs',

   @delete_level  = 1,

   @job_id   = @jid OUTPUT

  exec msdb.dbo.sp_add_jobserver

   @job_id   = @jid,

   @server_name  = '(local)'

  exec msdb.dbo.sp_add_jobstep

   @job_id    = @jid,

   @step_name   = 'call DTS Package',

   @subsystem   = 'CMDEXEC',

   @command   = @cmd

  exec msdb.dbo.sp_start_job

   @job_id    = @jid

   .

   .

   .

   .

  This is working fine.

  Now we have converted the existing DTS package to SQL server 2005 SSIS packages.

  So i tried to execute the SSIS package from cmdline, as follows

   

  "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\sample.dtsx" /U "test_usr" /P "test_usr"

  /SET "\Package.Variables[User::Userid].Properties[Value]";"frank"

  Microsoft (R) SQL Server Execute Package Utility

  Version 9.00.1399.06 for 32-bit

  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

  The File option cannot be specified with the DTS, SQL, Server, User, or Password options.

  C:\>

  I am getting the follow error.

  The File option cannot be specified with the DTS, SQL, Server, User, or Password options.

  C:\>

  Can anyone guide me in solving this issue or else what could be the resons why we cannot use the

  File option in DTExec with /User and other options

  Thanks in advance,

  franky

   

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  August 6, 2007 at 4:37 am

  #724781

  Would the UserName and password not be for packages saved in MSDB only and not files?

  If you omit the parameters, does it work?

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• frank-363642

  Old Hand

  Points: 393

  More actions

  August 6, 2007 at 4:45 am

  #724784

  You r corecct.

  It is running fine.If i remove the /U and /P switches.

  But then what is authentication mechanism is taking by default?

  Regards,

  franky

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  August 6, 2007 at 4:54 am

  #724786

  None.

  Well, none to run your package.

  Any connections in your package will use their authentication.

  So if your package is connecting to SQL using NT Auth, it will be using the context in which the package is run. i.e. Your credentials.

  If you want to use SQL auth in the package, you have to change it within the package and specify the details in there.

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• frank-363642

  Old Hand

  Points: 393

  More actions

  August 6, 2007 at 5:00 am

  #724789

  Thank you very much Crispen.

   I understood.