Troubleshooting SQL Express errors in Recovery Manager for AD (4368921)

Troubleshooting SQL Express errors in Recovery Manager for AD

The three most common issues relating to SQL Express are noted in the following KB Articles:

Failed to initialize reporter: Cannot open database requested by the login. Login Failed
Failed to initialize reporter: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
How to grant SysAdmin rights when Recovery Manager for AD is using a SQL Express instance

If after trying the resolutions from the above three KB articles, try updating the DB Provider as per Method 3 in the Resolution.
​​​​
To identify the actual error, run the RMAD installer with full logging enabled to capture the verbose installation log.
eg. msiexec.exe /i "C:\RMADInstaller\Setup\RMAD_x64.msi" /L*V "C:\TEMP\RMADInstall.log"

Method 1 - Add user to DBOwner Role using SQL Management Studio

To grant users db_owner rights to the database you need to use SQL Server Management Studio, and have sufficient rights on the SQL server to create new logins and grant them rights to the database. You can grant db_owner rights to individual users or create a group and grant db_owner to the group:

1. Open SQL Server Management Studio and connect to the SQL server where the RMAD database is hosted
2. Expand Security
3. Right click Logins and select New Login
4. Click Search
5. Click Locations and change to your domain
6. If adding a group click Object Types and check Groups
7. Enter the name of the user or group and click Check Names
8. Click OK
9. Click User Mapping
10. Check the RMAD database (RecoveryManager-Reporting-<RMADServerName>)
11. Check db_owner in the bottom window and click OK

NOTE: If the error occurs during an install/upgrade to 10.2.2 it is also required to add db_creator also at step 11


Method 2 - Add Local Administrators group to DBOwner role

Check the key below to determine the local SQLExpress InstanceName (by default this is QUESTRMAD)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

Download the script attached to the following KB article and when prompted for an instance name, supply the named instance determined from Registry setting (Usually QUESTRMAD)
How to grant SysAdmin rights when Recovery Manager for AD is using a SQL Express instance

Method 3 - Update OLEDB Provider used by Recover Manager for AD

By default, RMAD uses the SQLNCL10 provider. This can cause issues if the provider has not been patched to support TLS 1.2. To change the provider, you need to create the following registry key and add the value detailed below as REG_SZ (string value) on the RMAD server:
Key: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Quest\Recovery Manager for Active Directory\DBReporting
Value Name: Provider
Value (REG_SZ): SQLNCLI11

Alternatively, and run this on the RMAD server and this will perform the steps above programmatically.

To execute the script:

1. Download and extract the PowerShell script Quest-SetOLEDBProvider-SQLNCL11.ps1
2. Launch an administrative command shell
3. Launch PowerShell ("PowerShell.exe")
4. Run the script by typing "./Quest-SetOLEDBProvider-SQLNCL11.ps1" at the PowerShell prompt and pressing enter

Method 4 - Ensure rights and permissions for Windows User Account are correct

Problems can occur when you install or upgrade Microsoft SQL Server after you tighten security by removing default rights and permissions. This can manifest itself as a "SQL_SRV_INSTALL_ERROR = 4" within the Windows installer logfile.

Follow the instructions in the Microsoft Article below and check especially for the removal of the following rights from the local administrator:

• Backup files and directories
• Debug Programs
• Manage auditing and security log

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/installation-fails-if-remove-user-right

Method 5 - Alternative method for installing SQL related components included with RMAD

This can manifest itself as a "SQL_SRV_INSTALL_ERROR = 4" within the Windows installer logfile when adding the following components - "Forest Recovery Persistence", "Reports" and "Recovery Manager Hybrid Restore".

Click To See Full Image.

Rather than running the setup via Windows Explorer, run the following command line install from an administrative command prompt ensuring UAC is temporarily turned off:

Msiexec /i "C:\RMADUpgrade\RMADFE_x64.MSI" /l*!+v C:\RMADUpgrade\Manual.Install.log

The command above should execute the installer with super-verbose logging enabled. Please also remember to amend the path to the MSI file accordingly for your own environment.

Prior to re-running the setup, also ensure that the target SQL Server does not already contain the databases required which may have been left behind from a failed installation. The database names are "ForestRecovery-Persistence", RecoveryManager-Reporting-COMPUTERNAME", "RecoveryMgrHybridRestore".