Issues with SQL Server 2005 SP3 and Maintenance Plans

[reporting]

Some of the maintenance plans I have created did not delete the backup files that the maintenance plans created. I found it bizarre because some did and others didn’t…

I found an article on the Microsoft KBase that explains the cause (Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at different intervals than intended (

http://support.microsoft.com/kb/933508)

.

I decided to install SP3. The first server I ran it on worked just fine. Today, I took a look and the backups were deleted. I installed SP3 on another client server with this porblem today. The install failed for 2 components: “Database Services” and “Analysis Services” – all other components were upgraded to SP3.

I rebooted the server and tried it again. Same failure. I verified the config and found some minor differences between the first server and the 2nd one. I made them identical and tried a third time. Same failure.

The SP3 install log notes these 2 failures:

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final)   : 
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Error Number              : 29512
Error Description         : MSP Error: 29512  SQL Server Setup was unable add user NT AUTHORITY\SYSTEM to local group 
                            Domain Name\SQLServer2005SQLAgentUser$SERVEUR$MSSQLSERVER.
----------------------------------------------------------------------------------
Product                   : Analysis Services (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final)   : 
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\OLAP9_Hotfix_KB955706_sqlrun_as.msp.log
Error Number              : 29512
Error Description         : MSP Error: 29512  SQL Server Setup was unable add user NT AUTHORITY\SYSTEM to local group 
                            Domain Name \SQLServer2005MSOLAPUser$SERVEUR$MSSQLSERVER.

I took a look at adding NT AUTHORITY\SYSTEM to those groups … but the groups don’t exist!

I then decided to create new Maintenance Plans. I received the following error message:

Microsoft SQL Server Management Studio is unable to load this document
Error	1	Error loading 'System Database Maintenance Plan' : Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSApplication90'. 
This operation failed because the QueryInterface call on the COM component for the interface with IID '{7591CC39-276A-4973-97C7-4EFEA5A5788F}' failed due to the following error: No such interface supported 
(Exception from HRESULT: 0x80004002 (E_NOINTERFACE))..	server=SERVEUR;package=Maintenance Plans\System Database Maintenance Plan;	1	1

I have absolutely no idea what that means! And Google was unable to help me…

Can anyone here help me with either problem?

Thanks very much,

John

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[mrdenny]

The error from SSMS is because the client is running SP3 but the database engine isn't, so things are going to be funky.

You should find some local groups created on the SQL Server with those domain names. Make sure that NT Authority\System is listed in those groups, or change the SQL Server from running under the system account to a domain account, and add the domain account to those groups.

It is best practice to not run the SQL Server under the local system account for security reasons.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[reporting]

Thanks very much Denny. I took a look at the server and was unable to find these groups:
DOMAIN-NAME\SQLServer2005SQLAgentUser$SERVEUR$MSSQLSERVER

DOMAIN-NAME\SQLServer2005MSOLAPUser$SERVEUR$MSSQLSERVER



In Local Groups, I found the following groups:
SQLServer2005DTSUser$SERVEUR - "NT AUTHORITY\NETWORK SERVICE (S-1-5-20)" only

SQLServer2005MSSQLServerADHelperUser$SERVEUR - "NT AUTHORITY\NETWORK SERVICE (S-1-5-20)" only

SQLServer2005MSSQLUser$SERVEUR$BKUPEXEC - "NT AUTHORITY\SYSTEM (S-1-5-18)" only

SQLServer2005NotificationServicesUser$SERVEUR - no users in group

SQLServer2005ReportingServicesWebServiceUser$SERVEUR$MSSQLSERVER - "NT AUTHORITY\NETWORK SERVICE (S-1-5-20)" only

SQLServer2005ReportServerUser$SERVEUR$MSSQLSERVER - "NT AUTHORITY\SYSTEM (S-1-5-18)" only

SQLServer2005SQLBrowserUser$SERVEUR - "NT AUTHORITY\SYSTEM (S-1-5-18)" only



When I saw that the SQLServer2005DTSUser$SERVEUR local group had "NT AUTHORITY\NETWORK SERVICE (S-1-5-20)" only, I decided to see if I was able to add NT AUTHORITY\SYSTEM in order to add it to the group. I was unable to find it in "Select Computers, Users and Groups" with Users and Groups selected.



FYI, in SQL Server Management Studio (Security/Logins), I found the following listed:
DOMAIN-NAME\SQLServer2005MSFTEUser$SERVEUR$MSSQLSERVER

DOMAIN-NAME\SQLServer2005MSSQLUser$SERVEUR$MSSQLSERVER

DOMAIN-NAME\SQLServer2005SQLAgentUser$SERVEUR$MSSQLSERVER

NT AUTHORITY\NETWORK SERVICE

NT AUTHORITY\SYSTEM


You suggested "Make sure that NT Authority\System is listed in those groups, or change the SQL Server from running under the system account to a domain account, and add the domain account to those groups."

In Services, I found the following SQL Server services:
SQL Server (BKUPEXEC)
SQL Server (MAXIMIZER)
SQL Server (MSSQLSERVER)
SQL Server Active Directory Helper
SQL Server Agent (MSSQLSERVER)
SQL Server Analysis Services (MSSQLSERVER)
SQL Server Browser
SQL Server FullText Search (MSSQLSERVER)
SQL Server Integration Services
SQL Server Reporting Services (MSSQLSERVER)

Can you please tell me:

1. Can I use the Domain Administrator account?
2. If not, I will create a SQLServerService account and assign it the proper rights. Can you please tell me what rights are required?
3. Which of the above services need to run under the domain account.

Thanks again,

John


John Marrett
Crystal Reports Trainer & Consultant
Maximizer CRM Trainer & Consultant

http://www.e-reporting.ca

 

[mrdenny]

While you can use the Domain Admin account, it is highly recommended not to. Create a new user and grant it the right to run as a service. If you make the change via the SQL Server Configuration Manager it will grant the account the correct rights on the local server.

Based on the logins that you found in Management Studio you will want to find those groups on the domain (search for them, they could be anywhere) and add the domain login that you created to those groups.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog