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

1
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

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

EventID 17120

1
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 smsexec.log. 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.

1
2
3
4
5
6
*** [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 smsexec.log. 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.

1
2
3
4
5
*** [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.

System Center 2012 SP1 Orchestrator: Getting Started with Configuration Manager 2012 Integration

Introduction

System Center 2012 SP1 Orchestrator is a tool to help automate enterprise processes. It stresses the reduction of code writing, which has its benefits, but also has its drawbacks. In my personal opinion, everyone should learn how to write code. These days, if you’re focused on the Microsoft platform, there is nothing better to learn than PowerShell. Knowing how to write code will help you to fill in gaps in the integration of two or more software products.

Given the wide array of functionality offered by System Center 2012 SP1 Configuration Manager, it would seem highly desirable to use Orchestrator to automate some of that functionality. You can perform functions such as:

  • Creating or deleting Collections
  • Invoke Collection membership evaluations
  • Add or remove Collection Membership Rules
  • Enumerate Collection members
  • Deploy Applications or Configuration Baselines
  • Invoke ConfigMgr client actions

In the next section, we’ll explore how to set up integration between Orchestrator and Configuration Manager.
Continue reading

SQL 2012 SP1: Errors on starting database server named instance

I recently got some weird SQL Server error messages in the Application event log after rebooting my SQL 2012 SP1 server, running on Windows Server 2012:

Event ID 26014: Unable to load user-specified certificate [Cert Hash(sha1) "D3D88810B87C9533AA30CE9B2C3BDCEE3C674B97"]. 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.

Event ID 17182: TDSSNIClient initialization failed with error 0×80092004, status code 0×80. Reason: Unable to initialize SSL support. Cannot find object or property.

Event ID 17182: TDSSNIClient initialization failed with error 0×80092004, status code 0×1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

These errors confused me, because as far as I know, I’m not using SSL for my SQL Server instance. To solve the issue, I opened the SQL Server Configuration Manager –> SQL Server Network Configuration –> right-click “Protocols for CM12″ –> Properties –> Certificate tab. Click the “Clear” button and restart the service. Hopefully this clears up the error.

 

ConfigMgr 2012: Ccmeval.exe causing client corruption

I’ve discovered, on more than one occasion, that the ConfigMgr 2012 client’s ccmeval.exe, which is intended to resolve client health related issues, actually breaks the ConfigMgr client. This has happened on a Windows 8 RTM client, and I’m fairly certain that it affects Windows Server 2012 as well. You might be aware that Windows 8 and Windows Server 2012 are unsupported client operating systems with ConfigMgr 2012 RTM, but will be officially supported in Configuration Manager 2012 Service Pack 1 (SP1).

Issue


By default, when the ConfigMgr 2012 client is installed, a Scheduled Task is registered under the \Microsoft\Configuration Manager called “Configuration Manager Health Evaluation.” The command line that is called is simply ccmeval.exe, with no command line arguments. The task will run approximately around midnight (12:19 AM on my test client) every night.
Continue reading

VBscript: Export ConfigMgr Task Sequence Variables

Here is a simple VBscript that will export Microsoft System Center Configuration Manager (SCCM / ConfigMgr) task sequence variables to a file on the root of the system drive for troubleshooting. In WinPE (Windows Pre-execution), this is typically the x:\ drive, and in the full OS phase, it’s most commonly the c:\ drive, although that cannot necessarily be assumed either.

In WinPE 4.0, you can use PowerShell instead, but I developed this for use on WinPE 3.x.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
'  Author: Trevor Sullivan
'    Date: 2012-11-16
' Purpose: Exports all ConfigMgr OSD task sequence variables to a text file on
'          the root of %systemdrive%. In WinPE, this is normally x:\.

Option Explicit

dim tsenv, var, fso, log, sh, LogPath

' Create a few COM objects
set tsenv = CreateObject("Microsoft.SMS.TSEnvironment")
set sh = CreateObject("Wscript.Shell")
set fso = CreateObject("Scripting.FileSystemObject")

' Get path to log file
LogPath =  sh.ExpandEnvironmentStrings("%SystemDrive%\Task Sequence Variables.txt")
' Get FileStream object
set log = fso.OpenTextFile(LogPath, 8, true)

' Iterate over task sequence variables and write each one to log file
for each var in tsenv.GetVariables()
    call log.WriteLine(var & " = " & tsenv(var))
    call wscript.echo(var & " = " & tsenv(var))
next

PowerShell: Move ConfigMgr Collections

Introduction

If you work with Microsoft System Center Configuration Manager (SCCM / ConfigMgr) 2007 in any capacity, you probably are familiar with the concept of "collections" and how painful they can be to work with sometimes. The ConfigMgr console does not provide any method of moving a collection from one parent to another, and the GUI is pretty slow to work with.

image

So what’s the solution here? PowerShell, of course!

PowerShell Code

Here is a PowerShell function that will allow you to move a ConfigMgr collection either by name or by collection ID.

Note: Select all of the function text top-to-bottom, and you can retrieve the text that is cut off towards the right.

<#
    .Synopsis
    This function allows you to re-assing the parent for a ConfigMgr collection to a new collection ID

    .Author
    Trevor Sullivan (pcgeek86@gmail.com)

    .Example
    c:PS> Move-SccmCollection -SccmServer sccm01 -SiteCode LAB -CollectionID LAB00159 -ParentCollectionID LAB000150;

    Description
    -----------

    This command moves the ConfigMgr collection with ID "LAB000159" to being a child of collection ID "LAB000150".

    .Example
    c:PS> Move-SccmCollection -SccmServer sccm01 -SiteCode LAB -CollectionName 'Visual Studio' -ParentCollectionID Microsoft;

    Description
    -----------

    This command moves the ConfigMgr collection named "Visual Studio" to being a child of the collection named "Microsoft". Note that you do not need to specify quotes around the parameter value if it does not contain spaces.

    .Notes
    This function is untested with collection links. It is not known whether or not this will remove existing collection links.
#>
function Move-SccmCollection {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)] [string] ${SccmServer}
        , [Parameter(Mandatory = $true)] [string] ${SiteCode}
        , [Parameter(ParameterSetName = "ByCollectionID", Mandatory = $true)] [string] ${CollectionID}
        , [Parameter(ParameterSetName = "ByCollectionID", Mandatory = $true)] [string] ${ParentCollectionID}
        , [Parameter(ParameterSetName = "ByCollectionName", Mandatory = $true)] [string] ${CollectionName}
        , [Parameter(ParameterSetName = "ByCollectionName", Mandatory = $true)] [string] ${ParentCollectionName}
    )

    # Set-PSDebug -Strict;

    # Ensure that ConfigMgr site server is available
    if (-not (Test-Connection -ComputerName $SccmServer -Count 1)) {
        return;
    }

    # Obtain references to collection and parent collection
    switch ($PSCmdlet.ParameterSetName) {
        # Use the "ByCollectionID" PowerShell parameter set to retrieve collection references by ID
        'ByCollectionID' {
            ${CollectionRelationship} = @(Get-WmiObject -ComputerName $SccmServer -Namespace rootsmssite_$SiteCode -Class SMS_CollectToSubCollect -Filter "subCollectionID = '$CollectionID'")[0];
            ${Collection} = @([wmi]("\{0}rootsmssite_{1}:SMS_Collection.CollectionID='{2}'" -f ${SccmServer}, ${SiteCode}, ${CollectionID}))[0];
            ${ParentCollection} = @([wmi]("\{0}rootsmssite_{1}:SMS_Collection.CollectionID='{2}'" -f ${SccmServer}, ${SiteCode}, ${ParentCollectionID}))[0];
        }
        # Use the "ByCollectionName" PowerShell parameter set to retrieve collection references by name
        'ByCollectionName' {
            ${Collection} = [wmi](@(Get-WmiObject -ComputerName $SccmServer -Namespace rootsmssite_$SiteCode -Class SMS_Collection -Filter ("Name = '{0}'" -f ${CollectionName}))[0].__PATH);
            ${ParentCollection} = [wmi](@(Get-WmiObject -ComputerName $SccmServer -Namespace rootsmssite_$SiteCode -Class SMS_Collection -Filter ("Name = '{0}'" -f ${ParentCollectionName}))[0].__PATH);
            ${CollectionRelationship} = @(Get-WmiObject -ComputerName $SccmServer -Namespace rootsmssite_$SiteCode -Class SMS_CollectToSubCollect -Filter ("subCollectionID = '{0}'" -f ${Collection}.CollectionID))[0];
        }
    } 
    
    # If references to both the child and [new] parent collection were obtained, then move on
    if (${Collection} -and ${ParentCollection}) {
        Write-Verbose -Message ('Setting parent collection for {0}:{1} to {2}:{3}' -f `
            ${Collection}.CollectionID `
            , ${Collection}.Name `
            , ${ParentCollection}.CollectionID `
            , ${ParentCollection}.Name);
        ${CollectionRelationship}.parentCollectionID = ${ParentCollection}.CollectionID;
        # Create the new collection relationship (this [oddly] spawns a NEW instance of SMS_CollectToSubCollect), so we have to clean up the original one
        ${CollectionRelationship}.Put();

        # Clean up all other collection relantionships for this collection
        ${OldCollectionRelationshipList} = @(Get-WmiObject -ComputerName $SccmServer -Namespace rootsmssite_$SiteCode -Class SMS_CollectToSubCollect -Filter ("subCollectionID = '{0}' and parentCollectionID <> '{1}'" -f ${Collection}.CollectionID, ${ParentCollection}.CollectionID));
        foreach (${OldCollectionRelationship} in ${OldCollectionRelationshipList}) {
            ${OldCollectionRelationship}.Delete();
        }
    }
    else {
        Write-Warning -Message 'Please ensure that you have entered a valid collection ID or name';
    }
}

 

