GUID mapping table wrong

[PatrickOnGit]

The guids for extended rights stored in are wrong.

You may update using the following process:

# Correct Extended Rights GUIDs

# Update AD_Attributes to support overlapping GUIDs between AD attributes and rights attribute
# The current table gets renamed, a new table with updated setting is created, data copied and old one removed.
 
Invoke-SqliteQuery -Connection $conn -Query @"
PRAGMA foreign_keys=off;
 
BEGIN TRANSACTION;
 
ALTER TABLE AD_Attributes RENAME TO oldAD_Attributes;
 
CREATE TABLE "AD_Attributes" (
    "OBJ_Name"    VARCHAR NOT NULL UNIQUE,
    "OBJ_guid"    VARCHAR NOT NULL,
    "OBJ_adtype"    VARCHAR NOT NULL,
    PRIMARY KEY("OBJ_adtype","OBJ_Name"),
    UNIQUE("OBJ_adtype","OBJ_guid")
);
 
INSERT INTO AD_Attributes
SELECT * FROM oldAD_Attributes;
 
DROP TABLE oldAD_Attributes;
 
COMMIT;
 
PRAGMA foreign_keys=on;
"@
# Cleanup database after deleting a table
Invoke-SqliteQuery -Connection $conn -Query "VACUUM"


# Gather data in Forest with Exchange schema
$RootDSE = Get-ADRootDSE
Get-ADObject -SearchBase $RootDSE.configurationNamingContext -SearchScope Subtree -Filter { ObjectClass -eq 'ControlAccessRight' } `
    -Properties rightsGuid -PipelineVariable ADObj | 
    Export-Csv C:\Temp\ExtendedRightsGuidMapping.csv -NoTypeInformation

# Load discovered Data and check how many need to be updated
(Import-Csv  C:\Temp\ExtendedRightsGuidMapping.csv -PipelineVariable ADObj |
%{ Invoke-SqliteQuery -Connection $conn -Query "Select OBJ_Name,OBJ_guid FROM AD_Attributes WHERE OBJ_Name = '$($_.Name)' and OBJ_guid <> '$($_.rightsGUID)' and OBJ_adtype = 'controlAccessRight'"  | Select *, @{n="ADGuid";e={ $ADObj.rightsGuid }} } ).count

( Invoke-SqliteQuery -Connection $conn -Query "Select * FROM AD_Attributes where OBJ_adtype = 'controlAccessRight'" ).count

# update GUIDs
# Note: one error is expected The following share the same GUID
#  'DNS-Host-Name-Attributes' - '72e39547-7b18-11d1-adef-00c04fd8d5cd'
#  'Validated-DNS-Host-Name' - '72e39547-7b18-11d1-adef-00c04fd8d5cd'
Import-Csv  C:\Temp\ExtendedRightsGuidMapping.csv -PipelineVariable ADObj |
%{ Write-Host "Updating '$($_.name)' - '$($_.rightsGUID)'"; Invoke-SqliteQuery -Connection $conn -Query "UPDATE AD_Attributes SET OBJ_guid = '$($_.rightsGUID)' WHERE OBJ_Name = '$($_.Name)' and OBJ_guid <> '$($_.rightsGUID)' and OBJ_adtype = 'controlAccessRight'" }

# Load discovered Data and check again how many need to be updated. This time nothing should  return
(Import-Csv  C:\Temp\ExtendedRightsGuidMapping.csv -PipelineVariable ADObj |
%{ Invoke-SqliteQuery -Connection $conn -Query "Select OBJ_Name,OBJ_guid FROM AD_Attributes WHERE OBJ_Name = '$($_.Name)' and OBJ_guid <> '$($_.rightsGUID)' and OBJ_adtype = 'controlAccessRight'"  | Select *, @{n="ADGuid";e={ $ADObj.rightsGuid }} } ).count

# Set schema version to 'current' date (yyyymmdd) this version is now for 2022-07-19
Invoke-SqliteQuery -Connection $conn -Query "PRAGMA user_version = 20220719"

# verify schema version
Invoke-SqliteQuery -Connection $conn -Query "PRAGMA user_version"

$conn.close()

[merddyin]

Thanks...I have identified a couple of spots that need some touch ups. It's actually an odd occurrence as all of these GUIDs were pulled from a live environment and verified against others. I have some code that isn't yet committed that will fix the main GUIDs that are consistent based on the schema definition files, and to dynamically grab the ones that are not static.

[PatrickOnGit]

Thank you for your feedback.
The mistake actually was that schema guids are stored as base64 encoded binary guids but extended rights guids as string. So you have to take this into account when exporting it (one needs to be decoded and one must be exported as is).
And there is another twist. There are a few overlapping guids. Simply looking up guids may result in the wrong information returned.
An example of this:

#  'DNS-Host-Name-Attributes' - '72e39547-7b18-11d1-adef-00c04fd8d5cd'
#  'Validated-DNS-Host-Name' - '72e39547-7b18-11d1-adef-00c04fd8d5cd'

So you have to change indexing of the table and combine OBJ_guid and OBJ_adtype to ensure uniqueness.

PRAGMA foreign_keys=off;
 
BEGIN TRANSACTION;
 
ALTER TABLE AD_Attributes RENAME TO oldAD_Attributes;
 
CREATE TABLE "AD_Attributes" (
    "OBJ_Name"    VARCHAR NOT NULL UNIQUE,
    "OBJ_guid"    VARCHAR NOT NULL,
    "OBJ_adtype"    VARCHAR NOT NULL,
    PRIMARY KEY("OBJ_adtype","OBJ_Name"),
    UNIQUE("OBJ_adtype","OBJ_guid")
);
 
INSERT INTO AD_Attributes
SELECT * FROM oldAD_Attributes;
 
DROP TABLE oldAD_Attributes;
 
COMMIT;
 
PRAGMA foreign_keys=on;

[merddyin]

Will double check that. This table is actually only used for two purposes; as a foreign key to ensure valid values are selected for task group mapping, and to set the actual ACLs related to a given object. When setting the ACLs, I only use the friendly name as a lookup, via the hash table post import, as the ACLs need the GUIDs...so long as the correct GUID is returned when I perform the lookup, it should be mostly ok. The challenge I ran into however, is that things like the attributes used by the MS LAPS solution use dynamic generation of the GUID at the time the schema is extended. This is against Microsoft guidelines for authoring of schema extensions, but I have found several other cases where MS internal teams decided to ignore that.

I actually have a to-do item to create an initial load function that will validate the values in the DB and update if needed. The current working copy of the code has work arounds for the LAPS attributes specifically. Incidentally, there is also an update in the code logic required to support object creation and deletion that I need to finish, which is due to the changes MS made in their December 2022 security update. The ACLs now have to be set slightly differently, and there is a bug when deleting computer objects for Azure IaaS systems, which have child objects. Always more to do than time in the day lol.