FIM Troubleshooting: CM Upgrade Fails exception executing SQL statement

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
Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Steve Light - MSFT edited Revision 4. Comment: Fixed script

  • Peter Geelen - MSFT edited Revision 3. Comment: Fixed colors and headings

  • Tim Macaulay edited Revision 2. Comment: title update, tags update

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Tim Macaulay edited Revision 2. Comment: title update, tags update

  • Peter Geelen - MSFT edited Revision 3. Comment: Fixed colors and headings

  • Steve Light - MSFT edited Revision 4. Comment: Fixed script

Page 1 of 1 (3 items)