Post reply

DTS package fails after SA password is reset

  • March 30, 2004 at 8:04 am

    #60069

    I have a few DTS packages executing as jobs in SqlAgent. I recently changed the SA password and the jobs started failing with the following error message...

    Executed as user: DM\sql_service_account. DTSRun:  Loading...      Error:  -2147217843 (80040E4D); Provider Error:  18456 (4818)      Error string:  Login failed for user 'sa'.      Error source:  Microsoft OLE DB Provider for SQL Server      Help file:        Help context:  0.  Process Exit Code 1.  The step failed.

    The DTS packages were created by another user other than SA and they don't use the SA user or password anywhere in the package. The jobs seems to be failing while trying to execute the Dtsrun command to load the package. It works fine if I change the SA password back to the old one.

    What I can't understand is why would it still be trying to use the old SA password when loading the DTS package.

    Thanks in advance!

     

  • March 30, 2004 at 8:33 am

    #501084

    Did you check the string the is executing the job step? You could be using the /U switch with the SA user and pwd.

    Within the DTS I would also check to see if the package is logging to a server and check that it's not trying to use SA for that.


    Shamless self promotion - read my blog http://sirsql.net

  • March 30, 2004 at 8:48 am

    #501087

    I'm not using the /U switch when executing the Job. It is a job that is owned by SA and is executing under the service account that runs mssqlserver and the sqlserveragent services. both have sysadmin access to sqlserver and admin access to the local node.

    I looked within the package and and the SA user/password isn't hardcoded anywhere within the dts package.

    I did some testing before I posted this question and determined that the dts package isn't even starting to execute. The dtsrun command is failing trying to connect to the server and execute the package. Its almost like the dtsrun executable has the old sa password hardcoded somewhere.

     

  • March 31, 2004 at 11:36 am

    #501347

    I've come across the same thing.  Try this.  Open the package and do a "save as" and you will see where the password was probably hard-coded.  After you save it with a different name (and password), run it or schedule it and it should be fine.

    Linda 

  • March 31, 2004 at 11:42 am

    #501348

    That was the problem. The DTS package was saved with the SA user and password.

    Thanks so much for you help!!

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

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