SCCM Data Warehouse – SQL SSL certificates on AlwaysOn cluster

I was setting up the new Data Warehouse feature of SCCM on our corporate SQL 2016 AlwaysOn cluster.

You will notice that after you have installed the DW and try to run a report, you may get this 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.)

The Data Warehouse reporting data source has this parameter: TrustServerCertificate=false

You can change this to true – but it will get set back to false by SCCM 🙂

The correct way forward – in my my opinion, is to install trusted certificates from your root CA on the SQL Instances hosting the DW database. There are other posts on installing the self signed certificate as a trusted root CA onto the reporting server, but I don’t like using self signed certs as they are difficult to track and revoke.

This solution was tested on a 3 node AlwaysOn SQL 2016 cluster running on Server 2016 VMs in Azure.

The certificates are requested from the internal publishing CA’s using powershell.

The SQL instance where the Data Warehouse database exists will likely have >1 Availability Group and Availability Group Listener (AGL), therefore when requesting the certificate, ALL the AGLs netbios and fqdn’s should be included in the Subject Alternative Name (SAN) properties, as well as the hostname.

I ran this PowerShell on each of the 3 SQL nodes to request a unique certificate.

# Create and submit a request
$Hostname = [System.Net.Dns]::GetHostByName(($env:computerName)).Hostname
$template = "WebServer2008"
$SAN = $Hostname,"AGL1-INSTANCE1","AGL1-INSTANCE1.domain.local","AGL2-INSTANCE1","AGL2-INSTANCE1.domain.local","AGL3-INSTANCE1","AGL3-INSTANCE1.domain.local","AGL4-INSTANCE1","AGL4-INSTANCE1.domain.local"
Get-Certificate -Template $template -DnsName $SAN -CertStoreLocation cert:\LocalMachine\My

Now, hop onto your issuing CA and approve the pending request for the certificate.

Back on the SQL nodes, retrieve the requested certificate from the CA. It will be installed into the machines Personal store.

# Now the certificate is approved, you can retrieve the request:
Get-Certificate -Request (Get-ChildItem -Path cert:\LocalMachine\Request)

Open an MMC and add the Certificates snap-in targeted at the local computer store. Find the newly installed certificate (The one with the above SANs in it) and retrieve the certificate thumbprint.

On each of the SQL Nodes, change the following registry value so that it matches the thumbprint of the newly approved certificate:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.INSTANCE1\MSSQLServer\SuperSocketNetLib]
"Certificate"="25615102f32108599d89555678e7fcdabb0db491"

The SQL Server service for the instance will need restarting following this Registry update.

Before testing the SCCM Data Warehouse reports, you can try in SQL Server Management Studio using these options:

SQLServerSSL

Advertisements

SQL Backup to URL (Azure Blob) behind authenticated proxy server

If you want to use SQL 2012 or above to backup directly to a URL and your servers are behind a corporate proxy that also requires auth, create the following .net config file so that the sql service will make the connection correctly:

c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\BackuptoURL.exe.config

Add the following code with your proxy address to the new file:

<?xml version ="1.0"?>
<configuration>
            <system.net>
                        <defaultProxy>
                                    <proxy  proxyaddress="http://proxylb.domain.local:8080" bypassonlocal="true" usesystemdefault="True" />
                                    <bypasslist>
                                                <add address="[a-z]+.domain.local$" />
                                    </bypasslist>
                        </defaultProxy>
            </system.net>
</configuration>