Post reply

DTS Package tries to login using old sa login

  • October 17, 2005 at 9:54 am

    #76438

    Hi guys,

    All my DTS packages used a login with sa rights. I dropped the login with sa rights and am running all DTS packages under a different logins with sa rights. the packages are run as scheduled jobs.

    However the job fails with error - unable to login as the old sa login. why is it still trying to access the old login. i changed all the connection properties in the dts packages and also the login under which the scheduled job runs.

    i changed the owner sid in sysdtspackages to match the one with sid in sysjobs.

    any ideas?

    thanks in advance.

  • October 17, 2005 at 10:37 am

    #597680

    Just checking that you changed the owner of the scheduled job rather than 'login under which the scheduled job runs'.

    To make sure, I would login to the server as the new sa account and delete and create the job again. I'm sure I remember coming across something similar and that was how I fixed it.

    Even though it shows new sa as owner of the job, I think it stores the old one, too.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • October 17, 2005 at 11:38 am

    #597700

    Sorry. I forgot to mention. If I drop and recreate the jobs, they work fine. Interestingly, if I script the job and re-create, it still takes the old login. I restarted the agent but does'nt work. Can't re-start sql server unfortunately.

    Wondering if there is another way.

  • October 17, 2005 at 12:30 pm

    #597726

    Were you aware that when you change a DTS package, the prior versions are still retained ?

    Under some circumstances ( that I have yet to determine), an older version of the DTS package is executed instead of the latest version.

    Try deleting all of the old versions for each of the DTS packages.

    SQL = Scarcely Qualifies as a Language

  • October 18, 2005 at 1:48 am

    #597843

    Further to this, it is a good idea to use the GUID (Globally Unique Identifier) that is produced when you schedule a DTS script out of Data transformation services.

    This will ensure that you never run anything but the version that you wish to.

    The GUID changes whenever you save the script, so you need to place the New GUID into the job step in the scheduler when this occurs.

    Kindest Regards,

    Phil Couch

  • October 18, 2005 at 6:57 am

    #597913

    Phil's idea works perfect. Replacing the DTS GUID works.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply