Authentication in DTS

• beath

  SSCrazy

  Points: 2939

  More actions

  November 3, 2003 at 8:46 am

  #160841

  I've got all the developers using Windows Authentication for the connection objects in DTS packages.

  Thing is, when one of them leaves, and their NT account is deleted, I'm going to have to manually change all the connection objects to a new account and this could run into the hundreds. I don't want to introduce a generic account either as then auditing suffers.

  What's the solution...?

  cheers

• Steven.

  SSCrazy Eights

  Points: 9806

  More actions

  November 3, 2003 at 12:32 pm

  #480690

  You could add a 'dynamic property task' to define the connection string from something like a .ini file. Hence you would only need to change the ini file rather than all the packages.

  Steven

• stevevincent

  SSC Veteran

  Points: 265

  More actions

  November 4, 2003 at 1:21 am

  #480731

  I agree with Steven, but in addition, you might try running the package with a parameter string from the job scheduler. Eg.

  DTSRun /S "MyInputServer" /N "MyDynamicPackage" /A "MyDbGVar":"8"="MyDb" /A "MyOputServerGVar":"8"="MyOPServer" /W "0" /E

  You will need to create Global variables for the input/output servers and database names. If you run the supplied DTSRUNUI.EXE (in the BINN folder of your MSSQL program files installation, it will build this string for you - you just need to copy and paste the result into your command string in your job.

  HTH

  Steve Vincent

• mcudmore

  SSC-Addicted

  Points: 495

  More actions

  November 4, 2003 at 9:02 am

  #480783

  When Windows Authentication is used within a DTS package for connection objects, it is not specific to the NT account that created the package. For example, if you run the DTS package from a job, the account that the SQL Server Agent service runs under is the user that the connection within the DTS package will use. If the developer runs the package interactively through DTS Designer on their workstation, then the package will use the developer's NT account.

  Hope this is what you were looking for.

  Sincerely,

  Mark Cudmore, MCDBA

  
  Sincerely,
  
  Mark Cudmore, MCDBA