Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Impossible to change 'sa' password ->error 18456

Author  Topic 

Mathias
Posting Yak Master

119 Posts

Posted - 2010-11-22 : 03:05:40
I have setup a new sql 2008 sp1 server on VMWare server. Each time I change the 'sa' password, I'll get the following message when running the system dbs backups.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
Error 18456, Severity: 14, State: 8.


I have no other option than to keep the original password. WMI is not running, the sql agent and sql service are running using local system account. I don't get it, the 'sa' password is only changeable in one location!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-22 : 08:27:54
you need to change the password in the job (sounds like a maintenance plan?) that you're calling system dbs backup.

better yet, have it log in with NT credentials instead of SQL
Go to Top of Page

Mathias
Posting Yak Master

119 Posts

Posted - 2010-11-22 : 10:57:23
The owner of the job is 'sa' but there is no password associated at the job level. Do you mean remove 'sa' as owner and put it back as owner?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-22 : 11:32:00
no. somehwere inside the job i think you, or somebody, hard-coded the old sa password.

but i'll use a windows domain account as job owner, not sa
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-22 : 15:56:29
Open the maintenance plan - and check the connection information for the maintenance plan. This should be set to windows authentication. If it is not set that way - you need to change it.

There is no reason to change this to use SQL authentication - and is probably where you are having issues.

Jeff
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-22 : 16:18:58
quote:
Originally posted by jeffw8713

Open the maintenance plan - and check the connection information for the maintenance plan. This should be set to windows authentication. If it is not set that way - you need to change it.

There is no reason to change this to use SQL authentication - and is probably where you are having issues.

Jeff


Exactly what I was trying to say. Only you said it better lol.
Go to Top of Page

Mathias
Posting Yak Master

119 Posts

Posted - 2010-11-23 : 02:43:58
Thank you, I didn't pay attention to this setting in the past and it make sense.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-23 : 11:20:10
russell - yeah, I thought that's what you meant.

One more note, on the agent jobs for maintenance plans - you can change those, but if you then modify the maintenance plan it is going to be reset back to the owner of the maintenance plan.

Since there is no provided functionality for changing the owner, I have found that updating the maintenance plan owner manually works. If it is SQL Server 2005 - this is stored in the sysdtspackages table in msdb, for SQL Server 2008 it is stored in the ssispackages table.

I always update the owner of the maintenance plan to sa - because I know that user (on my systems) cannot be used by anyone and it will always exist. You can change it to a domain user, but I don't like this option because if you cannot authenticate on the domain your agent jobs will fail. Yes, if the job is owned by a domain account SQL Server authenticates the owner before it runs the job.

The same thing goes for regular agent jobs - if the job is owned by a domain user, you are dependent upon access to the domain to run the jobs.

Jeff
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-23 : 14:42:56
quote:
Originally posted by jeffw8713

russell - yeah, I thought that's what you meant.

One more note, on the agent jobs for maintenance plans - you can change those, but if you then modify the maintenance plan it is going to be reset back to the owner of the maintenance plan.

Since there is no provided functionality for changing the owner, I have found that updating the maintenance plan owner manually works. If it is SQL Server 2005 - this is stored in the sysdtspackages table in msdb, for SQL Server 2008 it is stored in the ssispackages table.

I always update the owner of the maintenance plan to sa - because I know that user (on my systems) cannot be used by anyone and it will always exist. You can change it to a domain user, but I don't like this option because if you cannot authenticate on the domain your agent jobs will fail. Yes, if the job is owned by a domain account SQL Server authenticates the owner before it runs the job.

The same thing goes for regular agent jobs - if the job is owned by a domain user, you are dependent upon access to the domain to run the jobs.

Jeff





declare @new_owner	sysname
set @new_owner = N'SA'


if -- Verify SQL Server version is 2005
left(convert(varchar(50),serverproperty ('ProductVersion')),2) = '9.'
begin
update msdb.dbo.sysdtspackages90
set
ownersid = suser_sid(@new_owner)
where
ownersid <> suser_sid(@new_owner) and
suser_sid(@new_owner) is not null and
packagetype = 6 -- 6 = Maint Plan
end

if -- Verify SQL Server version is 2008
left(convert(varchar(50),serverproperty ('ProductVersion')),3) = '10.'
begin
update msdb.dbo.sysssispackages
set
ownersid = suser_sid(@new_owner)
where
ownersid <> suser_sid(@new_owner) and
suser_sid(@new_owner) is not null and
packagetype = 6 -- 6 = Maint Plan
end


CODO ERGO SUM
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-11-24 : 01:59:18
Thanks Michael - I have similar code, just didn't have access to it when I posted.

Jeff
Go to Top of Page
   

- Advertisement -