The announcement by Microsoft that Microsoft BitLocker Administration and Monitoring (MBAM) was slated for deprecation raised a lot of concern from many customers who rely upon MBAM to manage BitLocker drive encryption. In response to customer feedback Microsoft extended the lifetime of MBAM and committed to providing the same features and functionally in to System Center Configuration Manager (SCCM).

Starting in technical preview version 1905, you could use SCCM to install and manage the Microsoft BitLocker Administration and Monitoring (MBAM) client however this feature did not make the 1906 production release. The 1909 Technical Preview has introduced improvements to BitLocker management including integrated reports, a helpdesk portal for administration and monitoring, and a self-service portal for users.

One of the main concerns with moving BitLocker compliance data from the MBAM database to the SCCM database is retention time. As discussed in this whitepaper published by Microsoft, as a best practice your organization should have an intentional stance around the longevity and guardianship of BitLocker recovery keys that will outlast the present administration. Again, quoting the same white paper:

 As a legally bound entity, your organization is subject to data retention policies for the kinds of data you collect, generate and store. Often this concept applies to on-line or near-line      storage, data sets that the company has ‘on the books’. For the data you know about, you legally comply with the requirements around it.

Disk Volume Encryption introduces a high-order accountability/liability, Retainable Data Recoverability Retention, that applies to the data you may have written ‘off the books’. Your ability to show or accomplish future compliance on past data depends on your policy now around your generations of recovery keys. Your organization must consider the implications that data, once thought lost or destroyed, may reappear long after the fact.

MBAM did provide the requirements listed above because MBAM never purges data from its database, however storing your compliance and recovery keys in the SCCM database alone cannot meet this requirement unless you store backup indefinitely. Even if you do store backups indefinitely recovering the data would be tedious and time consuming.

The SCCM data warehouse is a role that must be installed in SCCM. This role creates a data warehouse database where data is stored for 3 years. Periodically, a built-in task removes data that’s older than three years. The data warehouse supports up to 2 TB of data, with timestamps for change tracking. The data warehouse stores data by automatically synchronizing data from the Configuration Manager site database to the data warehouse database. This information is then accessible from your reporting service point. While this still doesn’t rise to the level of what was provided in MBAM it most definitely is an improvement over storing your compliance and recovery data in SCCM alone.

To ensure that your MABM data is stored in the data warehouse begin by installing the data warehouse using the guidance from Microsoft that can be found here.

This blog assumes you’ve not yet installed the administration and monitoring or self-service portals. If you have already installed them, you can remove them from IIS or you can install a second set of portals on another server. The data warehouse only syncs once per day which means that a computer that is less than 24 hours old would not yet be in the data warehouse.

For this reason, installing a second set of portals on another server may be a good idea. (I have tested sync’ing the data warehouse more than once per day in my lab using a scheduled task and a script, I’m not posting that here because I don’t know how well it would perform under the load of a production environment.) These portals being the ones that will connect to the data warehouse may be left unknown to your users and only used in cases where BitLocker data has been purged from the SCCM database and the ports connected to the SCCM database would be the ones that are published to the Help Desk and end users.

Next follow the guidance here to use SCCM to install and manage the Microsoft BitLocker Administration and Monitoring (MBAM) client.

After a short period of time you should confirm that data from BitLocker is in the SCCM database. I used the following SQL query to see the data.

select * from MBAM_POLICY_DATA

select * from  RecoveryAndHardwareCore_Keys

select * from  RecoveryAndHardwareCore_Machine_Types

select * from  RecoveryAndHardwareCore_Machines_Users

select * from  RecoveryAndHardwareCore_Machines_Volumes

There are some other tables that seem interesting but contain no data in my lab. You may want to look at these in your environment.

select * from ENCRYPTABLE_VOLUME_DATA

select * from MBAM_MACHINE_DATA

select * from MBAM_VOLUME_DATA

select * from RecoveryAndHardwareCore_Domains

select * from RecoveryAndHardwareCore_ExemptionStatus

select * from RecoveryAndHardwareCore_Machines