Here is an example of how to use this function to move a collection based on their collection IDs:

Move-SccmCollection -SccmServer sccm01.mybiz.loc -SiteCode LAB -CollectionID LAB00011 -ParentCollectionID LAB00022;

Here is an example of how to use the function to move a collection based on the collection name:

Move-SccmCollection -SccmServer sccm01.mybiz.loc -SiteCode LAB -CollectionName ‘Visual Studio’ -ParentCollectionID Microsoft;

PowerShell: Report / Check the Size of ConfigMgr Task Sequences

Introduction

In Microsoft System Center Configuration Manager 2007 operating system deployment (OSD), there is a limitation of 4MB for task sequence XML data. This is discussed in a couple of locations:

The Technet document linked to above says the following:

Extremely large task sequences can exceed the 4-MB limit for the task sequence file size. If this limit is exceeded, an error is generated.

Solution: To check the task sequence file size, export the task sequence to a known location and check the size of the resulting .xml file.

Basically, the Technet troubleshooting article is suggesting that you would need to go into the ConfigMgr console, right-click a task sequence, export it to a XML file, and then pull up the file properties. That’s fine for one-off troubleshooting, but what if you had 1000 task sequences and needed to know how large all of them were? Read on to find out how!

Continue reading

PowerShell: Get a List of Installed Software from ConfigMgr

