Trevor Sullivan's Tech Room - Minding the gap between administration and development

ConfigMgr 2012 SP1: Remote SQL Connectivity Problem

Recently I had some issues with a ConfigMgr 2012 SP1 Primary Site communicating with a remote SQL Server 2008 R2 named instance. This was in my lab environment. I decided to upgrade the database instance from SQL Server 2008 R2 to SQL Server 2012 SP1.

Missing SQL Server Services in Configuration Manager

The first problem I encountered, right after the upgrade, was that I could not view the SQL Server 2012 SP1 instance from the SQL Server Configuration Manager tool. I posted about this problem on the Microsoft TechNet forums, and quickly got the help I needed to resolve it. Although I did not have a new shortcut in the Start Menu for the SQL Server 2012 Configuration Manager tool, it has indeed been installed. I used Windows PowerShell to locate the file (sqlconfigurationmanager11.msc) that was mentioned in response to my inquiry, and it turned out to be in the %WinDir%\System32 folder. After launching this tool, I could successfully “see” the SQL Server services that had been previously “missing” from the SQL Server Configuration Manager.

Database Engine Certificate Issue

I had been having a problem for a little while, on SQL Server 2008 R2, and then SQL Server 2012 after the upgrade, where something with the SQL Server database engine configuration got screwed up. Basically, the SSL certificate that was auto-generated by SQL Server was “broken.” I tried to clear the certificate on the database engine instance, using SQL Server Configuration Manager, but that only caused more problems. I generated a new Computer certificate from my internal Certificate Authority (which the ConfigMgr Primary Site trusts), and set that as the SSL certificate for the SQL Server database engine. After doing this, I started getting some errors in the Application event log.

EventID 26014

Unable to load user-specified certificate [Cert Hash(sha1) "0D1E36686557FDB0A86E9E60DCE80E2820C3D1C1"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

EventID 26014

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

EventID 17120

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

As it turns out, I found out that the service account did not have access to the private key of the SSL certificate that I had enrolled onto the SQL Server. In order to resolve this, I used the MMC snap-in for Local Computer Certificates, right-clicked on the SSL certificate with the corresponding thumbprint in the error message, selected All Tasks –> Manage Private Keys, and added the Read permission for the SQL Server domain service account. After doing this, I was able to successfully start the SQL Server service.

ConfigMgr 2012 SP1 Database Connectivity

This is just some brief documentation of the errors I was getting while I was encountering SQL database engine issues.

Since the first problem I had was the SQL Server database being unavailable, I was getting the following errors in the [cci]smsexec.log[/cci]. The “actively refused” message makes a lot of sense, since there was no database engine service listening on the port that the SMS_Executive service was attempting to connect to.

*** [08001][10061][Microsoft][SQL Server Native Client 11.0]TCP Provider: No connection could be made because the target machine actively refused it.
*** [HYT00][0][Microsoft][SQL Server Native Client 11.0]Login timeout expired
*** [08001][10061][Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
*** Failed to connect to the SQL Server, connection type: SMS ACCESS.
CSiteControlEx::GetCurrentSiteInfo: Failed to get SQL connection
CSiteControlEx::GetMasterSCF:Failed to read site information from database, retry in 5 seconds ...

After getting the SQL Server instance back up and running, I started seeing these messages in the [cci]smsexec.log[/cci]. I’m pretty sure that the root cause of this was that the SQL Server database engine could not access the private key of the configured SSL certificate. Given this, I have no idea which certificate it was ultimately using, but obviously it was not the one that I had explicitly told it to use.

*** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.
*** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
*** Failed to connect to the SQL Server, connection type: SMS ACCESS.
CSiteControlEx::GetCurrentSiteInfo: Failed to get SQL connection
CSiteControlEx::GetMasterSCF:Failed to read site information from database, retry in 5 seconds ...

In order to resolve this, check out the previous section Database Engine Certificate Issue.





Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>