INF: Enabling DLL-based COM Object Execution Outside SQL Server

ID: Q198891

The information in this article applies to:

Microsoft SQL Server versions 6.5, 7.0

SUMMARY

SQL Server 6.5 and later provides the capability of loading and executing custom Component Object Model (COM) objects through a set of OLE Automation stored procedures or through extended stored procedures. DLL-based COM objects are by default loaded as an in process server, which means the COM object(s) are not only loaded within the SQL Server process memory address space, but have full access to this memory address space. Therefore, a COM object loaded in the SQL Server process space must adhere to the same rules as any DLL file. There is a potential that a COM object could overwrite memory within the SQL Server process or leak resources, causing instability. If there is suspicion that a COM object(s) may be affecting the robustness of the SQL Server process, you may want to use the steps in this article to instantiate the COM object outside the SQL Server process space. Implementation of the Distributed Component Object Model's (DCOM) specification of "Location Transparency" into the operating system has provided the ability to execute a DLL-based COM object outside the SQL Server process space.

The COM process of running the DLL outside of the SQL Server process is called Remoting. Remoting requires that another executable be a surrogate process in place of the SQL Server executable. The default executable used by the DCOM Service Control Manager (Rpcss.exe) is named Dllhost.exe. The DCOM support structure uses the Dllhost.exe file to load the DLL into its process space and then uses proxy/stub pairs to marshal the requested interface(s) transparently back to the client, which in this case is the SQL Server. This executable can accept multiple interface/method requests concurrently. After the interface use is complete, the DCOM Service Control Manager (SCM) manages the clean up and unloading of the Dllhost.exe file. COM objects should not be expected to retain state information in between instantiations.

In order for this article to work correctly, the system must be running a DCOM enabled operating system. This would be either Windows NT 4.0 Service Pack Two (or higher), Windows 98 or Windows 95 with the DCOM add-in installed in the case of SQL Server version 7.0 Desktop Edition. The following steps can apply to any DLL-based COM object that is being created in the SQL Server process space, whether it is being instantiated through sp_OACreate or an extended stored procedure.

MORE INFORMATION

Modify COM Client to Instantiate the COM object Externally

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

You can update the COM object(s) creation to inform DCOM to create the object outside of the SQL Server process space within SQL Server with one of the following two methods:

The OLE Automation stored procedure, sp_OACreate has a third parameter. This parameter is used to indicate the context of where to create the object and that needs to be explicitly passed for the context. If the third context parameter is not passed, the default setting of five (5) is used, which means to run the object either inside or outside of the process. This needs to be changed to four (4), which tells DCOM that this component is to run as a local executable. Use the following sample syntax to explicitly inform DCOM to run the COM object 'out of process' using the sp_OACreate stored procedure:
```
   DECLARE @object int
   DECLARE @hr int
   EXEC @hr = sp_OACreate 'SQLOLE.SQLServer', @object OUT, 4 
```

If the COM object is created within an extended stored procedure the third parameter of CoCreateInstance or CoCreateInstanceEx can be changed to CLSCTX_LOCAL_SERVER. This is shown in the following code sample using CoCreateInstance:
```
   HRESULT hr = CoCreateInstance(CLSID_Test, NULL, CLSCTX_LOCAL_SERVER,
     IID_IUnknown, (void**)&piunknown); 
```

Putting DCOM Information in Place for Remoting of the DLL COM Object

Obtain the Class Identifier (CLSID) of the COM object. The CLSID is a 128-bit number and considered a Globally Unique Identifier or GUID that is used to uniquely identify the component, module or file that contains this COM object. When creating COM objects using the OLE Automation stored procedures, the first parameter to the stored procedure is a programmatic identifier or the ProgID of the OLE object is used to derive the CLSID. This character string describes the class of the OLE object and has the following form:
```
      OLTEComponent.Object 
```
The programmatic identifier can be used to find the class identifier for a COM object. Open the Registry Editor (Regedit.exe) and under the HKEY_CLASSES_ROOT key use the Find facility to locate a key with the name of your <OLEComponent.Object>. You will find it at other levels, but it should be located at the level directly below the HKEY_CLASSES_ROOT. Once located, click the plus sign in front of the key name and you should see a subkey called CLSID. Click that folder to see the values within that key. On the right-hand side of the screen is a title named "(Default)". The data for that key should be in the following form:
```
      {59F929A0-74D8-11D2-8CBC-08005A390B09} 
```
Make a note of this value or copy it to Notepad, and include the brackets.

