FIM Troubleshooting: CM Upgrade Fails exception executing SQL statement
ISSUE:
Installing a FIM CM R2 patch fails with the following dialog.
---------------------------
FIM Certificate Management
---------------------------
FIM CM Database upgrade failed with the following exception:
An error occurred: Problem while executing the SQL statement
--********************************************************
--* *
--* Copyright (C) Microsoft. All rights reserved. *
--* *
--********************************************************
/* Parameters: */
USE
GO
IF NOT EXISTS (
SELECT [column_name]
FROM INFORMATION_SCHEMA.columns
WHERE
[table_name] = 'CertificateAuthority'
AND [column_name] = 'ca_exit_module_version')
BEGIN
ALTER TABLE [CertificateAuthority]
ADD [ca_exit_module_version] VARCHAR(50) NULL
END
GO
A verbose MSI log indicates the following.
Executing op: CustomActionSchedule(Action=UpgradeDatabase,ActionType=1058,Source=C:\Program Files\Microsoft Forefront Identity Manager\2010\Certificate Management\Bin\Target="C:\Program Files\Microsoft Forefront Identity Manager\2010\Certificate Management\Bin\Microsoft.Clm.Config.exe" /DB,)
CustomAction UpgradeDatabase returned actual error code -5 (note this may not be 100% accurate if translation happened inside sandbox)
Note: 1: 1722 2: UpgradeDatabase 3: C:\Program Files\Microsoft Forefront Identity Manager\2010\Certificate Management\Bin\ 4: "C:\Program Files\Microsoft Forefront Identity Manager\2010\Certificate Management\Bin\Microsoft.Clm.Config.exe" /DB
A network trace showed no traffic from the CM server to the SQL server. A SQL Profiler trace corroborated this showing no activity from the CM server.
Findings
Some research indicated there may be something amiss with the SQL connection string in the registry. A PowerShell script converted the registry binary data to text. This text was added to the web.config file. After updating the web.config file the patch installation completed successfully.
Resolution
Add the connection string to the web.config file (details below).
Details
Sample DBConnectionString registry setting:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Clm\v1.0\Server\WebUser
DBConnectionString REG_BINARY 01000000D08C9DDF0115D1118C7A00C04FC297EB01000000EA385DBF07333B4887F97F08639DD0040400000002000000000003660000C00000001000000037BE31F2DB88ABACE3355A0B352031F60000000004800000A00000001000000057F97300F6F52206FFC9183E04F34F5E20010000EF95D0649EFB8B92841C703DC9B7CD0D5699D62D63DD956574236CDE5662F8410DBFEBD74E8138EB6562E37D464DA26C2F63F48AD5EA698133AC2D4000BC0D9E9085E162C78FBE3FA5349055C311FBDEB7A0474AF2CD5F455FC35F708CE0289387717B8A1466E2AF7E2E6167E573A52A082D7CC5DA3816A1125260159CD187159E764C9AA111953DCD7DC7AA6F46DA279E34FC186C5118F9BF9D0D32FD5A13B450AB0A9906F2520E7561F3210E916765459699E42D1FFA2C137BE911F9496A2ECE6F2EFFC8D8167F3F7A9E3450D4994E795D749FD32648A61740FCF2CB302A641685C8A8F138DEB373649007ADE1352D26A59DD5B532ADEDEA425EF741F4ECA6149E759B50149E588AE9F1ED4393427405617A189859F19CA0830923F8147718140000000454193E620BF88CA06C03DDF771F51293CFF538
Sample PowerShell script conversion of the DBConnectionString registry setting (formatting cleaned up for presentation):
Connect Timeout=15;Persist Security Info=True;Integrated Security=sspi;Initial Catalog=FIMCertificateManagement;Data Source=SQL01\FIM2010R2CM;
Sample web.config setting:
<!-- DATABASE SETTINGS ************************************************-->
<!-- Database Connection String~~~~~~~~~~~~~~~~~~-->
<add key="Clm.DataAccess.ConnectionString" value="Connect Timeout=15;Persist Security Info=True;Integrated Security=sspi;Initial Catalog=FIMCertificateManagement;Data Source=SQL01\FIM2010R2CM" />
Contents of PowerShell script:
Add-Type -assembly System.Security
Add-Type -assembly mscorlib
$reg = [wmiclass]'\\.\root\default:StdRegprov'
$HKLM = 2147483650 #HKEY_LOCAL_MACHINE
$key = "SOFTWARE\Microsoft\Clm\v1.0\Server\WebUser\"
$value = "DBConnectionString"
$connectionStringBinary = [byte[]]($reg.GetBinaryValue($HKLM, $key, $value).uvalue)
$connectionStringBytes = [System.Security.Cryptography.ProtectedData]::Unprotect($connectionStringBinary, $null, "LocalMachine")
$connectionStringString = [System.Text.Encoding]::Unicode.GetString($connectionStringBytes);
$connectionStringString