Change in 'sa' password, scheduled jobs failing

Hi all,
I have never come across this one before. I recently changed the 'sa' password. Now all of my scheduled jobs are failing with the error:
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.
I strange thing is that the 'sa' password is not referenced in any connection in any of my DTS Packages. Can anyone shed some light on this. I have hundreds of scheduled jobs and I don't want to have to recreate them all. Thanks.
The sa login and password are likely referenced in the
DTSRun command for the job. One common scenario for this is
when you have Enterprise Manager registered using the sa
login and you right click on the package, select Schedule
Package, this will create an encrypted DTSRun command in the
job's schedule which uses sa as that's what you would
currently be logged in as through Enterprise Manager.
I can't think of a fast way to redo hundreds of these.
Seems a better idea to have the packages run using trusted
connections via the service account instead.
-Sue
On Tue, 20 Apr 2004 09:56:02 -0700, "Greg"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have never come across this one before. I recently changed the 'sa' password. Now all of my scheduled jobs are failing with the error:
>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 Cod
e 1. The step failed.
>I strange thing is that the 'sa' password is not referenced in any connection in any of my DTS Packages. Can anyone shed some light on this. I have hundreds of scheduled jobs and I don't want to have to recreate them all. Thanks.
|||Are you referring to the SQL Server Agent jobs that are no longer working?
If so, try changing the "owner" of each job to one that has proper access
permissions.
Steve
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:E0D9E05D-45F8-4312-A9B5-2E99E0D3BFA0@.microsoft.com...
> Hi all,
> I have never come across this one before. I recently changed the 'sa'
password. Now all of my scheduled jobs are failing with the error:
> 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.
> I strange thing is that the 'sa' password is not referenced in any
connection in any of my DTS Packages. Can anyone shed some light on this.
I have hundreds of scheduled jobs and I don't want to have to recreate them
all. Thanks.
sql

Change in 'sa' password, scheduled jobs failing

Hi all,
I have never come across this one before. I recently changed the 'sa' passw
ord. Now all of my scheduled jobs are failing with the error:
DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18
456 (4818) Error string: Login failed for user 'sa'. Error source
: Microsoft OLE DB Provider for SQL Server Help file: Help cont
ext: 0. Process Exit Code
1. The step failed.
I strange thing is that the 'sa' password is not referenced in any connectio
n in any of my DTS Packages. Can anyone shed some light on this. I have hu
ndreds of scheduled jobs and I don't want to have to recreate them all. Tha
nks.The sa login and password are likely referenced in the
DTSRun command for the job. One common scenario for this is
when you have Enterprise Manager registered using the sa
login and you right click on the package, select Schedule
Package, this will create an encrypted DTSRun command in the
job's schedule which uses sa as that's what you would
currently be logged in as through Enterprise Manager.
I can't think of a fast way to redo hundreds of these.
Seems a better idea to have the packages run using trusted
connections via the service account instead.
-Sue
On Tue, 20 Apr 2004 09:56:02 -0700, "Greg"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have never come across this one before. I recently changed the 'sa' pass
word. Now all of my scheduled jobs are failing with the error:
>DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (48
18) Error string: Login failed for user 'sa'. Error source: Microsoft OL
E DB Provider for SQL Server Help file: Help context: 0. Process Exit
Cod
e 1. The step failed.
>I strange thing is that the 'sa' password is not referenced in any connection in an
y of my DTS Packages. Can anyone shed some light on this. I have hundreds of sched
uled jobs and I don't want to have to recreate them all. Thanks.|||Are you referring to the SQL Server Agent jobs that are no longer working?
If so, try changing the "owner" of each job to one that has proper access
permissions.
Steve
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:E0D9E05D-45F8-4312-A9B5-2E99E0D3BFA0@.microsoft.com...
> Hi all,
> I have never come across this one before. I recently changed the 'sa'
password. Now all of my scheduled jobs are failing with the error:
> 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.
> I strange thing is that the 'sa' password is not referenced in any
connection in any of my DTS Packages. Can anyone shed some light on this.
I have hundreds of scheduled jobs and I don't want to have to recreate them
all. Thanks.

Change Default database Confirm password required

