June 16, 2003 at 12:34 am
Hi!
Just cnaged the 'sa' password, both connections for the DTS package use other then 'sa' logins. Can't find where to look.
05.30 Import New Items Executed as user: My_Domain\SQL_Admin. 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.; MsgID: 0;
June 16, 2003 at 8:46 am
After some investigation found that the job, that runs DTS packet keeps old login and password for 'sa' like that:
DTSRun /~Z0x2E36B09E3D84999007923D66F8BEDF6EEAC7764940F6F9C56377C84BC86377A3831862105688A15A1690A6AF3350CE62C48B2BF863D4995D3AF4F98174B6092AA6EB64C94DC355CDDB4322CED47142926BEC1936C1A060C876CBFD2D7DABE631148F1B6C3C8F5A16A3EE128A96072E88C833F49017236E81FD80C8578FC22337F56C7B
Is it possible to correct this some way?
June 16, 2003 at 9:15 am
Use DTS Designer to open this package and change from there.
June 16, 2003 at 9:23 am
To my understanding DTS designer will not help much, because the package runs fine, if executed "by hand", it does not use 'sa' login for any connection. The job makes the problem... Am I wrong?
June 16, 2003 at 11:30 am
Try to re-schedule the job from this DTS package.
June 16, 2003 at 11:33 am
It sounds like you should delete your old job and recreate it. Find the package under the DTS menu, then right-click on it and select Schedule Package...
You can also change the DTSRun line to something unencrypted, but that would show your sa password in plain text. See "dtsrun utility" in Books Online.
If your SQL Agent runs in the context of a user account (not the system account), and that account has permissions to do everything you need within the package, then you can change the DTSRun line to use a trusted connection. Remember to make the SQL Agent account the owner of the job.
Edited by - allyannea on 06/16/2003 11:33:40 AM
June 17, 2003 at 1:06 am
My SQLSERVERAGENT starts with my_domain\sql_admin accout. I plan to use the same account for DTS packages, so I need to create a DTSrun command like this:
dtsrun /Ssaturn /UDOMAIN.LOCAL\SQL_admin /E /N[Import New Items]
Right?
June 17, 2003 at 6:09 am
That looks about right for a package that's local to the SQL Server (if in the repository, add /R). Not sure what the .LOCAL is but I assume it is part of your actual domain name.
Make sure my_domain\sql_admin has appropriate permissions in the database. Usually they are set up as local admins, and local admins inherit sa rights via BUILTIN\Administrators.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply