Post reply

How to Disable COM Components

  • March 22, 2012 at 5:28 pm

    #253079

    Hi All. I keep seeing the following line in all Security Best Practices documnets:

    •Disable COM components once all COM components have been converted to SQL CLR.

    How can I disable COM Components?

    How can I know COM Components have been converted to SQLCLR.

    I am thinking this does not mean I should disable CLR itself.

    Please help.

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • March 23, 2012 at 9:45 am

    #1463505

    kennethigiri (3/22/2012)

    Hi All. I keep seeing the following line in all Security Best Practices documnets:

    •Disable COM components once all COM components have been converted to SQL CLR.

    How can I disable COM Components?

    How can I know COM Components have been converted to SQLCLR.

    I am thinking this does not mean I should disable CLR itself.

    Please help.

    This is referring to code objects you wrote yourself. For example, let's say you have deployed a custom Extended Stored Procedure (XSP) implemented as a C++ COM object that made use of native Windows API calls to write data passed into the XSP to a file on a remote file system. The recommendation here is to convert the XSP to a custom SQLCLR Stored Procedure that did the same thing, but using Managed .NET Framework classes instead of an unmanaged C++ COM object.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 23, 2012 at 10:03 am

    #1463532

    Thanks so much SSCrazy. You are the best.

    Assuming I was not the person who implemented the instance, how can I fin ou whether there are any COM components?

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • March 23, 2012 at 10:59 am

    #1463597

    Here is a way to find all XSPs in a user-database that were not delivered by Microsoft as part of the base installation of SQL Server:

    SELECT *

    FROM sys.objects

    WHERE OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1

    AND is_ms_shipped = 0;

    Note the query must be run in each user-database separately as sys.objects is a catalog view, not a system view.

    Another item to beware of when it comes to COM object discovery is the usage of them via the OLE Automation procs (i.e. procs with a prefix of sp_OA). You can check to see if OLE Automation is enabled on your instance using this query:

    SELECT value,

    value_in_use

    FROM sys.configurations

    WHERE name = N'Ole Automation Procedures';

    If the value_in_use column is 1 then you'll need to scan all database code looking for places where an sp_OA proc was used. If the value column is 1 and value_in_use column is 0 then OLE Automation will be enabled after the next instance restart, an odd state for your instance to be in, but one to look for nonetheless.

    PS My handle is opc.three, SSCrazy, while fitting at times, is just my designation based on how many points I have earned on this site.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 24, 2012 at 5:35 am

    #1478040

    opc.three (3/23/2012)

    Here is a way to find all XSPs in a user-database that were not delivered by Microsoft as part of the base installation of SQL Server:

    SELECT *

    FROM sys.objects

    WHERE OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1

    AND is_ms_shipped = 0;

    Note the query must be run in each user-database separately as sys.objects is a catalog view, not a system view.

    Another item to beware of when it comes to COM object discovery is the usage of them via the OLE Automation procs (i.e. procs with a prefix of sp_OA). You can check to see if OLE Automation is enabled on your instance using this query:

    SELECT value,

    value_in_use

    FROM sys.configurations

    WHERE name = N'Ole Automation Procedures';

    If the value_in_use column is 1 then you'll need to scan all database code looking for places where an sp_OA proc was used. If the value column is 1 and value_in_use column is 0 then OLE Automation will be enabled after the next instance restart, an odd state for your instance to be in, but one to look for nonetheless.

    PS My handle is opc.three, SSCrazy, while fitting at times, is just my designation based on how many points I have earned on this site.

    Thanks so much for this opc.three.

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • May 6, 2012 at 2:16 pm

    #1483924

    Sorry I have to come back to this. I do not have to any none-microsoft COM components but I do have OLE Automation enabled. Do I have to disabled it to met the security recomendation?

    How do I scan DBs to see whether sp_OACreate and other such SPs are beig used?

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • May 6, 2012 at 8:52 pm

    #1483947

    kennethigiri (5/6/2012)

    How do I scan DBs to see whether sp_OACreate and other such SPs are beig used?

    This will scan a single DB.

    SELECT OBJECT_NAME(object_id)

    FROM sys.sql_modules

    WHERE Definition LIKE '%sp__oa%' ESCAPE '_'

    ;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply