SCCM: Unable to run Data Warehouse Reports from Remote SQL

Introduction

With the 1702 release of Configuration Manager, Microsoft released (pre-release) the Data Warehouse service point that can be used to report on long-term historical data. And with the 1706 release, this feature is no longer a pre-release feature.

Setting up the Data Warehouse feature is pretty much straight forward, but depending on your environment you might see some challenges.

Prerequisites and Installation guide can be found here: https://docs.microsoft.com/en-us/sccm/core/servers/manage/data-warehouse

The Remote SQL challenges

Last week I was asked to setup a new Data Warehouse server for one of my customers. The Data Warehouse Database should be hosted on a new dedicated SQL Server 2016.

Setup:

• SERVER1 (Primary Site Server with local SQL Server, and reporting services)
• SERVER2 (New SQL Server for the Data Warehouse Database)

When everything was installed, I tried to run the build-in Data Warehouse reports from Reporting Services on SERVER1, and got the following 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.)

This error is also documented under the known issues section here: https://docs.microsoft.com/en-us/sccm/core/servers/manage/data-warehouse

The solution (fix) is also documented:

Solution – Use the following steps to configure certificates:

1. On the computer that hosts the data warehouse database:
1. Open IIS, click Server Certificates, right-click on Create Self-Signed Certificate, and then specify the “friendly name” of the certificate name as Data Warehouse SQL Server Identification Certificate. Select the certificate store as Personal.
2. Open SQL Server Configuration Manager, under SQL Server Network Configuration, right-click to select Properties under Protocols for MSSQLSERVER. Then, on the Certificate tab, select Data Warehouse SQL Server Identification Certificate as the certificate, and then save the changes.
3. Open SQL Server Configuration Manager, under SQL Server Services, restart SQL Server service and Reporting Service.
4. Open the Microsoft Management Console (MMC) and add the snap-in for Certificates, select to manage the certificate for Computer account of the local machine. Then, in the MMC, expand the Personal folder > Certificates, and export the Data Warehouse SQL Server Identification Certificate as a DER encoded binary X.509 (.CER) file.
2. On the computer that hosts SQL Server Reporting Services, open the MMC and add the snap-in for Certificates. Then, select to manage certificates for Computer account. Under the Trusted Root Certificate Authorities folder, import the Data Warehouse SQL Server Identification Certificate.

I followed the guide, but when I restarted the SQL service, I got the following error:

“The request failed or the service did not respond in a timely fashion. Consult the event log or application logs for details”.

After some digging in log files, event logs and talking with some SQL friends, I learned that the service account running the SQL Service needs to be granted access to the new self-signed certificate, so it can load and use the certificate.

The solution

Before we can grant access, we need to identify the “Unique Container Name” of the certificate. This can be done by using the following command on the SQL Server that is hosting the Data Warehouse Database: certutil –store my > C:\temp\cert.txt. This command will show and dump all the certificates on the machine in the notepad file (cert.txt).

Open the cert.txt in notepad, and find the certificate and get the “Unique container name”.

Go to the following path from a command prompt:
“C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys”.

And run the following command:
icacls /grant UniqueContainerName “SQL_Service_Account”:(R)

Example: icacls d408dafea010a8c284bc0c65083ccc5a_7fde1626-3f98-467e-a718-0a2392463ba7 /grant “DOMAIN\SQLServiceAccount”:(R)

Verify the result, and make sure you have success reported.

Restart both SQL Server service and Reporting Service.

Now you should be able to run the Data Warehouse reports from Reporting Services.

Pro-Tip

The self-signed certificate created by the IIS, is SHA1 and expires after just 365 days. Personally I find it hard to keep track of all the certificates that I (or my customers) need to update and when. So in this specific scenario I’d recommend that you don’t follow the documentation and create the Server Certificate using IIS. Instead you should create a custom certificate using MakeCert utility or PowerShell. MakeCert is available as part of the Windows SDK, which you can download here: http://go.microsoft.com/fwlink/p/?linkid=84091.

This is the command used to create the certificate using MakeCert:

makecert -r -pe -n “CN=SERVER2.corp.domain.com” -eku 1.3.6.1.5.5.7.3.1 -sky exchange -SS MY -a sha256 -sr LocalMachine

Now we have a certificate that is SHA2 and the default expire time is 40 years. Secure and “Admin friendly”.

If you don’t have MakeCert (or don’t want to download the ~1 GB of content to get the 50k MakeCert.exe file, you can also use the New-SelfSignedCertificate PowerShell cmdlet.

More information

• Data Warehouse Service Point: https://docs.microsoft.com/en-us/sccm/core/servers/manage/data-warehouse
• MakeCert: https://msdn.microsoft.com/en-us/library/windows/desktop/aa386968(v=vs.85).aspx
• New-Self​Signed​Certificate: https://docs.microsoft.com/en-us/powershell/module/pkiclient/new-selfsignedcertificate?view=win10-ps

/Enjoy

+Ronni Pedersen