select * from  RecoveryAndHardwareCore_Users

select * from RecoveryAndHardwareCore_Volumes

select * from RecoveryAndHardwareCore_Volumes_Users

select * from  RecoveryAndHardwareCore_VolumeTypes

After you’ve confirmed that data has been populated in the tables in the SCCM database, using the queries above, you will need to add these tables to the data warehouse. This step does directly edit the SCCM database. If you are not comfortable doing so, stop here and contact Microsoft support! With that disclaimer I should note that it is now possible to add some tables to the data warehouse using the SCCM console, however, not all tables can be added in this way.  Directly editing the SCCM database was the only way to add tables to the data warehouse prior to the addition of the current, limited functionality, in the UI. The SQL queries method were provided to me by Microsoft.

Run the following SQL query against your SCCM database and Copy the result from the query.

select Value2 from SC_SysResUse_Property where name like '%includedtables%'

Create a statement like the one below however be certain that you include anything returned in the results from the previous statement in the values along with the tables names above that returned data in your environment. In my lab the statement was this:

UPDATE SC_SysResUse_Property

set Value2 =

'ClientSettings

,ENCRYPTABLE_VOLUME_DATA,MBAM_MACHINE_DATA

,MBAM_VOLUME_DATA

,MBAM_POLICY_DATA

,RecoveryAndHardwareCore_Domains

,RecoveryAndHardwareCore_ExemptionStatus

,RecoveryAndHardwareCore_Keys

,RecoveryAndHardwareCore_Machine_Types

,RecoveryAndHardwareCore_Machines

,RecoveryAndHardwareCore_Machines_Users

,RecoveryAndHardwareCore_Machines_Volumes

,RecoveryAndHardwareCore_Users

,RecoveryAndHardwareCore_Volumes

,RecoveryAndHardwareCore_Volumes_Users

,RecoveryAndHardwareCore_VolumeTypes'

where name = 'includedtables'

Run the following SQL query against your SCCM database to confirm that the tables have been added in the Value2 column.

select Value2 from SC_SysResUse_Property where name like ‘%includedtables%’

Open the configuration Manager console, Select Administration, Under Site Configuration select Servers and Site System Roles. Select the site system on which you installed the Data warehouse service point, right click and select properties. Select the Synchronization Settings tab. Click Synchronize now.

After some time run the following SQL query against your data warehouse database to confirm that the tables have been added and data has been populated into the tables. Substitute any table names from the sample query with those you added in the UPDATE statement above.

select * from MBAM_POLICY_DATA

select * from  RecoveryAndHardwareCore_Keys

select * from  RecoveryAndHardwareCore_Machine_Types

select * from  RecoveryAndHardwareCore_Machines_Users

select * from  RecoveryAndHardwareCore_Machines_Volumes

Once you confirmed that data has been populated in the data warehouse database proceed with the remaining steps below.

Follow the guidance here to install the Help Desk and Self Service websites substituting the data warehouse database name for the SCCM database name in the Powershell script below.

Alternatively: You could install the websites to the SCCM database and simply use SQL Management Studio to retrieve keys from computers that have been purged from the SCCM database. This could result in more escalated tickets depending upon your environment.

.\MBAMWebSiteInstaller.ps1 -SqlServerName <ServerName> -SqlInstanceName <InstanceName> -SqlDatabaseName <DatabaseName> -ReportWebServiceUrl <ReportWebServiceUrl> -HelpdeskUsersGroupName <DomainUserGroup> -HelpdeskAdminsGroupName <DomainUserGroup> -MbamReportUsersGroupName <DomainUserGroup> -SiteInstall Both

A simple way to confirm everything is working is to run the following SQL query aginast either the SCCM or the data warehouse database and copy one of the resulting RecoveryKeyId’s

select RecoveryKeyId from  RecoveryAndHardwareCore_Keys

On the server where you installed the Help Desk and Self Service websites open Internet Explorer and go to http://localhost/Selfservice and/or http://localhost/helpdesk Use the RecoveryKeyId copied above to test recovery.