Running Linked Server Out-of-proc

More
10 years 8 months ago #98 by Moderator
Moderator created the topic: Running Linked Server Out-of-proc
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 (x86)\\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 (x86)\\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 (x86)\\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 (x86)\\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.

Please Log in or Create an account to join the conversation.

More
6 years 1 month ago #10948 by peer1bi
peer1bi replied the topic: Re: Running Linked Server Out-of-proc
I just upgraded PGNP provider to 1.4 version, and I wanted to use out-of-roc option from my linked servers. I don't have an OLEView.exe file and I tried to run the script you gave for 64bit PGNP version. I saved it in .bat file and tried to run but unsuccessfully. Then I tried to run it line by line. After the first line ([HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}] ) I got this error message: The system cannot find the path specified. I got the same message after each line I ran. Also in your script, the PGNP64.dll file is in C:\\Program Files(x86)\\PGNP\\PGNP64.dll", but my file is in C:\\Program Files(x86)\\Intellisoft\\PGNP\\PGNP64.dll". Could that be the problem?

Please Log in or Create an account to join the conversation.

More
6 years 1 month ago #10949 by Moderator
Moderator replied the topic: Re: Running Linked Server Out-of-proc
Save script in a file with .reg extension. Double-click on it to execute.

Please Log in or Create an account to join the conversation.

More
6 years 1 month ago #10950 by peer1bi
peer1bi replied the topic: Re: Running Linked Server Out-of-proc
Thank you, I executed the file with .reg extension and it worked, but the linked servers don't. they work fine when I check back the "allow inprocess" option. However, when I uncheck it and try to run the query against linked servers I get this error message: Cannot create an instance of OLE DB provider "PGNP" for linked server 'server_name'. I checked the event viewer and there are no entries. I downloaded Process Explorer and checked the Handle of DLL substring for PGNP64.dll and it is sqlservr.exe 5792 DLL C:\Program Files (x86)\Intellisoft\PGNP\PGNP64.dll. This seams correct.
Just to make sure that the .reg file I ran has correct path, this is the file's contents:

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(x86)\\Intellisoft\\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(x86)\\Intellisoft\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both"

Please Log in or Create an account to join the conversation.

More
6 years 1 month ago #10954 by peer1bi
peer1bi replied the topic: Re: Running Linked Server Out-of-proc
Hi,
I fixed the previous issue, which was authentication, and my servers are now running out-of-proc. However, I am unable to execute any of my queries. Every time I try to run queries that grab data from linked servers I get the query result for may be a second, and then the message appears:
Msg 0, Level 11, State 0, Line 0
"A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."
Did any one else see this happening after moving PGNP to out-of-proc? Is there a way to fix this issue?

Thank you.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #11162 by Moderator
Moderator replied the topic: Re: Running Linked Server Out-of-proc
Hi,
somehow this question was marked as answered, but I cannot find the answer. If this is still an issue, would you e-mail more details to our Support address?

Thank you!

Please Log in or Create an account to join the conversation.

Time to create page: 0.082 seconds
Powered by Kunena Forum