July 10, 2007 at 8:41 am
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,[098] SQLServerAgent terminated (normally)
07/06/2007 20:46:18,,Error,[298] SQLServer Error: 15247<c/> User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
07/06/2007 20:46:18,,Error,[000] Error creating a new session
07/06/2007 20:46:18,,Error,[298] 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,[298] 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,[298] 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
{ABF05265-635E-44B0-A28F-AEA45247ACA0}
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?
July 10, 2007 at 1:29 pm
If, instead of searching for 'MSDTSServer', you search for MS DTS Server, you will have much greater success.
As to why only one plan failed, it may have to do with the way it was configured initially, the way the duplicate IDs were resolved, or the nature of the plan itself. It could be that only this plan involved DTS Server, where the conflict arose. Or only this one plan didn't get the proper mapping - this could have been a network admin's oversight (aka ooopsie) - it was resolved for all but this one before you were involved.
No answer for your last question from me... Good luck going back and recreating the sequence of events (if you want to?), to determine what happened and what went wrong. I think it was probably an honest, human error.
May 29, 2008 at 3:38 am
MsDtsServer is the DCOM application name for: Microsoft.SqlServer.Dts.Server.DtsServer
better known to its friends as CLSID ABF05265-635E-44B0-A28F-AEA45247ACA0
You can discover this by searching for the CLSID in the registry.
I have a similar permissions problem. Whenever a Maintenance Plan runs to backup a database or log, I get 2 DCOM errors, Event ID 10016, which is the same as the error you posted.
I can see how to fix the permissions, for example a nice explanation at:
http://www.cleverworkarounds.com/2007/10/25/dcom-fun-with-sharepoint/
In my current, standard set up, only Administrators and SYSTEM have "Local Launch" permissions. Adding local launch permissions to the domain user listed in the Event ID, solves the problem. My SQL Server and SQL Server Agent services both run under the same domain user account, so I am unsure which service requires the DCOM permissions. [Perhaps this is another reason to run the 2 services under different credentials.]
Is this a normal problem for an MSSQL Instance which runs under the credentials of a non-local-administrator?
I am also curious as to what exactly is going on in the background with DCOM. The Maintenance Plans complete successfully, with no errors. The only error is logged in the System Event Log. If the Maintenace Plan completes successfully, what is the DCOM local launch error related to?
The backups are being saved to a NAS, but the NAS drive is presented to the OS as a local drive, so I can't imagine that RPC with DCOM is required. Also, files are written correctly.... Any ideas?
Thanks,
Andy
January 6, 2016 at 6:40 am
Seems like the problem arises when a new service account is given permissions / added on the server. The DTS is an admin function and occasionally there are reasons why the service account should not have admin on the box. In cases like this, they may need extended permission in the registry.
There are five hives to be concerned with
HKEY_CLASSES_ROOT\CLSID\{FDC3723D-1588-4BA3-92D4-42C430735D7D}
and
HKEY_CLASSES_ROOT\Microsoft.SqlServer.Dts.Server.DtsServer
and
HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{FDC3723D-1588-4BA3-92D4-42C430735D7D}
and
HKLM\SOFTWARE\Classes\CLSIDand
HKLM\SOFTWARE\Classes\Microsoft.SqlServer.Dts.Server.DtsServer\CLSID
The remaining permissions will autofill (if you choose to do this through the registry).
And easier method might just be to add the service account to the group:
SQLServer2005SQLBrowserUser$<servername>
I haven't tried it this way but it should work in theory.
And another method might be by enabling the local sec policy for that user on :
Log on as a service (SeServiceLogonRight)
Bypass traverse checking (SeChangeNotifyPrivilege)
Impersonate a client after authentication (SeImpersonatePrivilege)
There is also some needed configuration for the Local DTC Properties. If you go to the COM Services and open the Distributed Transaction Coordinator to the Local DTC and check the properties|security tab, make sure the following are checked:
Network DTC Access (with Allow Remote Clients) and (Allow Inbound) and (Allow Outbound) with "No Authentication Required"
and (Enable SNA LU 6.2 Transactions)
The account here is generally the NT AUTHORITY\NetworkService but I suspect it can be changed to whatever is needed. This is probably an inherited right so your service account may need to be specifically entered but if so, I've never had an issue leaving it as is and service accounts run our systems.
Jamie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply