SCCM Data Warehouse Reports Error – Workaround

In my previous post, I showed the steps to install Data Warehouse service point role. At the end of same post, i mentioned about an error while accessing Data Warehouse reports. In this post I will present a workaround for SCCM data warehouse reports error. First of all, let me tell you that this is a known issue and is already documented here.

When you install Data Warehouse role, you will notice an error when you open any Data Warehouse reports. This is a known issue and even I experienced the same in my lab setup. Since the error can be fixed using a simple workaround, we will see how to do that.

SCCM Data Warehouse Reports Error

When you install Data Warehouse service point role, under reports you will find several data warehouse reports.

SCCM Data Warehouse Reports ErrorHowever when you try to run any report, you will see the below error.

An error has occurred during report processing. (rsProcessingAborted). Cannot create a connection to data source ‘AutoGen__39B693BB_524B_47DF_9FDB_9000C3118E82_’. (rsErrorOpeningConnection). A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0). The certificate chain was issued by an authority that is not trusted.)

SCCM Data Warehouse Reports Error

Workaround for SCCM Data Warehouse Reports Error

Following are the steps that are part of this workaround to fix the SCCM Data Warehouse reports error.

Step 1 – Create Self-Signed Certificate

You must perform the below steps on the computer that hosts the data warehouse database.

  • Open IIS.
  • Click Server Certificates.
  • On the right pane, right-click and then click Create Self-Signed Certificate.

SCCM Data Warehouse Reports Error

In this step specify a friendly name for the Certificate. You can specify it as “Data Warehouse SQL Server Identification Certificate”. Select the certificate store as Personal. Click OK.

SCCM Data Warehouse Reports Error

Step 2 – Assign Data Warehouse SQL Server Identification Certificate

  • Launch SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration.
  • Right-click Protocols for MSSQLSERVER and select Properties.
  • Under Protocols for MSSQLSERVER properties, click Certificate tab.
  • Select Data Warehouse SQL Server Identification Certificate as the certificate.
  • Click Apply and OK.

SCCM Data Warehouse Reports Error

In SQL Server Configuration Manager, click SQL Server Services. Restart the SQL Server service and SQL Server Reporting Service.

SCCM Data Warehouse Reports Error

Step 3 – Export the Data Warehouse SQL Server Identification Certificate

On the computer where you hosted the Data Warehouse DB, perform the below steps.

  • Open the Microsoft Management Console (MMC).
  • Add the Certificates snap-in.
  • Select Computer account of the local machine.
  • Expand the Personal folder, and select Certificates.
  • Right click and export the Data Warehouse SQL Server Identification Certificate.

SCCM Data Warehouse Reports Error

On the certificate export wizard welcome page, click Next.

SCCM Data Warehouse Reports Error

Select No, do not export the private key. Click Next.

SCCM Data Warehouse Reports Error

Select the format as DER encoded binary X.509 (.CER). Click Next.

SCCM Data Warehouse Reports Error

Now click Browse and save a certificate with a name. Click Next.

SCCM Data Warehouse Reports Error

We have successfully exported the certificate. Click Finish.

SCCM Data Warehouse Reports Error

Step 4 – Import Data Warehouse SQL Server Identification Certificate

Very Important – Perform the below steps on the computer that hosts SQL Server Reporting Services.

  • Open the MMC.
  • Add the Certificates snap-in.
  • Select Computer account.
  • Expand Trusted Root Certificate Authorities folder.
  • Right click Certificates > All Tasks > Import.

SCCM Data Warehouse Reports Error

Click Next on Certificate import wizard.

SCCM Data Warehouse Reports Error

Select the certificate that you exported in Step 3. Click Next.

SCCM Data Warehouse Reports Error

Ensure the certification store is Trusted Root Certification Authorities. Click Next.

SCCM Data Warehouse Reports Error

Click Finish.

SCCM Data Warehouse Reports Error

Run the Data Warehouse reports now and there should be no error displayed.

SCCM Data Warehouse Reports Error

You might also like

2
Leave a Reply

avatar
1 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
newest oldest most voted
Alban
Guest
Alban

Thanks for these informations.
For my part I had the same error, I just edited via the reportbuilder one of the reports and did a database check (in success) after that I was able to launch the report and the other reports (that I did not edit).

Alex
Guest
Alex

This is Interesting, Alban. I just read this since I need to do a workaround where the server hosting my SCCM DW database does not have IIS installed. Can you share the steps you made for this?

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. AcceptRead More