Navigate under the HKEY_CLASSES_ROOT\CLSID key and find the subkey with this GUID number. Once you highlight the HKEY_CLASSES_ROOT\CLSID key you can use the Registry Editor's Find function under the Edit menu and paste the GUID into the Find dialog box. Ensure that you have found the proper interface by inspecting the InprocServer32 subkey below this key which points to the location of your COM DLL file. If there is a TypeLib key, check this GUID value. This should be different than what you noted in step 2. Otherwise, you have the TypeLib GUID and not the GUID for the COM object. The ProgID subkey will have a value of 'OLEComponent.Object.1'. The one on the end is for this sample only and is used for versioning information.

While under the GUID's InprocServer32 subkey, make sure that a ThreadingModel value exists and it is set to either Both or Free to make sure the marshaling understands the threading model of the COM object to enable execution of COM out of SQL Server process space. If there is no ThreadingModel value or it is set to Apartment, COM object instantiation may not be consistent.

NOTE: If you add the ThreadingModel value make sure that you test your COM object before implementing. If was no ThreadingModel value, then setting this to Both and testing is recommended.

Highlight the GUID number/subkey under the HKEY_CLASSES_ROOT\CLSID key. From the Edit menu, choose New and then select String Value. Under the Name column, type the following:


      AppID

Press the enter key then insert the class identifier or GUID number you noted from step 2 as the value. The GUID should be inside the curly brackets as in the following example:
```
 
      {59F929A0-74D8-11D2-8CBC-08005A390B09} 
 
```
The application identifier AppID is used by DCOM to associate the DLL with an executable file.

Add a new subkey under the HKEY_CLASSES_ROOT\AppID and set its name to the same class identifier or GUID number with the brackets as inserted in the preceding step.

Highlight the GUID name and again from the Edit menu, choose New and then select String Value. Under the Name column, type the following:
```
       DllSurrogate 
```
Leave the Data column blank for this value. Because the data column is blank, this informs DCOM to run the default executable file, Dllhost.exe and load the COM object within its process space.

Close the Registry Editor and from the Start button, choose Run. In the Run dialog box type the following:
```
      DCOMCNFG 
```
Press the ENTER key to open the Distributed COM Configuration Properties dialog box. Click the Default Properties tab, and make sure that Enable Distributed COM on this computer is selected. If it is not, select it and click the Apply button to enable.

Make sure that the Windows NT user account that SQL Server is running under has "Full Control" permission on the registry keys for this object. If the permissions are not sufficient or the registry keys are input incorrectly the following errors can be raised when creating the COM object.

OLE Automation Error Information
HRESULT: 0x80040154
Source: ODSOLE Extended Procedure
Description: Class not registered

OLE Automation Error Information
HRESULT: 0x80070005
Source: ODSOLE Extended Procedure
Description: Access is denied.

OLE Automation Error Information
HRESULT: 0x80080005
Source: ODSOLE Extended Procedure
Description: Server execution failed

Test and see if this is executing the Dllhost.exe file and loading the COM object in its process space. This requires that the Windows NT Resource Kit is on the Windows NT computer on which the SQL Server is running. Open a command prompt and from there run the Tlist.exe file, which shows all the processes and their associated process identifiers, or Process Identifiers (PID)s. In the T-SQL script where the sp_OACreate is run and after that call is executed, but before the script ends, use the following to delay the script completion for an additional 20 seconds:
```
WAITFOR DELAY '000:00:20' 
```
Run the script and immediately go to the command prompt and run the Tlist.exe file and note the Dllhost.exe PID. Re-execute the Tlist.exe, passing the PID as a parameter. This shows the DLLs that are loaded within the Dllhost.exe process space. The DLL-based COM object should be listed as running within this process. Once the script returns, re-executing Tlist.exe reveals that the Dllhost.exe process is no longer running.