Let’s say you’ve got Microsoft’s System Center Configuration Manager (SCCM / ConfigMgr) in your IT environment (and if you don’t, why on earth not!). If you’re on the desktop management team, you might occasionally get requests from someone on a network or security team, inquiring as to the installed software on a particular client, or group of clients.

Rather than diving straight into the ConfigMgr reports, as most people do, sometimes it’s just faster to load a data set into PowerShell and massage the data from there. Why PowerShell? Well, it provides very easy, real-time filtering and sorting capabilities, and if you need to make a modification to a temporary “report,” you don’t have to worry about modifying the Report object in the ConfigMgr provider, which is typically done through the ConfigMgr console.

Continue reading

ConfigMgr 2007: PXE Service Point Installation Error

Just recently, I was getting an error in the pxemsi.log (pxemsi.log.lasterror) while trying to install a ConfigMgr 2007 PXE Service Point (PSP):

DEBUG: Error 2203:  Database: C:WindowsInstaller1e0d86.ipi. Cannot open database file. System error –2147287037
MSI (s) (20:FC) [09:46:12:689]: Product: SMS PXE Service Point — Internal Error 2203. C:WindowsInstaller1e0d86.ipi, –2147287037
Internal Error 2203. C:WindowsInstaller1e0d86.ipi, –2147287037

 

Continue reading

ConfigMgr: Cleanup Software Updates Objects

Introduction

A common complaint I hear about Microsoft System Center Configuration Manager (SCCM / ConfigMgr) 2007 is the ability to clean up expired and superseded software updates from the objects related to software updates. As software updates are marked as expired or are superseded by newer software updates, Microsoft marks the old updates accordingly. Once an update has been retired, it is desirable for ConfigMgr administrators to remove the updates from deployments and reporting objects. This cleanup effort saves disk space for deployment packages, and can reduce unnecessary information from showing up in reports.

Continue reading