For those who have SSRS 2008 R2 DataCenter edition RTM, you might quickly run into this bug when you are configuring remote catalog using the configuration tool:

The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services.

We will get this addressed in the next CU (CU4 I believe). But thanks to a colleague, a workaround using WMI is available.

 

# 1. Generating Scripts:

 

$RptSrv = gwmi -Namespace "root\Microsoft\SqlServer\ReportServer\RS_MSSQLServer\v10\Admin" -Class "MSReportServer_ConfigurationSetting"

$Res = $RptSrv.GenerateDatabaseCreationScript("ReportServer", 0, 1)

$Res.Script | Set-Content <Some File location>

 

# 2. Run the Script in the DB Server

 

# 3. Grant Permission to the user.

$ret = $RptSrv.GenerateDatabaseRightsScript('redmond\uipqac1', 'ReportServer', 1,1)

$ret.Script | Set-Content <Some file location>

 

# Run the script on Report meta data server.

 

$RptSrv.SetDatabaseConnection('XXXXXX', 'ReportServer', 0, '<USer Account>', '<Password>')

 

 

# on the other nodes execute the below steps.

$RptSrv = gwmi -Namespace "root\Microsoft\SqlServer\ReportServer\RS_MSSQLServer\v10\Admin" -Class "MSReportServer_ConfigurationSetting"

$RptSrv.SetDatabaseConnection('XXXXXX', 'ReportServer', 0, '<USer Account>', '<Password>')