In the following sample output the ADODB.Connection object is created outside of the SQL Server process space. This snapshot using Tlist.exe was performed while the COM object existed in the Dllhost.exe process space. Notice that the module Msado15.dll, which is the module that contains the COM object, is loaded.
```
C:\>tlist dllhost
 275 dllhost.exe
   CWD:     C:\NT40\system32\ 
   CmdLine: C:\NT40\System32\dllhost.exe {00000514-0000-0010-8000-00AA006D2EA4}
-Embedding
   VirtualSize:    19180 KB   PeakVirtualSize:    19180 KB
   WorkingSetSize:  1780 KB   PeakWorkingSetSize:  1780 KB
   NumberOfThreads: 3
    278 Win32StartAddr:0x01001920 LastErr:0x00000000 State:Waiting
    215 Win32StartAddr:0x00001b5e LastErr:0x00000000 State:Waiting
    253 Win32StartAddr:0x00001b60 LastErr:0x000000cb State:Waiting
   4.0.1381.105 shp  0x01000000  dllhost.exe
   4.0.1381.130 shp  0x77f60000  ntdll.dll
   4.0.1381.121 shp  0x77dc0000  ADVAPI32.dll
   4.0.1381.133 shp  0x77f00000  KERNEL32.dll
   4.0.1381.133 shp  0x77e70000  USER32.dll
   4.0.1381.115 shp  0x77ed0000  GDI32.dll
   4.0.1381.131 shp  0x77e10000  RPCRT4.dll
   4.0.1381.117 shp  0x77b20000  ole32.dll
     6.0.8267.0 shp  0x78000000  MSVCRT.dll
                     0x1f310000  msado15.dll
    2.30.4265.1 shp  0x766f0000  OLEAUT32.dll
    4.0.1381.72 shp  0x77bf0000  rpcltc1.dll 
```
With the SQL Server version 7.0 Desktop Edition running on Windows 95 or Windows 98 workstations the "32-bit Modules Loaded" within the Microsoft System Information application tool can be used during the execution to see the loading\unloading of the Dllhost.exe file and the COM object DLL during this test. This tool is accessed by clicking the Start button, pointing to Programs, pointing to Accessories and then selecting System Tools.

An alternative method to editing the Registry directly as detailed in the preceding steps is to use the OLEView utility. OLEView that ships as one of the tools with Visual C++ can be used to set the registry settings to force the out of process COM object creation. Within OLEView, locate the ProgID in the form of OLEComponent.Object under All Objects. Select the COM object and from the Object menu, select the CoCreateInstance Flags and make sure only the CLSCTX_LOCAL_SERVER is selected. Next, under the Implementation and Inproc Server tabs select the Use Surrogate Process, leaving the "Path to Custom Surrogate" blank, which cause the use of Dllhost.exe to be loaded and the COM DLL brought within it's process space.

The OLE/COM Object Viewer utility is also available for download from the Microsoft Web site at the following URL:

http://www.microsoft.com/com

NOTE: Because of security limitations, Windows 95 or Windows 98 does not support starting a DLLSurrogate process and loading a COM DLL by a remote client. Therefore, the COM object must exist within the Running Object Table (ROT) and be running/loaded if it is to be available for use by a remote client computer. The steps in this article can be used to help isolate COM objects when they are suspected to be the cause of instability in the SQL Server. Make sure that each component is tested thoroughly running out of process to ensure consistent behavior. Performance difference in instantiating a COM object in the SQL Server process and outside the process space varies. Also, some COM objects were not built to be Remoted and can leak resources. Test thoroughly before implementing the steps in this article for something other than a troubleshooting step. The following article is an example of remoting a COM object that can cause a resource leak:

Q197426 BUG: Handle Leak when Passing ADO Objects Between Processes

NOTE: SQL Server 6.5, by default, operates with Single Thread Apartment (STA) model and handles initialization of COM objects on a separate internal thread. In this model, one thread is selected to control the creation of all the OLE objects within the SQL Server process and to proxy back to all client connections needing access to this COM object. Because this is handled internally by the SQL Server, the object's persistence cannot be guaranteed between instantiations of the COM object.

REFERENCES

Further information on the SQL Server 6.5 COM Object model and the way the Sp_OA stored procedure is implemented can be found in the following article in the Microsoft Knowledge Base:

Q194661: SQL Server COM Object Persistence Model

Q180780: How Sp_OA Procedures Extension to SQL Server Is Implemented

Further reading on running DLL-based COM object(s) within DLL Surrogates can be found in the following sources:

Eddon, Guy; Eddon Henry, Inside Distributed Com (Mps). Microsoft Press, 1998, (ISBN 1-57231-849-X), Chapter Eight: 'DLL Surrogates and Executable Components'

Box, Don, Essential COM. Addison-Wesley Pub. Co., (ISBN 0-201-63446-5) Chapter Six: 'Applications' Grimes, Richard, Professional DCOM Programming. Wrox Press Inc. (ISBN 1-861000-60-X), Chapter Four: 'Distributed Component Object Model' Various articles and whitepapers may be found at the following Web URLs:

http://www.microsoft.com/com

http://www.msdn.microsoft.com

The DCOM Add-In for Windows 95 is shipped with the SQL Server 7.0 media and the file is named Dcom95.exe. The file can also be downloaded from: the www.microsoft.com/oledev website.

Additional query words: ActiveX, DCE, RPC, ATL, COM+, MTS, memory, resource, leak


Keywords          : kbole SSrvProg SSrvStProc SSrvTran_SQL SSrvVisB kbSQLServ650 kbSQLServ700 
Version           : winnt:6.5,7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: June 9, 1999