Sql-server – SQL 2008 Reporting Service SSL certificate chain not trusted

Most likely you need to re-register the SSRS WMI Namespace and/or the WMI Provider. You can troubleshoot and fix the problem using the following steps.

Troubleshoot the problem using the WMI Tester

1. Close all instances of Reporting Services Configuration Manager or SQL Server Management Studio.
2. Open a command prompt and type wbemtest, and then click OK.
3. Click Connect in the WBEM tester dialog box
4. In the Namespace field, type the following string, and then click Connect. root\microsoft\sqlserver\reportserver \v9
5. The namespace you specify should appear in the WMI Tester dialog box. If you see an error message instead, the WMI namespace for Reporting Services is not registered correctly. To resolve this issue, follow the procedures later in this article for registering the WMI namespace for Reporting Services.
6. Click Enum Classes, and click OK in the Superclass Info window.
7. In Top-level classes list of the Query Result window, double-click the MSReportServer_Instance class.
8. Click the Instances button. You should see a Query Result window that shows all the instances of Report Server on your computer. If not, the Reporting Services WMI provider is not communicating with Reporting Services Configuration Manager or SQL Server Management Studio. To resolve this issue, follow the procedures later in this article for registering the Reporting Services WMI provider.
9. If you successfully resolved the issues in the WMI Tester dialog box, restart Reporting Services Configuration Manager or SQL Server Management Studio to determine whether that the error is resolved.

Repairing Report Services WMI Provider

1. Close all instances of Reporting Services Configuration Manager or SQL Server Management Studio.
2. Open a command prompt.
3. Add the class instance of the Reporting Services WMI provider to the WMI repository. In the command prompt, run the following command:
   mofcomp "%ProgramFiles%\Microsoft SQL Server\90\Shared\reportingservices.mof"
4. Run the WMI Tester again to see whether you can now connect to the namespace for the Reporting Services WMI provider and continue with the debugging process.

Register the Reporting Services WMI provider

1. Close all instances of Reporting Services Configuration Manager or SQL Server Management Studio.
2. Open a command prompt.
3. Register the Reporting Services WMI provider. In the command prompt, run the following command:
   regsvr32 "C:\Program Files\Microsoft SQL Server\90\Shared\reportingserviceswmi.dll
4. Run the WMI Tester again to see whether you can now connect to the namespace for the Reporting Services WMI provider and continue with the debugging process.

Here is the source for this information, Microsoft's How-To on troubleshooting WMI Provider errors in SSRS.

If I understand the MSDN spec correctly, all you need is to specify in the connection string Encrypt=True;TrustServerCertificate=True. This means that the client requests encryption and is willing to accept any certificate the server may use. The server always has a self-signed certificate generated at server startup time to use, if nothing else is available. If the client is willing to accept any certificate, then it will accept that server's temporary self-signed one, is just as good as any.

What such a setup provides is an encrypted communication channel between your application and your server, a channel that cannot be ear dropped with ease. However, the channel is open to a malicious man-in-the middle attack. If an attacker can fool the client to connect to him instead of the server (eg. by having control of the DNS records, more exactly the DNS server IP the client will use, which is a trivial DHCP setting to control) then the attacker can present any certificate and the client will accept it, it can then do the full authentication round-trip with the client, thus obtaining the SQL username and password used, then it can connect to the true server and forward back and forth all the communication, with a free look at all the content. The client will never know is being 'monitored'. This is the 'man-in-the-middle' attack.

To prevent the situation above, the client must remove the TrustServerCertificate=True from the connection string. Once this is done though, the certificate used by the server has to be trusted by the client, and this is when all the complications arise. If you are OK with a weaker setting on which you have an encrypted traffic but you understand that you may be subject to a man-in-the-middle attack and you are OK with it, then use the much simpler TrustServerCertificate=True setting. If not, then unfortunately you must really understand what you're doing and is not trivial. If the data is so important, then perhaps shelling out the moneys for a VeriSign, Thawte or GlobalSign (these being the 3 roots trusted by every Windows client) certificate for your server (~$500/year) is not so outlandish.