Permissions needed to set up linked server with out-of-process provider

When setting up linked server to third-party Databases, it is recommended to run the third-party provider in out-of-process mode, because when the provider is run in-process (within the same process as SQL Server), then any issues with the provider can affect SQL Server process which could also result in crashing SQL server.

To how to set a provider to run out-of-process, uncheck "Allow In Process" property of the provider:

There are certain permissions that have to be set on MSDAINITIALIZE to be able to initialize the provider out-of-process and run linked server queries successfully locally and remotely.

MSDAINITIALIZE is a COM class that is provided by OLE DB. This class can parse OLE DB connection strings and load/initialize the provider based on property values in the connection string.

MSDAINITILIAZE is initiated by users connected to SQL Server. If windows authentication is used to connect to SQL Server, then the provider is initialized under the logged in user account. If the logged in user is a SQL login, then provider is initialized under SQL Server service account. Based on the type of login used, permissions on MSDAINITIALIZE have to be provided accordingly.

When these permissions are not set for the logged in users, we get Access Denied errors as below:

2. Click the Advanced button in the Permissions window and select the Owner tab. Under Change owner to select the local Administrators group and click on Apply/OK and then click Ok again.

3. Then under Permissions window, select the local Administrators group and under Permissions for Administrators select Full Control.

NOTE: DO NOT modify/change any permissions for the TrustedInstaller account.

4. Click on Apply or OK to make the changes effective.

5. Re-run the Computer Services management console (dcomcnfg.exe) and you should now be able to modify the settings for MSDAINITIALIZE package.

6. After making the necessary changes as mentioned above, reset the permissions for the above registry key in the registry settings back to its defaults:

- First make the account "NT SERVICE\TrustedInstaller" from the local computer the Owner of the key and then remove Full Control access for the Administrators group, and leave it with only Read access.

In the property of MSDAINITIALIZE. Only the General Tab has an entry. All other tabs (rather fields in them) are disabled.

The work around suggested needs so many tweaks one wondera whether it is worth. By the way the first screen you show is default and so are the dconfig's defaults.

John T

29 Oct 2014 11:33 AM

This is truly a great write-up - never once in my experience with data connections have I had to mess with MSDAINITIALIZE, so it's great to find a clear online resource which solves the issue.

I should note that Step 2 was unnecessary (as I discovered when the options were completely disabled). Skipping that and proceeding to implement Step 3 was sufficient to enable remote OLE DB connectivity.

fregatepallada

13 Jan 2015 3:53 PM

Unfortunately it did not work for me - 32-bit version of SQL Server 2014. It is amazing that vendor managed to break a standard functionality that worked for ages out of the box! I forced to abandon reading Excel files and read CSV one!