How to Disable COM Components

Question

How to Disable COM Components

Kenneth Igiri

SSCertifiable

Points: 5001
More actions
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

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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

Kenneth Igiri SSCertifiable Points: 5001 More actions · Answer 2

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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

Kenneth Igiri SSCertifiable Points: 5001 More actions · Answer 4

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

Kenneth Igiri SSCertifiable Points: 5001 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

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)