I have some question concerning a problem we had. Sorry for the size of this post, but I need to give some backgroud to the current situation.
My shop is currently running a number of virtual servers using VMWare. While many of these were copied from physical servers, a few were created as new servers. Due to a 'problem' with VMWare, these new servers were created with duplicate network SID numbers. Over the last weekend, July 7-8, Our network group changed the SIDs for these machines. Only four of these machines had SQL Server installed(and the DBAs were not informed of this work). Two of these were SQL Server 2000 and two were 2005.
After the SID was changed, the SQL Agent would not start on the 2005 boxes. The SQL Agent logs gave the following errors:
07/06/2007 20:46:18,,Information, SQLServerAgent terminated (normally)
07/06/2007 20:46:18,,Error, SQLServer Error: 15247<c/> User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
07/06/2007 20:46:18,,Error, Error creating a new session
07/06/2007 20:46:18,,Error, SQLServer Error: 229<c/> The INSERT permission was denied on the object 'syssessions'<c/> database 'msdb'<c/> schema 'dbo'. [SQLSTATE 42000]
07/06/2007 20:46:18,,Error, SQLServer Error: 229<c/> The EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info'<c/> database 'msdb'<c/> schema 'dbo'. [SQLSTATE 42000]
07/06/2007 20:46:18,,Error, SQLServer Error: 229<c/> The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access'<c/> database 'msdb'<c/> schema 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Event viewer gave the following message:
SQLServerAgent could not be started (reason: Error creating a new session).
After investigating the problem, it was determined that, even though the services for SQL Server were running under domain accounts, these account had been placed in the local groups that were created during the instalation proccess. Since these were local groups, the SID of the group in Windows was now different from the SID of the group as defined in SQL Server. We deleted the groups from SQL Server and recreated them, which placed the new SID into the syslogin table. Even though it was only the agent giving us problems, we deleted and recreated the groups for both the agent and SQL Server.
Last night, July 9, the maintenance plan job for backing up the system databases failed. When I checked the files, the backup files had been created, but the log entry in the LOG directory had not been written. The following message was found in the Event viewer for DCOM:
The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
to the user DDPOKDOM\SQLAgentService SID (S-1-5-21-520107568-192953301-624655392-6649). This security permission can be modified using the Component Services administrative tool.
After one of our network admins looked at the problem, he found that the permissions were missing for an app called MSDTSServer. After adding permissions, the job worked. The maintenance plan job for backing up the user databases did not fail.
My questions are:
What is MSDTSServer? I have looked in BOL and online through Google and cannot find any definition for this app.
Why did only one maintenace plan job fail?
Are there any other apps or components that we need to check for the change of the server SID?