Configuration Manager 2012 R2 not updating Heartbeat DDR and Hardware Scan info

Suddenly, and without any introductions, Configuration Manager 2012 R2 is no longer update the information of Heartbeat DDR and Hardware Scan fields. On the other hand, machines are sending hardware invntory and communicating in a healthy way to the configuration manager. We can also see updated hardware inventory for all machine. The only thing, is that these two fields are showing a very old date. This can be harmful if like me you are using these fields for various reports.

After opening a case with Microsoft, the support engineer confirmed that the configuration manager implementation is healthy and everything is working fine. After several escalations, Microsoft solution was to create a SQL JOB that runs every day that execute a built in stored procedure called exec_CH_syncClientSummary. The solution worked like magic.

Configuration Manager 2012 R2 not updating 3433

Config Manager SCCM 2012/R2 – What happened if you reinstalled WSUS or SUP? Fix it

Hi everyone,

I want to share with you a strange behavior that happened that day when you uninstall WSUS and SUP from a server and then you try to install them again.

Say for example that you have a dedicated server running WSUS and SUP on it, and you got to a problem that you could not solve, and you decided to remove WSUS and SUP and then install them again to solve the problem. It can happen.

The moment you remove WSUS and SUP, at the Config Manager console, all updates will appear as expired. The strange thing is that when you install WSUS and SUP again, the synchrronization between your SCCM server and WSUS/SUP will work without errors but nothing get synched. No single error.

Resolution:

  • Uninstall SUP and confirmed from the supsetup.log to that it’s been uninstalled.
  • Run “select * from WSUSserverlocations” and confirm that it has removed entry for SCA.
  • Backup/Export HKLM\SOFTWARE\Microsoft\SMS\Components\SMS_WSUS_SYNC_MANAGER and renamed it.
  • Install SUP and again get the SUPSetup.log to verify.
  • New HKLM\SOFTWARE\Microsoft\SMS\Components\SMS_WSUS_SYNC_MANAGER registry key created automatically.
  • Restart SMS_EXECUTIVE.
  • Synchronization started.

[Resolution provided by Microsoft Support]

Get Software Update Size from Configuration Manager 20120/R2 [Report/PowerShell]

Hi everyone,

I worked in an environment where distribution points are centralized and we had to plan our software update distribution via config manager 2012/R2 very carefuly, by making sure that a Software Update Group will not have over 300 MB of content.

If you browse the Config Manager Software Updates node, you can filter and sort the updates that you are interested in, but you cannot see the Size of those updates right away. You have to go to each update, check its properties, and see what content files are part of that specific update, and then calculate the size.

I created a SQL query that can filter updates by a time frame, and will report back the list of output updates along with the size of each update, so you can easily plan your Software Update Group membership.

This blog post is moved to my new blog platform

Go here please to continue reading: https://blog.ahasayen.com/configuration-manager-update-size/

Configuration Manager Randoms

Sometimes, you may see that your Management Point is rejected registration from some clients for different reasons, and you will get the GUID of that client. One example of such errors is (MP has rejected a message from GUID:……..) where Message Error ID = 5447

ConfigurationManagerGUID234233321

ConfigurationManagerGUID23423233

Sometimes, you will get Error ID 5445 “MP has rejected registration request due to failure in client certificate (Subject Name: XXXX) chain validation” but without any GUIDs. You may have to go to your Management Point, browse to Program Files\Microsoft\SMS_CCM\Logs and read the MP_RegistrationManager log. From there you can search for the certificate subject name and get the GUID of that machine.

It is always about GUIDS when shit happens in Config Manager, and the challenge to identify that resource by name so you can go there and read the logs.

There are three ways to convert that GUID to a name:

SQL Query

Just open the SQL Management Studio, connect to your Config Manager SQL database and run this:

SELECT
Name0,
SMS_Unique_Identifier0
FROM
v_R_System
WHERE
SMS_Unique_Identifier0 LIKE '%FFF8C47-4156-4A34-B5AD-4B8D483FFA7D'

ConfigurationManagerGUID23423332

Add GUID column to ALL SYSTEMS built in Query

Just add the column to the existing all system query and put the System.Resource.Configuration Manager Unique Identifier in Search bar u will get the Client name.

ConfigurationManagerGUID234232

PowerShell

I will redirect you to this blog post to see sample script to do this.

Configuration Manager 2012 and WSUS/SUP – How Windows Updates Works

Check these posts:

There are four components that work together to deliver a complete patching scenario:

  • SCCM Agent on the client machine
  • Windows Update Agent on the client machine
  • WSUS Server.
  • SCCM Site server and Distribution Points.

First of all, the SCCM will configure the WSUS with the Update Classifications and Products that should be in the scope of the update process via the SUP components as per the previous section.
WSUS will contact Microsoft Update Services and will download the catalog (metadata) of the matching updates. This is only the metadata (Description of the update) and not the update files themselves.

The SCCM client on Windows client machines will configure the local group policy on client machine with the WSUS server (Server2) as the WSUS server for Windows Updates.
The Windows Update Agent on client machines will contact the WSUS server configured via the local group policy (Server2) to download the catalog and will scan the machine for matching updates needed and will report back to WSUS what updates are needed.

Then on the SCCM site server will contact WSUS and get the catalog file that contains the description of the updates (not the update files themselves).
On the SCCM console, you will see those update descriptions and you will choose to deploy them to a collection of machines.

SCCM (not WSUS) then will contact Microsoft Update Services and will download the actual update files and will store them locally and push them to distribution points.
The SCCM client on the client machines will then see those updates and will download them locally. The Windows Update Agent will continue the work and will install the updates on client machines.

Now, the client machine will read the local group policy setting and the Windows Update Agent will contact the WSUS (server2) to download the
In other words, the WSUS server on Server2 will not download the update files. Actually the WSUS server on Server2 will only has the catalog on its disks, while the distribution points will have the actual update files on its disks.
Since the SCCM client on Windows client machines will configure the local group policy to point to Server2 as the WSUS server, it is so important to be careful when you have a domain group policy that configures the WSUS server as the source of update servers since domain group policy are stronger than the local group policies configured by SCCM client. Just make sure if you have a domain group policy that configures the WSUS settings to configure it with Server2 as the WSUS server and nothing else. Not even a CNAME for Server2.

If you have configured the GPO with (Configure Automatic Updates) to Enabled, then the Windows Update Agent on machines will display an extra notification to the client that a restart is pending. Disabling that setting will prevent Windows machines to download updates to the (Windows Update Agent) component. Check out this.

Configuration Manager 2012 R2 Deployment Notes

Introduction

I want to share with you my experience in deploying System Center Configuration Manager 2012 R2 in a live environment and on Windows 2012 R2.

My setup will consist of three servers:

  • SCCM_Site : Site server, management point, distribution point
  • SCCM_WSUS : SUP and WSUS server
  • SCCM_APP

Microsoft recommends simplifying the SCCM hierarchy, and if possible, sticking to one site when possible. I like this idea of consolidating the complex hierarchies we used to have in previous versions of SCCM, to one big SCCM site. After all, simplicity is the key of success in my opinion.
Microsoft and many MVPs recommend to consolidate the SQL with the site server. I was in TechEd 2014 when I shared my design of separating the SQL instance in backend cluster configuration for better performance, and everyone there recommend having the SQL and SCCM site server in same box when possible. I am considering this in my new design.
I am separating WSUS to different server along with the SUP, just because WSUS is a different product and from a governance perspective, I decided to locate it in different server. Also, I love to have WSUS using pots 80 and 443 and by separating it in a different box, no conflict with the IIS roles required by SCCM.

Prerequisitres

Users

The following users:

  • SCCM_Network: used as a network access account in SCCM. This account is used as the security context to access content in distribution in case the client cannot authenticate using its computer account (in case of workgroup scenario for example).
  • SCCM_Site: used to install other SCCM server roles from the SCCM console.

Groups

Create the following groups

  • ALL SCCM Servers: contains the SCCM three servers
  • ALL SCCM Admins: contains:
    • ALL SCCM Servers
    • SCCM_Network
    • SCCM_Site
    • Your admin accounts

Software

I have also downloaded the following software:

System Management Container:

If you extended the schema and want to publish SCCM data into active directory, do not forget to create the System Management container in AD with ALL SCCM Servers configured with full access in that folder and child folders.

Site Server Setup

I will be preparing a Windows 2012 R2 server with 16 GB RAM (12 GB RAM for SQL, and the rest for SCCM), and the following drives:

  • F Drive: for database files.
  • G Drive: for database logs.
  • H Drive: for database temp files.
  • I Drive: for SCCM files.
  • J Drive: for SCCM Content Library files.

I have also placed a file (no_sms_on_drive.sms) on the C, F, G, H and J drives to prevent SCCM setup from throwing files there.
In case that you have a firewall enabled on the server, just allow everything for now inbound and outbound for now.
After patching the server with all windows updates, I configured a group policy to configure the administrator group in all three servers to be “ALL SCCM Admins”.
One of my worst tasks in SCCM deployment is the software prerequisites as there is so much to do. To install the software prerequisites, I checked the TechNet Documentation and personally I use this GUI tool to check if all my prerequisites are fine.

Next, is the folder structure for the J drive. I used this blog site to build my folder structure on the J drive as it shows a nice and well-designed folder structure along with share configuration and NTFS permissions.

Here is a script I wrote that will create the folder structure on the J drive. You can change the $ScriptFilesPath variable on the script to choose the drive of your choice.


$ScriptFilesPath = "J:\"

$Source_Directory1 = Join-Path $ScriptFilesPath "Source"
$Source_Directory2 = Join-Path $ScriptFilesPath "Source\Captures"
$Source_Directory3 = Join-Path $ScriptFilesPath "Source\Client"
$Source_Directory4 = Join-Path $ScriptFilesPath "Source\Content"

$Source_Directory5 = Join-Path $ScriptFilesPath "Source\Content\OSD"
$Source_Directory6 = Join-Path $ScriptFilesPath "Source\Content\OSD\BootImages"
$Source_Directory7 = Join-Path $ScriptFilesPath "Source\Content\OSD\Drivers"
$Source_Directory8 = Join-Path $ScriptFilesPath "Source\Content\OSD\MDTSettings"
$Source_Directory9 = Join-Path $ScriptFilesPath "Source\Content\OSD\MDTToolKits"
$Source_Directory10 = Join-Path $ScriptFilesPath "Source\Content\OSD\OSImages"
$Source_Directory11 = Join-Path $ScriptFilesPath "Source\Content\OSD\Source"


$Source_Directory12 = Join-Path $ScriptFilesPath "Source\Content\Software Deployment"
$Source_Directory13 = Join-Path $ScriptFilesPath "Source\Content\Software Updates"


$Source_Directory14 = Join-Path $ScriptFilesPath "Source\Import"
$Source_Directory15 = Join-Path $ScriptFilesPath "Source\Baselines"
$Source_Directory16 = Join-Path $ScriptFilesPath "Source\Drivers"

$Source_Directory17 = Join-Path $ScriptFilesPath "Source\InstallationUpdates"
$Source_Directory18 = Join-Path $ScriptFilesPath "Source\MDTLogs"
$Source_Directory19 = Join-Path $ScriptFilesPath "Source\Scripts"

$Source_Directory20 = Join-Path $ScriptFilesPath "Source\StateMigration"

$Source_Directory21 = Join-Path $ScriptFilesPath "Source\Tools"
$Source_Directory22 = Join-Path $ScriptFilesPath "Source\Error Logs"

