Friday, September 27, 2013

SQL Server 2012 Integration Services and DCOM Permissions

On my development machine, I noticed I was getting the same DistributedCOM error 28 times every 15 minutes.
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.
I'm not sure it is possible for this error to be any more cryptic. Luckily, I know a little about DCOM permissions. Roughly translated, there is a process calling a particular COM+ server (also known as a component). When that happens, the process fails to create/initialize the component because of a permissions error.

Debugging the Error

Based on the error, I know a couple of things. I know the process is a Windows service. Only Windows services can run under the Network Service (also known as "NT Authority\Network Service") user. I also know the GUID listed is the CLSID (also known as the Class ID) for the component.

Now, all COM security is configured from a little utility named dcomcnfg.exe. More specifically, the permissions I need to deal with are handled under the "DCOM Config" branch of the tree displayed in dcomcnfg.
Console Root -> Component Services -> Computers -> My Computer -> DCOM Config

So, a quick look at the listed components; and the CLSID can't be found. That's okay. Components can be listed in a variety of ways. The CLSID is only used as a last ditch effort if a more readable name is not stored for the component. To find the name for the component, I need to look it up in the registry under HKCR\CLSID, which gives me something much more useful.

Now I know that the COM+ server is named Microsoft.SqlServer.Dts.Server.DtsServer, and I know that this is definitely related to SQL Server - Data Transformation Services by the name. Switching back to dcomcnfg, the Microsoft.SqlServer.Dts.Server.DtsServer component is not listed either.

What else can the component be named? The registry key also displays an AppID GUID in addition to the component name. This represents the name of the process that hosts the component. Similarly to a component name, I can look up the application name in the registry under HKCR\AppID.

The process listed here is a Windows Service named MsDtsServer110, which is the short name for the SQL Server Integration Services 11.0 service. Switching back to dcomcnfg, the SQL Server Integration Services 11.0 component is listed. Perfect. Now all I have to do is configure it.

Fixing the Permissions

The steps listed here are very specific to my error but can easily be adapted to whatever component you need by replacing SQL Server Integration Services 11.0 with the appropriate application name, AppID or CLSID and replacing Network Service with the appropriate Windows user.
  1. In dcomcnfg, right-click on SQL Server Integration Services 11.0 and click on Properties.

  2. Change to the Security tab.
  3. Under Launch and Activation Permissions select Customize.

  4. Click [Edit] to display the Launch and Activation Permissions window.

  5. Click [Add] to open the Select Users or Groups window.

  6. Enter Network Service.
  7. Click [Check Names].
  8. Click [OK] to close the Select Users or Groups window.

  9. Select NETWORK SERVICE in the Group or user names list.
  10. Check the Local Launch and Local Activation permissions.
  11. Click [OK] to close the Launch and Activation Permissions window.
The error should now be resolved. For me, all I have to do is wait 15 minutes to see if the error is logged again. If your error can be duplicated another way, repeat those steps to see if you are still getting the error.

How did I get here?

Perhaps more important than all of this is, "why did I get this error in the first place?" The answer to that, in my case, is that I installed SQL Server 2012 and selected Network Service as the logon account for all the Windows services created by the installer. This is contrary to recommended practices, especially since the SQL Server 2012 installer has the ability to configure Windows accounts specifically for use by the Windows services it creates.

The short answer is that I shot myself in the foot because I didn't follow recommended security practices for the installation of SQL Server. Still, I would have thought that DCOM permissions would have been automatically configured for these components upon installation.

Regardless, it was relatively easy to fix, as long as I knew what DCOM was and how to deal with it.

Update (2013-09-28): Found another useful article for Troubleshooting DCOM and thought I would share.