August 10, 2002 at 9:51 pm
I need to change the SA password on a SQL 2000 server. There are about 20 DTS packages on this system and most of them used the sa account to connect to the databases. I changed the connections in the packages to use Windows authentication instead, and they worked fine scheduled. After a couple of days, I changed the sa password, but the scheduled jobs failed saying that sa could not login. The owner of all of the jobs is sa and the jobs that run only stored procs ran fine.
I quickly set the sa password back, but am not sure what to do next. I'm wondering if it has something to do with how the DTS packages were saved originally. If I open one of them, and click "Save As", I see that I can save the package with Windows or a SQL account. I'm wondering if this is where the problem lies. If so, I guess I can open each one up and save with the Windows authentication. But what account is it saving under?
Any ideas on how to get the security correct would in a DTS package would be appreciated.
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 15, 2002 at 4:01 pm
I recently added a procedure to the scripts section that should help you. DTS packages are stored on the server in msdb.dbo.sysdtspackages. You need to update both the owner, and owner_sid fields. Owner_sid is the field used by SQL when checking security.
update sysdtspackages set owner = 'domain\username', owner_sid = suser_sid('domain\username')
IMHO, you're much better off going the trusted connection route.
Good luck.
John
August 16, 2002 at 10:27 am
This is really driving me nuts! Here is an example:
DTS1 originally had a connection object to the SQL Server using SA. The connection was changed to "Windows Authentication". The old versions of the package were deleted. The package can be executed manually even if the sa password has been changed. BUT when the scheduled package is run, it will fail (login failed for user sa) when the sa password has changed. The scheduled job runs fine if the sa password is the same as it was when the original package was written.
DTS2 was originally written with a connection object using Windows Authentication. It never used the sa to connect. This package/job runs no matter what. The package has the same owner as DTS1.
It's like the packages "remember" what used to be there! If I create a new DTS identical to DTS1, but never use sa then the new package works fine.
So, I guess I will have to re-create every package. Luckily, they are not too complicated!
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 16, 2002 at 10:59 am
DTS1 - fails when scheduled to run - login failed for SA - runs ok when fired manually. Who owns the job? How is the job executing the package?
August 16, 2002 at 12:21 pm
SA owns the job all 3 cases. I had also tried changing the owner of the job that runs DTS1 to another SQL Login and got the same error message about sa login failed.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 16, 2002 at 12:32 pm
Kathi,
I can see why this would make you want to pull your hair out. Very important, don't forget to breathe.
Please try this. Change the owner of a job to your domain\loginname (nt security). Your domain\loginname is set up as a user with proper priviledges, right? Then change the owner of the DTS package to your domain\loginname. Make sure the owner_sid is also updated to suser_sid(domain\loginname). Finally make sure the connection in the package is set to use a Trusted Connection. Cross your fingers, do the hokey pokey, turn yourself about, and try it.
Let me know what happens.
John
August 16, 2002 at 1:02 pm
Unfortunately, no scheduled jobs will run at all if the owner is a domain account. I suppose that it is because we are running NDS instead of a Windows ar AD domain. This is the message I get emailed to me: "Unable to determine if the owner (MYDOMAIN\myAccount) of job _Export Cli_Mat for DOCS has server access (reason: Could not obtain information about Windows NT group/user 'MYDOMAIN\myAccount'"
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 16, 2002 at 1:22 pm
Ok, what does the command line from the job look like? Do you have a /U and /P switch somewhere? The /P switch is the password stored in the job. /U will be the user, in your case, sa.
August 16, 2002 at 1:53 pm
Hey, that's it!!! I recreated the JOB and the new job runs with the password change. The problem was in the DTSRun command. The original had the encrypted U parameter (the reason I didn't notice it), the new job has only the ID of the package.
So, all I have to do is alter the DTSRun commands. Woo Hoo!!!
Thanks for your help. I have had this job for about 3 months and SA has been used in the past for just about everything. I'm on a crusade to stop it's abuse. This particular server has been a real problem because of DTS.
Thanks again and have a great weekend,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 16, 2002 at 2:04 pm
Cool. Now that we uncovered the problem, make your life a little easier so you don't have to go through this next time. Create a procedure called, sp_runDTS that accepts the name of the package to run as a parameter. Store the SA login and password, encrypted of course, in this stored proc. Then change your jobs to execute the packages via this sp. The next time you want to change the sa password, you'll only have to update this sp instead of your 20 jobs.
Ours looks like this:
DECLARE
@AdhocSQL varchar(1000)
SET @AdhocSQL = "c:\Progra~1\Micros~4\80\Tools\Binn\DTSRun /S " + @@servername + " /~U encrypted_username /~P Encrypted_password /N" + @Package_Name
IF @Debug = 1
exec @FileError = master..xp_cmdshell @AdhocSQL
ELSE
exec @FileError = master..xp_cmdshell @AdhocSQL, no_output
🙂
John
August 16, 2002 at 3:51 pm
Ok, that looks useful. This may be a dumb question, but how do you determine the encrypted password?
I see in the DTSrun utility, you can create a completely encrypted command using /!Y, but not the individual pieces. I haven't found anything else. The command that was generated when I recreated the job was a completely line.
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 16, 2002 at 3:56 pm
Good question... I'll ask our dba on Monday if nobody on here answers before then.
August 19, 2002 at 12:32 pm
Found out how to do it. If you check SQL 7 format, it will encrypt each parameter instead of the entire line.
Thanks for all of your help!!!
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply