SQL Upgrade 2014 to 2016 (or newer)
If you are in need to upgrade your SQL from 2014 to 2016 or newer /having Bitlocker Keys encrypted
inside SQL DB, you need to change the certificate BEFORE this upgrade. Otherwise you may loose the
keys or the access to the keys.
Part I: Change certificate used for bitlocker
The procedure is described in our official documentation:
https://learn.microsoft.com/en-us/mem/configmgr/protect/deploy-use/bitlocker/encrypt-recovery-
data#manage-the-encryption-certificate-on-sql-server-upgrade
As the document is missing a few things, here are the complete steps to follow.
Be aware to use your own passwords 😊
1. On a SQL Server 2016+ create a new certificate
IF NOT EXISTS (SELECT name FROM sys.symmetric_keys WHERE name =
'##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<MyMasterKeyPassword>'
END
CREATE CERTIFICATE BitLockerManagement_CERT_Temp
WITH SUBJECT = 'BitLocker Management',
EXPIRY_DATE = '20391022'
2. Export this new certificate to a file
BACKUP CERTIFICATE BitLockerManagement_CERT_Temp TO FILE = 'C:\
BitLockerManagement_CERT'
WITH PRIVATE KEY (FILE = 'C:\BitLockerManagement_CERT_KEY',
ENCRYPTION BY PASSWORD = '<Your Password>')
3. Copy/move the files C:\BitLockerManagement_CERT & C:\BitLockerManagement_CERT_KEY into
c:\ on the SQL server which holds the ConfigMgr database.
4. Move the current certificate to a new name
a) Create a backup of the current certificate and drop it afterwards.
USE CM_ABC
BACKUP CERTIFICATE BitLockerManagement_CERT TO FILE = 'C:\BitLockerManagement_CERT_Old'
WITH PRIVATE KEY ( FILE = 'C:\BitLockerManagement_CERT_KEY_Old',
ENCRYPTION BY PASSWORD = '<Your Password>');
drop certificate BitLockerManagement_CERT;
b) Import this certificate now with a new name
CREATE CERTIFICATE BitLockerManagement_CERT_Old
FROM FILE = 'C:\BitLockerManagement_CERT_Old'
WITH PRIVATE KEY ( FILE = 'C:\BitLockerManagement_CERT_KEY_Old',
DECRYPTION BY PASSWORD = '<Your Password>')
5. Import the new certificate, which was created on a SQL Server 2016+
CREATE CERTIFICATE BitLockerManagement_CERT AUTHORIZATION RecoveryAndHardwareCore
FROM FILE = 'C:\BitLockerManagement_CERT'
WITH PRIVATE KEY ( FILE = 'C:\BitLockerManagement_CERT_KEY',
DECRYPTION BY PASSWORD = '<Your Password>')
GRANT CONTROL ON CERTIFICATE ::BitLockerManagement_CERT TO RecoveryAndHardwareRead
GRANT CONTROL ON CERTIFICATE ::BitLockerManagement_CERT TO RecoveryAndHardwareWrite
6. Create backup tables
USE CM_ABC
SELECT * INTO RecoveryAndHardwareCore_Keys_bkp FROM RecoveryAndHardwareCore_Keys;
SELECT * INTO RecoveryAndHardwareCore_Machines_bkp FROM
RecoveryAndHardwareCore_Machines;
7. Re-encrypt the data with the new certificate
DECLARE @RecoveryKey VARBINARY(8000), @RecoveryKeyPackage VARBINARY(8000),
@TpmPasswordHash VARBINARY(8000)
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#keys', 'U') IS NOT NULL DROP TABLE #keys;
SELECT Id, RecoveryKey, RecoveryKeyPackage INTO #keys FROM RecoveryAndHardwareCore_Keys
IF OBJECT_ID('tempdb.dbo.#tpm', 'U') IS NOT NULL DROP TABLE #tpm;
SELECT TpmPasswordHash INTO #tpm FROM RecoveryAndHardwareCore_Machines WHERE
TpmPasswordHash IS NOT NULL
WHILE (1=1)
BEGIN
IF NOT EXISTS (SELECT * FROM #keys) BREAK;
SET @RecoveryKey = (SELECT TOP 1 RecoveryKey from #keys ORDER BY Id)
SET @RecoveryKeyPackage = (SELECT TOP 1 RecoveryKeyPackage from #keys ORDER BY Id)
UPDATE RecoveryAndHardwareCore_Keys
SET RecoveryKey = RecoveryAndHardwareCore.RecryptKey('BitLockerManagement_CERT_old',
'BitLockerManagement_CERT', @RecoveryKey),
RecoveryKeyPackage = RecoveryAndHardwareCore.RecryptKey('BitLockerManagement_CERT_old',
'BitLockerManagement_CERT', @RecoveryKeyPackage)
WHERE RecoveryKey = @RecoveryKey AND RecoveryKeyPackage = @RecoveryKeyPackage
DELETE FROM #keys WHERE RecoveryKey = @RecoveryKey AND RecoveryKeyPackage =
@RecoveryKeyPackage
END
WHILE (1=1)
BEGIN
IF NOT EXISTS (SELECT * FROM #tpm) BREAK;
SET @TpmPasswordHash = (SELECT TOP 1 * FROM #tpm)
UPDATE RecoveryAndHardwareCore_Machines
SET TpmPasswordHash =
RecoveryAndHardwareCore.RecryptKey('BitLockerManagement_CERT_old',
'BitLockerManagement_CERT', @TpmPasswordHash)
WHERE TpmPasswordHash = @TpmPasswordHash
DELETE FROM #tpm WHERE TpmPasswordHash = @TpmPasswordHash
END
DROP TABLE #keys
DROP TABLE #tpm
SET NOCOUNT OFF
More information (optional)
In case the query to re-encrypt the database has been aborted and/or the initial data needs to be
restored, execute following query:
update j0
set TpmPasswordHash = j1.TpmPasswordHash
from RecoveryAndHardwareCore_Machines j0
join RecoveryAndHardwareCore_Machines_bkp j1 on j0.Id = j1.Id
update j0
set RecoveryKey = j1.RecoveryKey, RecoveryKeyPackage = j1.RecoveryKeyPackage
from RecoveryAndHardwareCore_Keys j0
join RecoveryAndHardwareCore_Keys_bkp j1 on j0.Id = j1.Id
Once confirmed that the re-encryption was successful, the backup tables created in step 6 can be
dropped:
drop table RecoveryAndHardwareCore_Keys_bkp;
drop table RecoveryAndHardwareCore_Machines_bkp
Part II: Upgrade SQL (here 2014 to any version later)
Following this detailed procedure:
SCCM SQL Server Upgrade from 2014 to 2017 (prajwaldesai.com)
Stop MECM services all the time before performing any described step (especially after a reboot)
- Uninstall Reporting services role from MECM
- Uninstall SQL Reporting Services (till 2014 it is an integrated part of SQL)
- Delete Reporting databases and logfiles
- Upgrade SQL incl. latest CU and Hotfix package (if any available)
Note the used serial as it is needed for SSRS installation later
- Install SQL reporting services (same version as upgraded SQL server version)
- Configure Reporting Services
- Install reporting services role in MECM
- Restart MECM services
Part III: Test
Take one of your existing systems, read the actual Recovery Key ID
(open administrative command prompt and run manage-bde -protectors -get c: to get this ID),
go to the helpdesk portal and use this ID to request the recovery key.
Check against what you can see from the above command.
Wait until the config manager agent has initiated the key change.
Part from Hardwarereplacement Testenvironment
SDEMUCA01844
Problem:
Bitlocker DB is SQl 2014 – target is SQL 2022
Bitlocker cert must be “upgraded” to 2016 and newer
1. On a SQL Server 2016+ create a new certificate
IF NOT EXISTS (SELECT name FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<F0rgotP@ssword>'
END
CREATE CERTIFICATE BitLockerManagement_CERT_Temp
WITH SUBJECT = 'BitLocker Management',
EXPIRY_DATE = '20391022'
-> E:\SCCM\Hardwarereplacement\2016_new_BitLockerManagement_CERT
2. Export this new certificate to a file
BACKUP CERTIFICATE BitLockerManagement_CERT_Temp TO FILE = 'C:\BitLockerManagement_CERT'
WITH PRIVATE KEY (FILE = 'C:\BitLockerManagement_CERT_KEY',
ENCRYPTION BY PASSWORD = '<F0rgotP@ssword>')
F0rgotP@ssword
3. Copy/move the files C:\BitLockerManagement_CERT & C:\BitLockerManagement_CERT_KEY
into c:\ on the SQL server which holds the ConfigMgr database.
4. Move the current certificate to a new name
backup old Key to BitLockerManagement_CERT_KEY_Old; drop key
USE CM_XZ3
BACKUP CERTIFICATE BitLockerManagement_CERT TO FILE = 'C:\BitLockerManagement_CERT_Old'
WITH PRIVATE KEY ( FILE = 'C:\BitLockerManagement_CERT_KEY_Old',
ENCRYPTION BY PASSWORD = '<F0rgotP@ssword>');
drop certificate BitLockerManagement_CERT
import old key to BitLockerManagement_CERT_KEY_Old
CREATE CERTIFICATE BitLockerManagement_CERT_Old
FROM FILE = 'C:\BitLockerManagement_CERT_Old'
WITH PRIVATE KEY ( FILE = 'C:\BitLockerManagement_CERT_KEY_Old',
DECRYPTION BY PASSWORD = '<F0rgotP@ssword>')
=>> Stop SCCM services
5. Import the new certificate, which was created on a SQL Server 2016+
CREATE CERTIFICATE BitLockerManagement_CERT AUTHORIZATION RecoveryAndHardwareCore
FROM FILE = 'C:\BitLockerManagement_CERT'
WITH PRIVATE KEY ( FILE = 'C:\BitLockerManagement_CERT_KEY',
DECRYPTION BY PASSWORD = '<F0rgotP@ssword>')
GRANT CONTROL ON CERTIFICATE ::BitLockerManagement_CERT TO RecoveryAndHardwareRead
GRANT CONTROL ON CERTIFICATE ::BitLockerManagement_CERT TO RecoveryAndHardwareWrite
Re-encrypt DB
Create backup tables
USE CM_XZ3
SELECT * INTO RecoveryAndHardwareCore_Keys_bkp FROM RecoveryAndHardwareCore_Keys;
SELECT * INTO RecoveryAndHardwareCore_Machines_bkp FROM RecoveryAndHardwareCore_Machines;
Re-encrypt the data with the new certificate
DECLARE @RecoveryKey VARBINARY(8000), @RecoveryKeyPackage VARBINARY(8000), @TpmPasswordHash
VARBINARY(8000)
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#keys', 'U') IS NOT NULL DROP TABLE #keys;
SELECT Id, RecoveryKey, RecoveryKeyPackage INTO #keys FROM RecoveryAndHardwareCore_Keys
IF OBJECT_ID('tempdb.dbo.#tpm', 'U') IS NOT NULL DROP TABLE #tpm;
SELECT TpmPasswordHash INTO #tpm FROM RecoveryAndHardwareCore_Machines WHERE TpmPasswordHash
IS NOT NULL
WHILE (1=1)
BEGIN
IF NOT EXISTS (SELECT * FROM #keys) BREAK;
SET @RecoveryKey = (SELECT TOP 1 RecoveryKey from #keys ORDER BY Id)
SET @RecoveryKeyPackage = (SELECT TOP 1 RecoveryKeyPackage from #keys ORDER BY Id)
UPDATE RecoveryAndHardwareCore_Keys
SET RecoveryKey = RecoveryAndHardwareCore.RecryptKey('BitLockerManagement_CERT_old',
'BitLockerManagement_CERT', @RecoveryKey),
RecoveryKeyPackage = RecoveryAndHardwareCore.RecryptKey('BitLockerManagement_CERT_old',
'BitLockerManagement_CERT', @RecoveryKeyPackage)
WHERE RecoveryKey = @RecoveryKey AND RecoveryKeyPackage = @RecoveryKeyPackage
DELETE FROM #keys WHERE RecoveryKey = @RecoveryKey AND RecoveryKeyPackage =
@RecoveryKeyPackage
END
WHILE (1=1)
BEGIN
IF NOT EXISTS (SELECT * FROM #tpm) BREAK;
SET @TpmPasswordHash = (SELECT TOP 1 * FROM #tpm)
UPDATE RecoveryAndHardwareCore_Machines
SET TpmPasswordHash = RecoveryAndHardwareCore.RecryptKey('BitLockerManagement_CERT_old',
'BitLockerManagement_CERT', @TpmPasswordHash)
WHERE TpmPasswordHash = @TpmPasswordHash
DELETE FROM #tpm WHERE TpmPasswordHash = @TpmPasswordHash
END
DROP TABLE #keys
DROP TABLE #tpm
SET NOCOUNT OFF
Temp table und old Certificate are still included in the DB. You can remove this later, when everything
is running smoothly.
Test the re-encryption script with a backup on another SQL instance!
I had some problems with this script, I asked Microsoft for a newer version.
