So you have just installed SCCM 2012 R2 and found the awesome power of SQL Server Reporting Services, and you have given your managers access to the web site to allow them to access the information that they keep asking you about.
After a while you get complaints that not all of the information that they need is available via the website and they want the ability to be able to create their own reports on the website. So you delegate them access to create reports from the console and you go there desk to explain how SQL report builder works and then you get the following error: “The Certificate chain was issued by an authority that is not trusted” or looks like this.
You probably received the same error on your computer but you lived with it because we can just make the change on the server and it works.
So the course of this issue is this little option inside the saved connection
Ok cool we have identified the why, can’t we just change the TrustServerCertificate to equal true, this does work, for about 5-10 minutes before the SCCM system checks run and revert the setting back.
So to resolve this we just need to export the “ConfigMgr SQL Server Identification Certificate” from the SCCM server and import it onto each of the computers that you plan to have access to update/modify reports.
To do this first we need to export the Certificate from the SCCM server which is a matter of running the MMC console on the SCCM server
And adding the local computer certificate snapin
And browsing to the Personal Certificate Store
And exporting the highlighted certificate as DER
Now on the report builder machine we need to import the certificate to the Current User
Into the Trusted Root Certificate Authorities
Hit yes on the Security Warning
And go back to Report Builder and everything will be fixed.
Now you can create reports to you hearts content.