I have recently added SP3a to my SQL 2000 system and every time I detach a
database and then reattach I lose all my default database settings for my SQL
login/users. when I go to change the default database and select OK it
requires me to confirm the password.
How to I get around this so I do not have to always retype the password.
Jacci
Why not to use BACKUP/RESTORE ?
"Jacci" <Jacci@.discussions.microsoft.com> wrote in message
news:84BD75D1-B541-4F33-BA63-FFACE2006D63@.microsoft.com...
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.
|||There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
first messed up the DTSGUI.dll, the second, to fix this, messed up the login
password issue you are discribing. The latest is 8.00.819, you are probably
running build 818. Severice Pack 3a is build 760.
However, as far as the default databases goes, you can not fix this issue
since logins are assigned default databases by DBID. Once that database is
attached, there is no corresponding DBID to default to. Moreover, those ids,
although incremental, are reused. So, if you were to bring another database
online before you reattached the original, the logins may be assigned to the
new database instead.
Here is the KB for the password issue:
http://support.microsoft.com/default...b;en-us;826161
http://support.microsoft.com/default...b;en-us;821277
Sincerely,
Anthony Thomas
"Jacci" wrote:

> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.
|||Thank You - this worked ;-)
"AnthonyThomas" wrote:
[vbcol=seagreen]
> There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
> first messed up the DTSGUI.dll, the second, to fix this, messed up the login
> password issue you are discribing. The latest is 8.00.819, you are probably
> running build 818. Severice Pack 3a is build 760.
> However, as far as the default databases goes, you can not fix this issue
> since logins are assigned default databases by DBID. Once that database is
> attached, there is no corresponding DBID to default to. Moreover, those ids,
> although incremental, are reused. So, if you were to bring another database
> online before you reattached the original, the logins may be assigned to the
> new database instead.
> Here is the KB for the password issue:
> http://support.microsoft.com/default...b;en-us;826161
> http://support.microsoft.com/default...b;en-us;821277
> Sincerely,
>
> Anthony Thomas
>
> "Jacci" wrote:
sql

Change Default database Confirm password required

I have recently added SP3a to my SQL 2000 system and every time I detach a
database and then reattach I lose all my default database settings for my SQL
login/users. when I go to change the default database and select OK it
requires me to confirm the password.
How to I get around this so I do not have to always retype the password.Jacci
Why not to use BACKUP/RESTORE ?
"Jacci" <Jacci@.discussions.microsoft.com> wrote in message
news:84BD75D1-B541-4F33-BA63-FFACE2006D63@.microsoft.com...
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
first messed up the DTSGUI.dll, the second, to fix this, messed up the login
password issue you are discribing. The latest is 8.00.819, you are probably
running build 818. Severice Pack 3a is build 760.
However, as far as the default databases goes, you can not fix this issue
since logins are assigned default databases by DBID. Once that database is
attached, there is no corresponding DBID to default to. Moreover, those ids,
although incremental, are reused. So, if you were to bring another database
online before you reattached the original, the logins may be assigned to the
new database instead.
Here is the KB for the password issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;826161
http://support.microsoft.com/default.aspx?scid=kb;en-us;821277
Sincerely,
Anthony Thomas
"Jacci" wrote:
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||Thank You - this worked ;-)
"AnthonyThomas" wrote:
> There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
> first messed up the DTSGUI.dll, the second, to fix this, messed up the login
> password issue you are discribing. The latest is 8.00.819, you are probably
> running build 818. Severice Pack 3a is build 760.
> However, as far as the default databases goes, you can not fix this issue
> since logins are assigned default databases by DBID. Once that database is
> attached, there is no corresponding DBID to default to. Moreover, those ids,
> although incremental, are reused. So, if you were to bring another database
> online before you reattached the original, the logins may be assigned to the
> new database instead.
> Here is the KB for the password issue:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;826161
> http://support.microsoft.com/default.aspx?scid=kb;en-us;821277
> Sincerely,
>
> Anthony Thomas
>
> "Jacci" wrote:
> > I have recently added SP3a to my SQL 2000 system and every time I detach a
> > database and then reattach I lose all my default database settings for my SQL
> > login/users. when I go to change the default database and select OK it
> > requires me to confirm the password.
> > How to I get around this so I do not have to always retype the password.

Change Default database Confirm password required

