SCM issue workaround: 0 unique settings from the GPO’s xxx unique settings apply to this product.

I came across a very irritating bug (at least it looks like a bug) in SCM 4.0. To be honest, I remember me seeing the same behavior in SCM 3.0 as well, but I didn’t have enough motivation to dig into details that time.

In a nutshell. I did back up of my Contoso Windows Server 2012 R2 Default Domain Controllers Policy, imported it in SCM 4.0 hoping to merge the settings with WS 2012 R2 Domain Controller Security Compliance baseline. Before merging you’re supposed to “associate” baseline with a product, and here’s the point where something went wrong.

I choose my policy, click Associate, search for Windows Server 2012 R2 and click it – bang! What do we see?

selectOS-GUI

Right, “Associate” button is not available! You can choose Windows Server 2012 (not R2), but the target platform you’d like associate with is not available. I’d spent some time on googling and searching within mailing lists without any success before ventured to investigate the problem myself.

We know that SCM uses WID database (named XTrans), so I decided to have a look in the database queries being passed to WID in course of work with SCM GUI. How can we do this? Right, SQL Profiler is our friend.

So, step 1 – what do we in see while we click on Windows Server 2012 R2 in the window above? There is a lot of queries like this one:
profiler-init

What do we get as a result of such kind of request when choosing Window Server 2012 R2?
selectOS-profiler
Too bad, no results.

And what happens if we choose Windows Server 2012 instead of Windows Server 2012 R2?
selectOSsuc-profiler

Voilà! We’ve got one result!

So, what is stored procedure we call here that gives us nothing in the first case?

Here it is:
SqlSP

Let’s query the target table PrePopulatedProductAndCceIDForSetting to get some insight on the data for different products (Windows Server 2012 and Windows Server 2012 R2) it contains.

We’d like to find some records related to Windows Server 2012 R2.
prepop

No results. Things get a bit clearer.

Settings for Windows Server 2012 R2 didn’t get into the PrePopulatedProductAndCceIDForSetting table. I don’t say they should have got there (the name of the table is ambiguous), but in this case something is wrong with the stored procedure that (seemingly – we will check it later on) drives decision – to let us to Associate or not – based on the data from PrePopulatedProductAndCceIDForSetting.

Let’s check our assumption and change the stored procedure a bit.

DISCLAIMER: This is, of course, NOT supported and NOT recommended. Do it on your own risk.

The point here is to change the stored procedure GetCcIdForSettingAndOptions so this would always return one record at least.


USE [XTrans]
GO

/****** Object: StoredProcedure [dbo].[GetCcIdForSettingAndOptions] Script Date: 01.08.2016 21:46:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetCcIdForSettingAndOptions]
@SettingID uniqueidentifier,
@ProductID uniqueidentifier
AS
BEGIN
SELECT [CCE-ID],[ArrayOfOptionIdAndCceId]
FROM [dbo].[PrePopulatedProductAndCceIDForSetting]
WHERE [dbo].[PrePopulatedProductAndCceIDForSetting].SettingID = @SettingID AND
[dbo].[PrePopulatedProductAndCceIDForSetting].ProductID = @ProductID
/* Modifications start here */
UNION
SELECT Cast('' as nvarchar(max)) as [CCE-ID],Cast('' as varchar(max)) as [ArrayOfOptionIdAndCceId]

/* Modifications end here*/
END

GO

We’ve changed the stored procedure, it’s time to check SCM again. We don’t even need to close and re-open it.
allok1

Yep! This pesky Associate button is back!
Let’s check baseline merging now:
allok2

Looks like everything is working, though we definitely need to test it more (I will for sure). Let me know in case you’ve proceed with this workaround and found it useful.

And, of course, let’s hope this long-living issue will be fixed soon.

Comments 9

  • Thanks A LOT for posting this resolution! I was able to successfully create the association because of your post.
    The only issue with executing the statement was that the single quotes are copied as double-quotes. Just need to delete the two double-quotes ” and replace with two single-quotes ” on this line: SELECT Cast(” as nvarchar(max)) as [CCE-ID],Cast(” as varchar(max)) as
    Thanks again!

    • Thanks, Matt! I’ve updated the code snippet so the code could be run without the need of replacing the quote characters.

  • I usеd to be recommended this web site bʏ my coսѕin. I am not sure
    whether this post is written through him as nobody else realize such
    special approximately my trouble. You’re incгеdible!
    Thank ʏou!

  • Ⲏmm is anyone else ɦaving problems with the pictᥙres on this blog loading?

    I’m tryіng to find out if its a problem on my end ߋr if it’s the blog.
    Any feedback would be greatly appreciated.

  • Thanks for this hint. After reading this I become interested about the real reason why the association doesn’t work. It seems the problem is that table PrePopulatedProductAndCceIDForSetting doesn’t contain any GPO settings for W2K12 R2.

    To have at least the settings available which are part of the baselines, you can run this SQL statement

    use [XTrans]

    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′

    Afterwards you can associate your baseline with W2K12 R2 and add new settings to it.

    • @TheHawk – THANK YOU!!! Finally, a script that fixes what should have been resolved before SCM 4.0 was released!

      There are other GPO settings missing from the PrePopulatedProducAndCceIDForSetting table (basically any updates not part of the base install). Your SQL statement resolves them as well by changing WHERE ProductID=’ffb630e8-b52d-40aa-b61e-9a5783599afd’ to the corresponding ProductID:

      Windows 8.1 – AB22A52C-46B8-4877-933B-62E2EAE276D5
      Windows 10 version 1511 – 06E6C6A2-BBFC-49EE-A776-9375B3D5A572
      Internet Explorer 11 – BDF24DB0-1BA5-40DB-8A93-E355E0AF256A
      Microsoft Office 2013 – 785D08B2-BDD1-4502-8091-073841C38ED3
      SQL Server 2012 – E6FB85B2-19DA-4895-A951-0E590C670AC5

  • So how do you get around this with other things? I have imported some group policies that I would like to export to an SCCM DCM cab, but can’t get past this associate issue. LAPS is a good example, I’d like to be able to check compliance that LAPS is enabled. It’s a very small GPO, only 4 settings, but I can’t associate it with anything to export it. Same applies to firewall rules and restricted groups. The association seems to set the applicability of the rules once it get into SCCM and up until the fix by @TheHawk most of what I needed worked with 2008 R2 SP1, then I would change the applicability to the appropriate OS once in SCCM. Do I neeeeed to associate it just to get it out of SCM into SCCM?

  • You cannot properly export after doing this workaround.

  • […] quick (though quite late) note about SCM as this blog post is viewed from time to […]

Leave a Reply

Your email address will not be published. Required fields are marked *