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