SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Impossible to change 'sa' password ->error 18456
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mathias
Posting Yak Master

France
119 Posts

Posted - 11/22/2010 :  03:05:40  Show Profile  Reply with Quote
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!

Edited by - Mathias on 11/22/2010 03:07:19

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 11/22/2010 :  08:27:54  Show Profile  Visit russell's Homepage  Reply with Quote
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

France
119 Posts

Posted - 11/22/2010 :  10:57:23  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 11/22/2010 :  11:32:00  Show Profile  Visit russell's Homepage  Reply with Quote
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

USA
769 Posts

Posted - 11/22/2010 :  15:56:29  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 11/22/2010 :  16:18:58  Show Profile  Visit russell's Homepage  Reply with Quote
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

France
119 Posts

Posted - 11/23/2010 :  02:43:58  Show Profile  Reply with Quote
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

USA
769 Posts

Posted - 11/23/2010 :  11:20:10  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 11/23/2010 :  14:42:56  Show Profile  Reply with Quote
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

USA
769 Posts

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

Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000