Running Linked Server Out-of-proc

[Moderator]

Several of our customers were asking how to configure Linked Server to PostgreSQL to run "out-of-proc", i.e. in a separate process. The advantage is that possible memory leaks and crashes in the PGNP provider won't affect SQL Server process since PGNP provider runs in a separate surrogate process (dllhost.exe).
Here are steps:
1. Register a surrogate process for PGNP provider to make it visible in DCOMCNFG utility. For this either run OleView.exe utility (part of Visual Studio or SDK) or apply registry script (see below).
In OleView expand "All Objects" in the treeview on the left and select "PostgreSQL Native Provider" object. Goto "Implementation" tab and check "Use Surrogate Process" option. In the edit box below specify path to dllhost.exe, for example: C:\WINDOWS\system32\dllhost.exe. Then select "Registry" tab and you will see that DllSurrogate named value was added under AppId key.
Following scripts can be executed instead of running OleView.

For 64bit PGNP provider:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_CLASSES_ROOT\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both"

For 32bit PGNP provider on 64bit Windows:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"

For 32bit PGNP provider on 32bit Windows:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\System32\\dllhost.exe"
[HKEY_CLASSES_ROOT\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\System32\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"

2. Run DCOMCNFG.exe utility. Expand "Component Services", "Computers", "My Computer" and select "DCOM Config" folder. In the right pane find application named "{3170DFF1-4803-42a0-A1B3-D14656857070}", right-click and select Properties. Configure Security and Identity in the corresponding tabs. Default values could work in many cases. Close the utility.

3. In SQL Server Management Studio uncheck "allow inprocess" option in PGNP provider properties. The changes are immediate and usually do not require restart of SQL Server or the computer.

Perform a query for the Linked Server. If issue occurs see Events Viewer for errors information. If a test query succeeded you can use Process Explorer (http://www.sysinternals.com) to find which surrogate process hosts the PGNP provider dll.