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 |
|
|
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? |
|
|
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 |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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 sysnameset @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 endif -- 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 |
|
|
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 |
|
|
|