Fixing Microsoft SCM 4

Fixing Microsoft SCM 4

Those who use Microsoft’s Security Compliance Manager intensively, might have noticed this product has a couple of problems, first, if you are customizing a baseline and try to add settings corresponding to an OS or product released after the release of SCM, you might find yourself with a message indicated 0 settings are available for such product, same you will face when trying to associate a baseline with a later released product. Finally every time you open SCM, you are requested to update Windows Server 2012 R2 baseline, no matter how many times you updated it, you will be prompted over and over. This post will help you to resolve the first two issues noted here.

As you might know already, SCM uses SQL Server Express 2008 as DB, so you will need SSMSE (SQL Server Management Studio Express) 2008 R2 in order to address this issue. Only basic SQL knowledge is required in order required to install SSMS and execute these queries.

While I first found these issues with SCM, started looking online for a solution, and came across with this post:

The post describes how to edit a stored procedure so you don’t get the “0 setting(s)” message while trying to associate a baseline with a given product, which is fine for issue number two, but not for number one. If you look in the comments section, a guy named TheHawk posted a query to be executed that will address issues number 1 and 2.

Unfortunately, the query described there had a couple of typos, which for me, not a SQL guy, were hard to identify, so here is the query corrected for you to copy and some extra queries that might be useful to you (I recommend you to read til the end of the post before you start running queries):

INSERT INTO PrePopulatedProductAndCceIDForSetting (SettingID,ProductID,"CCE-ID",ArrayOfOptionIdAndCceId)
    SELECT DISTINCT
        s.[OriginalSettingID],
        s.StartingFromProductID,
        (SELECT TOP 1 [CCE-ID] FROM Setting ts LEFT JOIN [CCE-ID_50] c ON ts.ProductID=c.ProductID AND ts.SettingID=c.SettingID
        WHERE ts.ProductID=s.ProductID AND ts.OriginalSettingID=s.OriginalSettingID AND [CCE-ID] IS NOT NULL
        ORDER BY [CCE-ID] DESC
        ) AS [CCE-ID],
        ''
        FROM [Setting] s
        WHERE ProductID='ffb630e8-b52d-40aa-b61e-9a5783599afd' AND StartingFromProductID!='00000000-0000-0000-0000-000000000000'

Replacing the ProductID value on line 13, which corresponds to the product that you might be interested in adding the settings for. In order to get the different products IDs you can run this simple query:

SELECT Name,ProductID FROM Products

If you want to list the products which have no settings added to the PrePopulatedProductAndCceIDForSetting table, you can execute the following query:

SELECT Name,ProductID FROM Products WHERE ProductID IN (
	SELECT t1.ProductID 
	FROM Products t1 
	LEFT JOIN PrePopulatedProductAndCceIDForSetting t2 
	ON t2.ProductID = t1.ProductID 
	WHERE t2.ProductID IS NULL
)

The problem with the former query is that it will only work one time, if a baseline published by Microsoft gets updated with new settings you will receive a ‘duplicated value’ message and the query won’t work. So if you want to add the settings for new baselines, or updated ones, a simple modification for the original query should do the trick:

INSERT INTO PrePopulatedProductAndCceIDForSetting (SettingID,ProductID,"CCE-ID",ArrayOfOptionIdAndCceId)
    SELECT DISTINCT
    settings.[OriginalSettingID],
    settings.[StartingFromProductID],
    ( SELECT TOP 1 [CCE-ID] 
      FROM Setting tSettings 
      LEFT JOIN [CCE-ID_50] ccid ON tSettings.[ProductID]=ccid.[ProductID] AND tSettings.[SettingID]=ccid.[SettingID]
      WHERE tSettings.[ProductID]=settings.[ProductID] AND tSettings.[OriginalSettingID]=settings.[OriginalSettingID] AND [CCE-ID] IS NOT NULL
      ORDER BY [CCE-ID] DESC
     ) AS [CCE-ID],
    ''
    FROM [Setting] settings
    WHERE settings.[ProductID]='ffb630e8-b52d-40aa-b61e-9a5783599afd' AND settings.[StartingFromProductID]!='00000000-0000-0000-0000-000000000000' AND settings.[OriginalSettingID] NOT IN (SELECT SettingID FROM PrePopulatedProductAndCceIDForSetting WHERE ProductID = 'ffb630e8-b52d-40aa-b61e-9a5783599afd')

Note for this last query the Product ID needs to be modified in two different WHERE statements in line 13.

One important thing to mention is that this method will only work with settings existing in Microsoft published baselines, but in general terms that is enough in most of the cases.

Hope you find this useful.
Hernán J. Larrea

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.