if(Test-Path $Source_Directory1 ) {
 throw " Directory Exists $Source_Directory "
 Exit
 }
 
 
 if(!(Test-Path $Source_Directory1 )) {
 
 New-Item -ItemType directory -Path $Source_Directory1
 New-Item -ItemType directory -Path $Source_Directory2
 New-Item -ItemType directory -Path $Source_Directory3
 New-Item -ItemType directory -Path $Source_Directory4
 New-Item -ItemType directory -Path $Source_Directory5
 New-Item -ItemType directory -Path $Source_Directory6
 New-Item -ItemType directory -Path $Source_Directory7
 New-Item -ItemType directory -Path $Source_Directory8
 New-Item -ItemType directory -Path $Source_Directory9
 New-Item -ItemType directory -Path $Source_Directory10
 New-Item -ItemType directory -Path $Source_Directory11
 New-Item -ItemType directory -Path $Source_Directory12
 New-Item -ItemType directory -Path $Source_Directory13
 New-Item -ItemType directory -Path $Source_Directory14
 New-Item -ItemType directory -Path $Source_Directory15
 New-Item -ItemType directory -Path $Source_Directory16
 New-Item -ItemType directory -Path $Source_Directory17
 New-Item -ItemType directory -Path $Source_Directory18
 New-Item -ItemType directory -Path $Source_Directory19
 New-Item -ItemType directory -Path $Source_Directory20
 New-Item -ItemType directory -Path $Source_Directory21
 New-Item -ItemType directory -Path $Source_Directory22


 } # if(!(Test-Path $ScriptFilesPath ))

<#
 Description for each folder along with share and NTFS permissions

http://kristopherjturner.com/2014/04/11/deploying-system-center-2012-r2-configuration-manager-single-site-server-scenario-part-iii-configuration/
 #>

 

Also, I have disabled UAC on the box as it may cause some trouble. Check out this post.

SUP Server Setup

One of the most confusing point for me was SUP and its integration with WSUS. So I will try to share my understanding here about how configuration manager can integrate with WSUS to distribute software updates.

With configuration manager 2012 and 2012 R2, Microsoft is pushing people to consolidate their complex SCCM hierarchy in previous version to a simpler one. Usually one site is enough for most environment, but again this depends.

Even you deploy one SCCM site in 2012 or 2012 R2, the recommendation is to consolidate the SQL with the SCCM site server. Usually having the SCCM Site server, MP and distribution point along with the SQL is a welcome thing in terms of keeping thing simpler.
When it comes to integrating WSUS with SCCM in that context, you have to add a role called Software Update Point (SUP), which is the interface that SCCM uses to control WSUS simply. Think of SUP as the API that SCCM uses to communicate with WSUS.
I see people installing WSUS and SUP in the same box as the SCCM site server itself. This means that you are installing WSUS using custom site and different ports. I do not like personally to use custom websites and ports other than 80 and 443 for HTTP and HTTPS, so I will be installing WSUS and SUP in different server. The below chart demonstrate my setup.

You can see that I have two servers, one that has the SCCM 2012 R2 site role, management point, distribution point and SQL, and another server that I will install WSUS and later SUP on it. This way, I can install WSUS on the default web site and maintaining the 80,443 bindings. I recommend to make the computer account of Server1 an administrator on Server2, because SCCM site server will use by default its computer account to install SUP on Server2. The other requirement is that you have to install only the WSUS console on Server1, so that the SCCM site server can administer the WSUS settings on server2.

On Server2, install WSUS services, and when the installation finish, do not (again DO NOT) configure the WSUS here. In fact, if you are prompted to start the WSUS configuration wizard, just close it. This is because SCCM site server (Server1) is the only one that should configure WSUS.

If you are running Windows 2012 R2 on server2, which I do in my setup, WSUS version 4 get installed. SCCM will always mention that WSUS 3.0 SP2 should be installed to support SUP. Just ignore this fact.

When you install WSUS on Server2, by default the WSUS will be configured with ports 8530 and 8530. I then will open IIS on Server2, and leave those bindings, and add another binding for http on port 80. The reason why you should not remove the default bindings on ports 8530 and 8531 is that SUP component that will be installed later on Server2, will try to validate the health of WSUS using those ports always. So the IIS site that hosts WSUS on Server2 will have bindings on 8530 (http), 8531(https) and 80(http).