I have recently added SP3a to my SQL 2000 system and every time I detach a
database and then reattach I lose all my default database settings for my SQ
L
login/users. when I go to change the default database and select OK it
requires me to confirm the password.
How to I get around this so I do not have to always retype the password.Jacci
Why not to use BACKUP/RESTORE ?
"Jacci" <Jacci@.discussions.microsoft.com> wrote in message
news:84BD75D1-B541-4F33-BA63-FFACE2006D63@.microsoft.com...
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
first messed up the DTSGUI.dll, the second, to fix this, messed up the login
password issue you are discribing. The latest is 8.00.819, you are probably
running build 818. Severice Pack 3a is build 760.
However, as far as the default databases goes, you can not fix this issue
since logins are assigned default databases by DBID. Once that database is
attached, there is no corresponding DBID to default to. Moreover, those ids
,
although incremental, are reused. So, if you were to bring another database
online before you reattached the original, the logins may be assigned to the
new database instead.
Here is the KB for the password issue:
http://support.microsoft.com/defaul...kb;en-us;826161
http://support.microsoft.com/defaul...kb;en-us;821277
Sincerely,
Anthony Thomas
"Jacci" wrote:

> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||Thank You - this worked ;-)
"AnthonyThomas" wrote:
[vbcol=seagreen]
> There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
> first messed up the DTSGUI.dll, the second, to fix this, messed up the log
in
> password issue you are discribing. The latest is 8.00.819, you are probab
ly
> running build 818. Severice Pack 3a is build 760.
> However, as far as the default databases goes, you can not fix this issue
> since logins are assigned default databases by DBID. Once that database i
s
> attached, there is no corresponding DBID to default to. Moreover, those i
ds,
> although incremental, are reused. So, if you were to bring another databa
se
> online before you reattached the original, the logins may be assigned to t
he
> new database instead.
> Here is the KB for the password issue:
> http://support.microsoft.com/defaul...kb;en-us;826161
> http://support.microsoft.com/defaul...kb;en-us;821277
> Sincerely,
>
> Anthony Thomas
>
> "Jacci" wrote:
>

Change Database Password

I have a SQL database hosted on a server. This server supports SQL Sever 2000. How do I change the password to the password?

Thanks in advance,

I found out the answer. sp_password 'currentPSWD', 'newPSWD'

sql

Change database attributes with SQL?

Is there a way to change the password or encryption settings with SQL or do I need to use Compact from code to do so?

I'm trying to work around the issue that USE does not accept a password.

if you have an existing SQL CE 2 or SQL Mobile database and want to change the password or encryption settings, the only option is to run the Compact method on the SqlCeEngine object. If you have forgotten the old password, you are out of luck - there is no way to reset it.

Darren

|||I was afraid of that. Again, we need a bit of help in the SQL Ev SQL. The USE statement needs to accept a Password argument for this engine.sql

Change database Access - (newbie question)

You should provide the password for the new user.
This article may help with the error message:
http://support.microsoft.com/default.aspx?kbid=826161
Hope this helps.
Tim

>--Original Message--
>I have added a new User Login in Enterprise Manager. I
>then want to give that User access to a database. When I
>tick the appropriate table in the the Database Access
>property of the User, I am prompted to confirm the
>password.
>Which password is the system expecting here? The user's
>password or the SA passsord? But no matter what I type,
I
>get a message: "The confirmation password is not correct"
>
>
>
>.
>Newbie? I don't think so. This has always bugged the heck out of me. Thanks
for asking!
And, even if it is NOT a new user, when you want to add a database from the
Server Security folder, just be sure to reenter the users password before yo
u hit the "permissions" tab. Then, when you add the database access you desi
re, the prompt for the new
password will accept the password.
David

change "sa" password -> replication out

Hello,
I have several servers SQL with replications they are a priori, configure
all similar, nevertheless on the one if I change the password of the account
"sa" replications are not done any more. Here is the message of the log "
The job failed. The Job was invoked by User sqladm. The last step to run
was step 1 (Run agent.)." Only the password of "sa" has to change "sa" is
owner job. Then why the problem comes of sqladm? With the other servers not
of problem.
Thank you.
Vincent,
are you using trusted connections or sql authentication for your agents?
Have a check in the distributor properties to verify, as it would seem your
agents are using 'sa' to log in. If this is the case, the password will need
updating here also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)