SQLOLEDB reports an error when having Data Warehouse in a cluster server and Data Mart non-clustered (NETIQKB49080)

SQLOLEDB reports an error when having Data Warehouse in a cluster server and Data Mart in a non-cluster server.

Error: 'The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.'

You must allow communication between the servers by enabling Network DTC Access and removing RPC Security. Since changes to security require rebooting your machine, plan a time when you are able to reboot.

To enable Network DTC Access, verify the settings are correct on both servers:

1. Click Start > Administrative Tools > Component Services.
2. Expand My Computer.
3. Right-click My Computer in the right pane.
4. Click Properties.
5. Select the MSDTC tab.
6. Click Security Configuration.
7. Verify that Network DTC Access is selected:
   a. Select Allow Remote Administration under Client and Administration.
   c. Select Allow Inbound under Transaction Manager Communication.
   c. Select Allow Outbound under Transaction Manager Communication.
   d. Select Enable XA Transactions.
8. Click OK.

To remove RPC Security, according to Microsoft KB article 555017, perform the following steps on the Data Mart machine:

1. Click Start> Run.
2. Type Regedt32 and click OK.
3. Select HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft.
4. Right-click MSDTC, select NewDWORD Value.
5. Rename the key from the default New Value #1 to TurnOffRpcSecurity
6. Double-click the new key and change the value to 1 
7. Close the Registry Editor.
8. Restart the SQL Server service to enable DTC communication.

Warning : Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. NetIQ Technical Support cannot guarantee that problems resulting from the incorrect use of Registry Editor can be resolved. Make sure that you backup your Registry prior to making any changes.

RPC Security for MSDTC Service on SQL Server is enabled and prevents communication between the Data Warehouse and Data Mart when they are on separate servers.

Formerly known as NETIQKB49080

Consult with your IT Supervisor/Manager before making any modifications to the configuration of a domain controller because changes may produce adverse effects within your environment. NetIQ Technical Support cannot be held responsible for any problems that may arise from these changes.