I also just for making things perfect, I installed a certificate using my local PKI infrastructure for Server2.contoso.com and bind it to the IIS site hosting WSUS on server2 using port 443. This is not a required step as we will not enable https on WSUS at least in this scenario.

Now, I will log on to Server1, and install SUP component on Server2 (make sure the computer account of Server1 is member of the local administrators group in Server2).

Then, go to the SCCM management console, Administration, Site Configuration, Sites and click Configure Site Components, Software Update Points, and configure the settings of WSUS.

SCCM_deployment1

SCCM_deployment2

Now go to the SCCM management console, Administration, Servers and Site System Roles, click on Server2, and down you can see the components installed on the server. Right click Software Update Point and choose Properties, and make sure 80 and 443 are there.

SCCM_deployment3

In the same place, right click the Site system components (while Server2 is chosen), and check the properties. Check the properties there.

Config Manager 2012/R2 Build and Customize SQL Reports

See also:

I want to share with you my experience in building a professional SQL report in Configuration Manager 2012/R2. I suppose you have installed the Reporting Point role and you have everything configured for you. I will be creating a report to generate a full hardware inventory for all machines in a nice and organize way.

Now, open the Configuration Manager console> Monitor> Reporting>Reports> Right click> Create Report.

Choose SQL-based Report, and type a name for the report, Click Browse and choose any category for your report. Click Next.

Config Manager 2012 Build and Customize SQL Reports 1

A browser Window will open automatically and hopefully the SQL Server 2012 Report Builder 3.0 will be downloaded and open for you. I am assuming you are using SQL 2012 in this case, but it really does not matter what version you are using. In all cases, you will have the SQL Report Builder open for you. This tool is nothing but a a way for you to choose your SQL Query and customize how the report will look like.

Config Manager 2012 Build and Customize SQL Reports 2

Now right click on the Datasets and click Add Dataset.  You can think of datasets as a temporary SQL database table that contain the result of your SQL query that you are about to enter.

Config Manager 2012 Build and Customize SQL Reports 26

On the Dataset properties page, choose Use a dataset embedded in my report, and in the data source, choose the one created automatically for you with a long name that looks like a GUID, and then past your SQL query. In this example, I am using the SQL query from my previous blog post here.

Config Manager 2012 Build and Customize SQL Reports 4

Now click on the Query Designer and click the execute icon to make sure the query runs without any issues. If all is okay Click Ok twice.

Config Manager 2012 Build and Customize SQL Reports 5

You should have in the left side of the screen new columns in your Dataset.

Config Manager 2012 Build and Customize SQL Reports 6

Now Go to the Insert tab, and click Table> Table Wizard.

Config Manager 2012 Build and Customize SQL Reports 7

Choose the default Dataset (Dataset1) and Next.

Config Manager 2012 Build and Customize SQL Reports 8Now drag all Available Fields  in to the values section. Leave other sections empty.

Config Manager 2012 Build and Customize SQL Reports 9

on the next screen Choose the layout, just click Next.

Config Manager 2012 Build and Customize SQL Reports 10

Choose any style you like form the Choose a style page, click Finish.

Config Manager 2012 Build and Customize SQL Reports 11

Now you should have the table on your report. Feel free to adjust its location to the center of the page.

Config Manager 2012 Build and Customize SQL Reports 27

You can also insert a text box to type the report title.

Config Manager 2012 Build and Customize SQL Reports 13

Now click Run and you will have your report ready to view.

Extra Customization

Add Date header

You can add a Date value below the title of the report to indicate the date in which the report executed on. To do that, go to the Title text box, and right click > Create Placeholder..

Config Manager 2012 Build and Customize SQL Reports 14

in the Placeholder Properties box, in the Value field, enter =Today().

This will automatically generate the date and time value. If you want only to have the date value and not the time, then instead of =today(), you can type =format(cdate(today()),”dd-MM-yyy”)

Config Manager 2012 Build and Customize SQL Reports 15

Your report will look like this now:

Config Manager 2012 Build and Customize SQL Reports 16

Format Date on Column Values

