Monday, November 17, 2014

SCCM 2012x: Microsoft SQL Server Report Builder Error: Unable To Connect To Data Source

Never thought I would say this, but I really do like SCCM 2012x. Yes, I reckon it has its quirks for sure, but SCCM 2012x can do TONS of good stuff for any organization and safe so much time. Also the integration with SQL Server Reporting Services (SSRS) is magic.

Issue
However, when trying to build some customized Reports for SCCM 2012x, one is bound to bump into this error, when Microsoft SQL Server Report Builder is run from any other server than the SQL Server hosting the SCCM 2012x database: Microsoft SQL Server Report Builder Error: Unable To Connect To Data Source. The Certificate Chain Was Issued By An Authority That Is Not Trusted:
image

Cause
When SCCM 2012x is installed, a self signed Certificate is automatically created on the SQL server hosting the SCCM 2012x database. This certificate is used for communication with the SCCM 2012x database and required by any other system connecting directly to this database.

Solution
The solution is simple and straight forward. I write it down in an high level overview since I expect you to have enough experience to fill in the ‘blanks’ Smile.

  1. On the SQL Server hosting the SCCM 2012x database, start MMC > Certificates  > Local Computer. Browse to the Personal store and there you’ll find the certificate ConfigMgr SQL Server Identification Certificate. When in doubt simply check the last tab, Certification Path:
    image
  2. Export it (no Private Key required);
  3. On the computer experiencing this issue, open MMC > Certificates > Local Computer. Browse to the Trusted Root Certification Authorities store and import the previously exported certificate.
  4. Now Report Builder will work as intended.

No comments: