Distributed query involving external provider not supported when using Windows NT authentication (230576)

SYMPTOMS

Distributed queries are not supported when all of the following conditions are met:

The distributed query involves an external provider (one that runs out-of-process from the SQL Server).
The user issuing the distributed query connects to SQL Server using Windows NT authentication.
SQL Server has been started as a service.
The SQL Server service runs under the system account and is not allowed to interact with the desktop.

If all the preceding conditions are met, the distributed query fails with the following error:

dllhost.exe - DLL Initialization Failed
Initialization of the dynamic link library C:\WINNT\system32\USER32.dll failed.
The process is terminating abnormally.

CAUSE

This problem is a known limitation in COM with impersonating the client's security context when the provider is invoked out-of-process. Note that out-of-process is the default configuration for any external OLE DB provider.

WORKAROUND

Change the provider to be invoked in-process using the steps given below.

NOTE: Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. Only well-tested providers should be run in-process with SQL Server.

Under Linked Servers in the Enterprise Manager, highlight the provider and right-click Properties.
On the General tab, click the Options button.
Select the box beside Allow InProcess.
Click the OK button to close the Provider Options window.
Click the OK button to close the Linked Server Properties window.

In addition, the following workaround has been implemented for SQL Server 7.0 Service Pack 1:

Use sp_addlinkedsrvlogin to add a login mapping for the user to the remote server. Note that this remote login mapping creates a SQL Server login, so standard security will be used when accessing data from the remote server. Hence, this workaround can only be used by providers that support standard security.

MORE INFORMATION

We do not recommand that you run the SQL Server service under the local system account.

The sp_addlinkedsrvlogin stored procedure is used to add new linked accounts to remote servers. This information is stored in the sysxlogins table. The stored procedure requires the remote server name, the local user name, the remote user name, and the remote password as parameters. An extension has been developed for Windows 2000 Professional, which will provide security account delegation functionality. For more information, see the "Security Account Delegation" topic in SQL Server 7.0 Books Online.