DTS, Login and password problem

Question

DTS, Login and password problem

steve hughes-190458

SSC Enthusiast

Points: 119
More actions
September 11, 2006 at 3:51 am

#76576

Hi,
We have SQL 2K and about 40 DTS jobs all created in the last few weeks. We have just changed the sa password on our sql servers and now all of the jobs fail. Ok I can go through each job and re-set the passwords, but what happens next month? Is there a better way? - apart from setting the jobs to run under Windows Authentication. Most of the jobs need to connect 2 or more SQl servers and create - copy data to different windows / unix boxes.
Any help would be appreaciated.
regards steve

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

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

steve hughes-190458 SSC Enthusiast Points: 119 More actions · Answer 1

OK, to rephrase this somewhat differently. What is the best practice for setting up DTS jobs that need a sqlserver connection? Do people use Windows Authentication solely? If not how often are passwords changed on servers? We had not implemented sql password change, due to the fact that applications log in and not users specifically - until security was breached recently and a rougue machine was detected in the database.

Anyone any comments / ideas on this issue please.

regards

steve

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

I would first say never use sa. SA is the most powerful login that can be in SQL.If all the DTS does is to read data or write data create a login appropriately.In all my Servers DTS runs under windows user and I haven't had any problem.

Thanks

Sreejith

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Sreejith is absolutely correct. The easiest way I've found to effect password changes on DTS jobs and the connections within them is to make use of Windows Authentication. Create yoruself a local user called service_dts or bob, or jane or whatever you want to call it, then only grant it the rights you need to grant it to make your jobs run correctly. The big on here is remembering when using files etc on the network it's slightly more of a pain with rights and such.

This helps improve your security and it makes the password change fairly painless. Remmeber also remember if you are using the SQL Agent to run these jobs, that you would actually be changing the Sql agent service to run as whatever user you create.

To help us help you read this[/url]For better help with performance problems please read this[/url]

EamonSQL SSCrazy Points: 2307 More actions · Answer 4

Hi,

here's a follow on issue/scenario.....

I create DTS packages using my windows user name. I have dbo (not in msdb though) access and would like to save it as dbo....Can I do this? I could not see anything in the Save As windows. I need to do this as I currently share the development with another developer and we have problems with me not been allow to amend his DTS packages and vice-versa.

Any help on how to save as dbo ownership welcome so the development can be made easier.

Regards,

Eamon

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

First, you probably should have posted this question in a thread by itself as this one is stale and your question has nothing to do with changing the SA password and login problems associated with it as the first question was asked.

Second, I sounds like a security issue. You can change the Creator in Disconnected Edit but I dont' think that's what you want. Check out this article, specifically para 4 and 5, as I think it may be what you need to check into.

http://www.databasejournal.com/features/mssql/article.php/3404791

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]