If you have for example a column that produces a date, you can do the same trick by using the format function we have just used. Let us take the BIOSDate field in the report. just right click the value of the [BIOSDate], right click>Placeholder Properties..

Config Manager 2012 Build and Customize SQL Reports 17

On the Placeholder Properties, under the Value field, press the fx icon.

Config Manager 2012 Build and Customize SQL Reports 18The Expression window should look like this:

Config Manager 2012 Build and Customize SQL Reports 19

Now delete the existing expression value and type the following =format(cdate(Fields!BIOSDate.Value),”dd-MM-yyy”).

Now when you run the report, the BIOSData value will show only the date part and not the time part.

Config Manager 2012 Build and Customize SQL Reports 22

Add Index

You can also add an index to your report table. Just go to the designer again, click on the table border> Insert Column >Left

Config Manager 2012 Build and Customize SQL Reports 23

 Now right click on the value cell and click Expression..

Config Manager 2012 Build and Customize SQL Reports 24

 On the expression value, just type  =rownumber(nothing)

Config Manager 2012 Build and Customize SQL Reports 25

 Click Ok and you are done. You now have an index to your report table. Check this YouTube video that describes how to add an index number also.

Add Count of Rows

 Finally, you can add a row count to your report, the same way you added the date of the report field. Just right click anyway in the report title text box, click Create Placeholder.  Click the Fx icon in the Value field, and then type the following expression =count(Fields!Machine.Value, “DataSet1”)

Config Manager 2012 Build and Customize SQL Reports 28

We used the Count() function, and we gave it the name of one column (Machine in this case), and the name of the dataSet that we had created DataSet1

Now when you run the report, you will have the count of machines in the report:

Config Manager 2012 Build and Customize SQL Reports 29

Config Manager 2012 (SCCM) Most Amazing Hardware Inventory SQL Report

[new] Last Updated Sept 2015

Adding CPU Information and Windows Updates Scan Information

See Also:

Introduction

I will be talking about one of my favorite parts when it comes to configuring Configuration Manager 2012/R2 infrastructure. By now, you have the main SCCM roles configured including the reporting point, and you start collecting hardware inventory data from your machines.

The part that is so interesting is to take this raw data that exist in your Configuration Manager database, and transform it into a valuable information that you can look at, and have a good understanding of your network resources.

I was trying to generate a SQL report that gives me machines in my network with almost full hardware inventory in a very well formatted way. If you are using SCCM queries (WQL) to generate report, I may argue that queries are good for SCCM operations and collection membership. They are not the preferred way for reporting.

SQL reports using Reporting Point is the preferred way to generate reports in Configuration Manager using native SQL queries. To do that, you should have some knowledge of the SCCM database schema and start browsing using ResourceID as the identifier of resources inside the SCCM SQL database.

Report Data

  • Machine Name : without any duplicates in the way.
  • Active Directory Site
  • User Name
  • Top User : the most user appearing to log to this machine
  • Windows Version: the last Windows version installed, removing any duplicates in the way/
  • Windows Service Pack
  • Machine Manufacturer
  • Machine Model
  • Serial Number
  • BIOS Date
  • BIOS Version 
  • Managed Date: the date in which SCCM started to manage this machine.
  • Physical Memory: Aggregate memory installed in all memory slots, removing any duplicates in the way.
  • Number of memory slots.
  • Type of the machine : X64 or X86
  • Logical Disk Size in GB
  • CPU Information [New]:
    • CPU Type
    • Number of sockets
    • Number of cores
    • Number of logical processors
  • Windows Update Scan Information [New]
    • Last Windows Update scan.
    • Last Windows Update scan error.
    • Last location for Windows Update scan.
  • PC Type: enumeration of all values of ChassisTypes0. This can be one of the following:
    1. when ‘1’ then ‘Other’
    2. when ‘2’ then ‘Unknown’
    3. when ‘3’ then ‘Desktop’
    4. when ‘4’ then ‘Low Profile Desktop’
    5. when ‘5’ then ‘Pizza Box’
    6. when ‘6’ then ‘Mini Tower’
    7. when ‘7’ then ‘Tower’
    8. when ‘8’ then ‘Portable’
    9. when ‘9’ then ‘Laptop’
    10. when ’10’ then ‘Notebook’
    11. when ’11’ then ‘Hand Held’
    12. when ’12’ then ‘Docking Station’
    13. when ’13’ then ‘All in One’
    14. when ’14’ then ‘Sub Notebook’
    15. when ’15’ then ‘Space-Saving’
    16. when ’16’ then ‘Lunch Box’
    17. when ’17’ then ‘Main System Chassis’
    18. when ’18’ then ‘Expansion Chassis’
    19. when ’19’ then ‘SubChassis’
    20. when ’20’ then ‘Bus Expansion Chassis’
    21. when ’21’ then ‘Peripheral Chassis’
    22. when ’22’ then ‘Storage Chassis’
    23. when ’23’ then ‘Rack Mount Chassis’
    24. when ’24’ then ‘Sealed-Case PC’
    25. else ‘Undefinded’

