dts and sa password

[kuppu swamy]

I work for American express, Phoenix as a SQL 7.0 Admin
DBA.
I am facing a problem with the following. When ever we
change the sa password as routine, we need to re-build the
jobs which run as dts packages. The user is not sa on the
local package as well as the owner on the job is not sa.

Still we get a error message saying login failed as sa in
the job history.

What is the reason? and the solution to get rid of, so
that the sa password can be changed periodically, to
ensure safety, but doesn't mess-up with the jobs
consisting of dts runs (packages).

[Jasper Smith]

How is SQLAgent connecting ?
Right click on SQLAgent Icon in Enterprise Manager
and goto Properties. Click on the connection tab, is
it set to SQL Authentication ? If so, this needs to be
changed when you change the sa password or you
can set it to Windows Authentication.

HTH
Jasper Smith

"kuppu swamy" <kuppu.s....@aexp.com> wrote in message
news:346301c1e591$47c8dfd0$b1e62ecf@tkmsftngxa04...

[kuppu swamy]

Thanks for the quick update. It is set to Windows
Authentication. But still my jobs consisting of DTS are
failing, why?

Kuppu.

>.
>

[Jasper Smith]

Is it the job that fails or the dts package ?
It sounds like SQL Agent should be impervious
to sa password changes so do any of the DTS
packages use sa for connections ?

HTH
Jasper Smith

>.
>

[kuppu swamy]

The jobs fail but the packages run fine interactively, and
the packages don&#8217;t use sa connection, but a 'sa_role' user
connection. When I try to schedule these packages as a
job, they are getting created as sa, because of my logon
to the server as sa. So I manually, change the owner of
the job as the same 'sa_role' user connection. After
making these changes( meaning - username on package is
sa_role connection, and owner of the job is 'sa_role' user
connection)when I change the sa password the job fails.
But the package runs fine interactively.

The message from job history is that - login as sa failed.
I am not all using sa login either for the package or job
as said above.

Regards,
Kuppu.

>.
>

[Sue Hoegemeier]

It sounds like you may be scheduling the packages through
the scheduling functionality of DTS packages themselves.
When you do this, an encrypted dtsrun command line string is
created using the login based on the logged in user account
used when scheduling the package - and that would explain
why it is using sa
Try manually creating the dtsrun run command line for the
job. You can find the parameters needed for dtsrun in books
online but it's basically just:
dtsrun /S<servername> /U<user> /P<password> /N<package_name>

-Sue

[Darren Green]

In article <6kdrbughvfpb4kdfc...@4ax.com>, Sue Hoegemeier
<Su...@nomail.please> writes

>It sounds like you may be scheduling the packages through
>the scheduling functionality of DTS packages themselves.
>When you do this, an encrypted dtsrun command line string is
>created using the login based on the logged in user account
>used when scheduling the package - and that would explain
>why it is using sa
>Try manually creating the dtsrun run command line for the
>job. You can find the parameters needed for dtsrun in books
>online but it's basically just:
>dtsrun /S<servername> /U<user> /P<password> /N<package_name>
>
>-Sue
>

My guess exactly.
I think the reason why you are using sa is because the server
registration in enterprise manager server uses sa, so the local SQL-NS
stuff that creates the DTSRUN command lines uses the same info as the
rest of EM.

To avoid this in the future try re-creating the jobs using integrated
security in EM, or go manual as Sue describes above.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

[kuppu swamy]

yes I am scheduling these as sa, but in the package I am
changing the user name(say ABC) as not sa, as well as
changing the owner of the job once created as ABC from
sa.

One more thing, when I did so, the dtsrun command in the
job is starting like / ~ Z -----. I searched the books on
line, but this parameter Z is no where specified in the
dtsrun utility.

Kuppu.

>.
>

[kuppu swamy]

Can you PL take the pain in explaining how to create a job,
using integrated security in EM?

How exactly is this differnt from the usual way of
creating jobs from EM -> SQL server agent -> management ->
New job so on.

kuppu.

>.
>

[Sue Hoegemeier]

For your issue, It really doesn't matter who owns the job or
who the owner of the package is. What matters is the account
you have the server registered under in Enterprise Manager.
You are logging in with Enterprise Manager using sa and
that's why the packages pick up sa as the user for the
dtsrun command when you schedule them through the DTS
Schedule package functionality.
/~Z is for the encrypted command line that gets generated
when you schedule the package through DTS.
Change the command in the job so that the dtsrun command
uses:
/S<the name of your server> /U<the user you want to use>
/P<the password for this user> /N<the name of your package>
There is more information in books online under the dtsrun
utility topic.

-Sue

On Wed, 17 Apr 2002 13:29:01 -0700, "kuppu swamy"

[kuppu swamy]

I got it !!

Thanks everybody for the help !!!

Regards,
Kuppu.

>.
>