Blog Post Moved to my new blog platform

This post is available on my new blog:

https://blog.ahasayen.com/configuration-manager-report/

Configuration Manager 2012 R2 Reporting Services and SSL Trusting and Binding Issues

Hi everyone, i was configuring Reporting Services for Configuration Manager 2012 R2 that day, and i got two annoying issues when it comes to SSL binding.

I want to share with you the two issues and how I solved them.

Duplicate SSL binding, or Unknown appearing in the https URL

So you have a certificate in the personal store in the reporting server with name SRV-1.contoso.com, you already configured a certificate binding via the (Web Service URL) in the Reporting Services Configuration Manager. Now, you want to change the URL name, and thus the certificate, so you went to the certificate store and deleted the certificate for SRV-1.contoso.com before you use the Reporting Services Configuration Manager to unbind it. Now every time you try to add another certificate, the Reporting Services Configuration Manager keep showing the old name that exists in the old certificate, or worse, display an Unknown URL like https://unknown.contoso.com/reports... .

Also the following event appears in the event viewer.

Event ID 110, Source: “Report Server Windows Service”, Details: “The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead”.

Configuration Manager 2012 R2 Reporting Services and SSL 1

To investigate more, i went to the configuration file on the reporting server located here: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config.  On the file, you can find all binding including the one for the old name SRV1-contoso.com.

<Application>
<Name>ReportManager</Name>
<VirtualDirectory>Reports</VirtualDirectory>
<URLs>
<URL>
<UrlString>http://+:80</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>
<URL>
<UrlString>https://SRV-1.contoso.com:443</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>
<URL>
<UrlString>https://SRV-2.contoso.com:443:443</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>
</URLs>
</Application>

Now deleting the part containing SRV-1.contoso.com solves the issue. Here is the part that i deleted:

<URL>
<UrlString>https://SRV-1.contoso.com:443</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>

When you attempt to connect to the data source in Report Builder 3.0 with CM 2012, you receive the following error message: 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.)

So you are running Configuration Manager (20120 r2 in my case), and you have a reporting service (in another machine in my case), and you want to build a report, so you have your SQL report builder (Version 3 in my case), and when you try to do certain actions, you will get ugly errors about certificate chain not trusted.

Configuration Manager 2012 R2 Reporting Services and SSL 2

Reason is simple: SCCM SQL server is using self signed certificate for this operation. You have either to trust this certificate on computers you are running the SQL report builder from, or issue a trusted Web Server certificate and assign it to the SCCM SQL server.

The best way to find out where this self signed certificate is located, is by going to your SCCM SQL server, open the SQL Server Configuration Manager> SQL Server Network Configuration>Protocols for… > Right click properties> Certificate tab and then click view.

Configuration Manager 2012 R2 Reporting Services and SSL 3

Simpliest way is to export this certificate and import it to the trusted root certifications store on the computer on which you will be running the SQL Report Builder

Tip: a good reference that I recently read after posting this blog post can be found here. Check it out.