Archive for the ‘SCCM’ Category

This report accepts Package and collection name and returns package status (grouped by Package state/Last Status)

Import the SSRS Reports

Download the report file

Start Internet Explorer on and navigate to http://YOUR_REPORT_SERVER_FQDN/Reports

Choose a path and upload the previously downloaded report file.

Update the DataSource in the reports (also change this line):

<rd:ReportServerUrl>https://sccm.example.com/ReportServer</rd:ReportServerUrl>

Link the DataSets to your DataSource

Untitled

BitLocker recovery key is a unique 48-digit numerical password that can be used to unlock your system if BitLocker is otherwise unable to confirm for certain that the attempt to access the system drive is authorized.

There is a SQL query to get Recovery key:

select a.Id, a.Name, b.VolumeId, c.RecoveryKeyId, c.RecoveryKey, c.LastUpdateTime from dbo.RecoveryAndHardwareCore_Machines a
inner join dbo.RecoveryAndHardwareCore_Machines_Volumes b ON a.Id = b.MachineId
inner join dbo.RecoveryAndHardwareCore_Keys c ON b.VolumeId = c.VolumeId

But, we’re getting encrypted value in this case

Luckily, it’s pretty easy to decrypt it:

All we need to do is to locate SQL stored procedure [RecoveryAndHardwareRead].[GetRecoveryKey]

Stored procedures are located under Programmability-Stored Procedures

right click on it-Script Stored procedure as-Create To-New Query Editor Window

Quick look into this stored procedure reveals line which decrypts Recovery key

RecoveryAndHardwareCore.DecryptString(RecoveryAndHardwareCore_Keys.RecoveryKey, DEFAULT) AS RecoveryKey,

DecryptString is built-in scalar-value function which takes encrypted column name and certificate as parametar and decrypts it


CREATE FUNCTION [RecoveryAndHardwareCore].[DecryptString](@ciphertext [varbinary](8000), @certificateName [nvarchar](48) = N'CERT_NAME')
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [CryptoUtility].[Microsoft.SystemsManagementServer.SQLCLR.CryptoServiceProvider].[DecryptString]
GO

EXTERNAL NAME clause specifies that the function [RecoveryAndHardwareCore].[DecryptString] will be created using a SQL Server Assembly. The EXTERNAL NAME statement uses the following syntax to identify the correct class and method to use from the assembly:AssemblyName.ClassName.MethodName. 

In the previous example, the registered assembly is named [CryptoUtility],the class within the assembly is [Microsoft.SystemsManagementServer.SQLCLR.CryptoServiceProvider], and the method within that class that will be executed is [DecryptString]

An assembly is a file that is automatically generated by the compiler upon successful compilation of every .NET application. It can be either a Dynamic Link Library or an executable file.CryptoUtility assembly is located in <ConfigMgr_Install>\bin\x64\CryptoUtility.dll

SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.

This technology, introduced in Microsoft SQL Server 2005, allow users for example to create the following types of managed code objects in SQL Server in .NET languages such as C# or VB.NET.

The SQL CLR relies on the creation, deployment, and registration of CLI assemblies, which are physically stored in managed code dynamic load libraries (DLLs). These assemblies may contain CLI namespaces, classes, functions and properties.

CryptoServiceProvider provides methods and properties for accessing or examining Cryptographic Service Providers (CSPs) registered in the system.

Using this finding, we can create SQL report to get BitLocker status, like this one:

SELECT cm.Name,
s.User_Name0 as 'User name',
s.Last_Logon_Timestamp0 as 'Last Logon Time',
csys.Manufacturer0 as 'Manufacturer',
csys.Model0 as 'Model',
bl.DriveLetter0,
bl.IsAutoUnlockEnabled0,
bl.ProtectionStatus0,
mbam.MBAMPolicyEnforced0,
mbam.OsDriveEncryption0,
CASE EV.ProtectionStatus0
WHEN '0' THEN 'No' 
WHEN '1' THEN 'Yes' 
WHEN '2' THEN 'Unknown' 
END AS 'Bitlocker Enabled',
CASE WHEN (TPM.IsActivated_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Activated],  
CASE WHEN (TPM.IsEnabled_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Enabled],  
CASE WHEN (TPM.IsOwned_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Owned], 
EV.ProtectionStatus0 AS 'Bitlocker Indicator',

RecoveryAndHardwareCore.DecryptString(ck.RecoveryKey, DEFAULT) AS RecoveryKey,
--RecoveryAndHardwareCore.DecryptBinary(ck.RecoveryKeyPackage, DEFAULT) AS BitLockerRecoveryKeyPackage,
ck.LastUpdateTime


FROM   RecoveryAndHardwareCore_Keys ck
INNER JOIN RecoveryAndHardwareCore_Machines cm on cm .Id=ck.Id
LEFT  JOIN v_R_System s on s.Name0=cm.Name
left join v_GS_COMPUTER_SYSTEM csys on csys.ResourceID = s.ResourceID
left join  v_GS_BITLOCKER_DETAILS  bl on bl.Resourceid=s.ResourceID 
left join v_GS_MBAM_POLICY mbam on mbam.ResourceID=s.ResourceID
left join v_GS_ENCRYPTABLE_VOLUME EV on EV.resourceid=s.resourceid
LEFT JOIN v_GS_TPM TPM ON EV.ResourceID = TPM.ResourceID 

Get Latest BitLocker key

Update 04.06.2020 based on comments

As noticed by blogger Petr K, above query have issue that when there is more than one entry in the RecoveryAndHardwareCore_Keys for particular computer, only the first one gets selected. Typically that one is outdated, so based on his query in comment, i created report which lists latest generated BitLocker key

SELECT
*

from (
Select
cm.Name as 'Computer Name',
s.User_Name0 as 'User name',
s.Last_Logon_Timestamp0 as 'Last Logon Time',
csys.Manufacturer0 as 'Manufacturer',
csys.Model0 as 'Model',
bl.DriveLetter0 'Drive letter',
bl.IsAutoUnlockEnabled0 'Is AutoUnlocled enabled',
bl.ProtectionStatus0 'Protection Status',
mbam.MBAMPolicyEnforced0 'MBAM Policy Enforced',
mbam.OsDriveEncryption0 'OS Drive Encryption',
CASE EV.ProtectionStatus0
WHEN '0' THEN 'No' 
WHEN '1' THEN 'Yes' 
WHEN '2' THEN 'Unknown' 
END AS 'Bitlocker Enabled',
CASE WHEN (TPM.IsActivated_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Activated],  
CASE WHEN (TPM.IsEnabled_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Enabled],  
CASE WHEN (TPM.IsOwned_InitialValue0 = 1) then 'Yes' else 'No' END [TPM Owned], 
EV.ProtectionStatus0 AS 'Bitlocker Indicator',
RecoveryAndHardwareCore.DecryptString(ck.RecoveryKey, DEFAULT) AS 'Recovery Key',
ck.LastUpdateTime as 'Update time',
col.name as 'Collection Name',
row_number() over(partition by cm.name order by ck.LastUpdateTime desc) as rn

from 
RecoveryAndHardwareCore_Keys ck
iNNER JOIN RecoveryAndHardwareCore_Volumes cv on ck.VolumeID = cv.ID
LEFT JOIN RecoveryAndHardwareCore_VolumeTypes cvt on cv.VolumeTypeId = cvt.Id
LEFT JOIN RecoveryAndHardwareCore_Machines_Volumes cmv on cv.Id = cmv.VolumeId
LEFT JOIN RecoveryAndHardwareCore_Machines cm on cmv.MachineId = cm.Id
LEFT  JOIN v_R_System s on s.Name0=cm.Name
left join v_GS_ENCRYPTABLE_VOLUME EV on EV.resourceid=s.ResourceID
left join  v_GS_BITLOCKER_DETAILS  bl on bl.Resourceid=s.ResourceID
left join v_GS_MBAM_POLICY mbam on mbam.ResourceID=s.ResourceID
LEFT JOIN v_GS_TPM TPM ON EV.ResourceID = TPM.ResourceID
left join v_GS_COMPUTER_SYSTEM csys on csys.ResourceID = s.ResourceID
left join v_FullCollectionMembership fcm on fcm.ResourceID=csys.ResourceID
inner join v_Collection col on col.CollectionID=fcm.CollectionID
where col.Name = @collection_name) as t

where rn=1 

Before upgrading SCCM to new version, especially if you moved SCCM database to another drive, make sure database is set correctly.

If, during SCCM upgrade, prerequisite check fails with Checks if the specified SQL Server meets the minimum requirements for site upgrade, make sure SQL service broker is enabled and broker priority is set.

SQL check script:

USE CM_PR01
SET NOCOUNT ON

     DECLARE @dbname NVARCHAR(128)
  
     SELECT @dbname = sd.name FROM sys.sysdatabases sd WHERE sd.dbid = DB_ID()
  
     IF (@dbname = N'master' OR @dbname = N'model' OR @dbname = N'msdb' OR @dbname = N'tempdb' OR @dbname = N'distribution' ) BEGIN
     RAISERROR(N'ERROR: Script is targetting a system database.  It should be targeting the DB you created instead.', 0, 1)
     GOTO Branch_Exit;
     END ELSE
     PRINT N'INFO: Targeted database is ' + @dbname + N'.'
  
     PRINT N'INFO: Running verifications....'
  
     IF NOT EXISTS (SELECT * FROM sys.configurations c WHERE c.name = 'clr enabled' AND c.value_in_use = 1)
     PRINT N'ERROR: CLR is not enabled!'
     ELSE
     PRINT N'PASS: CLR is enabled.'
  
     DECLARE @repltable TABLE (
     name nvarchar(max),
     minimum int,
     maximum int,
     config_value int,
     run_value int )
  
     INSERT INTO @repltable
     EXEC sp_configure 'max text repl size (B)'
  
     IF NOT EXISTS(SELECT * from @repltable where config_value = 2147483647 and run_value = 2147483647 )
     PRINT N'ERROR: Max text repl size is not correct!'
     ELSE
     PRINT N'PASS: Max text repl size is correct.'
  
     IF NOT EXISTS (SELECT db.owner_sid FROM sys.databases db WHERE db.database_id = DB_ID() AND db.owner_sid = 0x01)
     PRINT N'ERROR: Database owner is not sa account!'
     ELSE
     PRINT N'PASS: Database owner is sa account.'
  
     IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_trustworthy_on = 1 )
     PRINT N'ERROR: Trustworthy bit is not on!'
     ELSE
     PRINT N'PASS: Trustworthy bit is on.'
  
     IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_broker_enabled = 1 )
     PRINT N'ERROR: Service broker is not enabled!'
     ELSE
     PRINT N'PASS: Service broker is enabled.'
  
     IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_honor_broker_priority_on = 1 )
     PRINT N'ERROR: Service broker priority is not set!'
     ELSE
     PRINT N'PASS: Service broker priority is set.'
  
     PRINT N'Done!'
     Branch_Exit: 


If broker is not set, run following query:

USE master;
ALTER DATABASE CM_PR1 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE CM_PR1 SET HONOR_BROKER_PRIORITY ON;
GO

If trustworthy is not set, configure it:

USE master;
GO
ALTER DATABASE CM_PR1 SET TRUSTWORTHY ON
GO

If owner is not sa, set it:

use CM_PR01
go
EXEC sp_changedbowner 'sa'

If, prerequisite fails with “Pending reboot” error, try restarting server 2-3 times, if error still persist, try resetting Windows component service:

Stop Windows update service

net stop wuauserv

Rename C:\Windows\SoftwareDistribution to C:\Windows\SoftwareDistribution_old

Start Windows update service

net start wuauserv

Run prerequisite check again

In this example SCCM and SQL services are monitored by NCPA agent.

Open host configuration file /usr/local/nagios/etc/objects/conf.d/hostname.cfg

Add following lines:

define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SMS Agent host
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=CcmExec,status=running'
        }


define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SMS EXECUTIVE
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=SMS_EXECUTIVE,status=running'
        }

define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SMS NOTIFICATION SERVER
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=SMS_NOTIFICATION_SERVER,status=running'
        }


define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SMS SITE COMPONENT MANAGER
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=SMS_SITE_COMPONENT_MANAGER,status=running'
        }


define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SMS SITE SQL BACKUP
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=SMS_SITE_SQL_BACKUP,status=running'
        }

define service{
        use                             generic-service
        host_name                       sccm.test.com 
        service_description             SMS SITE VSS WRITER
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=SMS_SITE_VSS_WRITER,status=running'
        }


define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SQL Server
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=MSSQLSERVER,status=running'
        }


define service{
        use                             generic-service
        host_name                       sccm.test.com
        service_description             SQL Server Reporting Services
        check_command                   check_ncpa!-t 'API KEY' -P 5693 -M 'services' -q 'service=SQLServerReportingServices,status=running'
        }

Service name can be found in services.msc snap-in

Restart nagios systemctl restart nagios and new services should appear for host.

Upgrading SCCM 1606 to 1610

Posted: February 10, 2017 in SCCM

For full features of 1610 take a look at technet link.

Before performing upgrade,go to upgrade check-list and perform site backup

Upgrade is done using console only (no download link)

 

Downloading update

Administration-Cloud Services-Updates and Services-Check for updates

 

1.png

You should see 1610 update in the console in “Downloading” state (Check C:\Program Files\Microsoft Configuration Manager\Logs\dmpdownloader.log for status)

 

2.png

 

3.PNG

Update files are downloaded to \Microsoft Configuration Manager\EasySetupPayload folder

 

4.PNG

We can aslo monitor download status using Resource Manager

 

5.png

 

Prerequisite check

 

After download,status is changed to “Available”-right click to update-Run Prerequisite check

 

6.png

Status can be tracked from C:\ConfigMgrPrereq.log

 

7.PNG

Or Monitoring-Distribution Status-Updates and Servicing Status-Right click on update-Show status

 

8.png

 

9.png

 

Starting update

 

After prerequisite steps are completed,perform actual installation by right clicking on update-Install update pack

 

10.png

 

11

 

12.png

 

 

13.PNG

 

 

14

 

For installation status,again log file C:\Program Files\Microsoft Configuration Manager\Logs\CMUpdate.log or  Monitoring-Distribution Status-Updates and Servicing Status-Right click on update-Show status

 

16.PNG

 

15.PNG

Installation has finished

 

17.PNG

 

Upgrading Console

After console is reopened we’ll be asked for console upgrade

 

18

For progress take a look at C:\ConfigMgrAdminUISetup.log and C:\ConfigMgrAdminUISetupVerbose.log

Checking version

 

19

 

Administration-Site Configuration-Sites-Right click site-General

 

20.png

Client package update check

Software Library-Application Management-Packages-Check Last Update Date for Client Packages,it it’s “out-of-date”,right click on package-Distribute Content-select DP and click finish

 

21

 

22.png

 

Updating Boot images

Check update time

 

22.png

If it’s not close to current time,right click image-Distribute Content

 

23.png

Select Distribution Point

 

24.PNG

 

 

26.png

Upgrading Configuration Management Client

Administration-Site Configuration-Sites-Select site and click Hierarchy settings

 

27.png

Click on Client Upgrade tab-check Upgrase all clients check-box,optionally set time frame

 

28

Creating container in AD 

In AD container SCCM will publish object which need to be published in Active Directory.

I used PS script to create container:

# Get the distinguished name of the Active Directory domain
$DomainDn = ([adsi]"").distinguishedName
# Build distinguished name path of the System container
$SystemDn = "CN=System," + $DomainDn
# Retrieve a reference to the System container using the path we just built
$SysContainer = [adsi]"LDAP://$SystemDn"
# Create a new object inside the System container called System Management, of type "container"
$SysMgmtContainer = $SysContainer.Create("Container", "CN=System Management")
# Commit the new object to the Active Directory database
$SysMgmtContainer.SetInfo()

Setting permissions on the System Management container

Setting permissions allows SCCM site servers to publish site information to the container

Open Active Directory Users And Computers (start-run-dsa.msc) ,click on Advanced Features

Untitled10

Expand System Folder,right click System Manager and click Delegare Control

Untitled

Click on Add, on select users,computers or groups window click on Object Types and check for Computers as object types. Click on OK. Type the name of the SCCM server computer account and click on OK.

Untitled1

Add SCCM computer account

capture00

Click create custom task to delegate

Untitled3

Make sure This folder,existing objects in this folder,and creation of new objects in this folder is selected and click next

Untitled4

Untitled5

choose General, Property Specific and Creation/deletion of specific child objects. For the permissions, click on Full Control

Extending AD schema

SCCM uses AD to publish information about its sites and services, making it easily accessible to Active Directory clients. To leverage AD, we must extend the schema to create classes of objects specific to SCCM.

Navigate to \SMSSETUP\Bin\X64 folder and run extadsch.exe as administrator.

capture6.png

Check ExtADSch.log file (Located on system drive)

capture7

Installing Windows Features

For SCCM to work we need to install IIS,Net Framework 3.5,Background Intelligent Transfer (BITS),Windows Update Service,Common HTTP Features – Default Document, Static Content,Application Development – ASP.NET 3.5, .NET Extensibility 3.5, ASP.NET 4.5, .NET Extensibility 4.5, ISAPI extensions,Security – Windows Authentication,IIS 6 Management Compatibility – IIS Management Console, IIS 6 Metabase Compatibility, IIS 6 WMI Compatibility, IIS Management Scripts and Tools:

install-windowsfeature web-server,net-framework-features,bits,rdc,web-net-ext,web-net-ext45,web-wmi,web-scripting-tools,web-windows-auth,updateservices,NET-WCF-Services45

Then install Windows Assessment and Deployment Kit,choose component as per picture

Untitled7

Installing SQL Server 2014

For SQL Service Accounts,(SQL Server Agent,SQL Server Database Engine,SQL Server Reporting Service) best practice is to use domain accounts created only for this purpose.

Here is sample script:

import-module activedirectory
New-ADOrganizationalUnit -NAME "SYSTEM ACCOUNTS"
New-ADUser -name sql_sa -displayname sql_sa -samaccountname sql_sa -AccountPassword (ConvertTo-SecureString "Password01" -asplaintext -force) -Enabled $true -PasswordNeverExpires $true -Path "OU=SYSTEM ACCOUNTS,DC=contoso,DC=com" -userprincipalname sql_sa@contoso.com
New-ADUser -name sql_db -displayname sql_db -samaccountname sql_db -AccountPassword (ConvertTo-SecureString "Password01" -asplaintext -force) -Enabled $true -PasswordNeverExpires $true -Path "OU=SYSTEM ACCOUNTS,DC=contoso,DC=com" -userprincipalname sql_db@contoso.com
New-ADUser -name sql_srs -displayname sql_srs -samaccountname sql_srs -AccountPassword (ConvertTo-SecureString "Password01" -asplaintext -force) -Enabled $true -PasswordNeverExpires $true -Path "OU=SYSTEM ACCOUNTS,DC=contoso,DC=com" -userprincipalname sql_srs@contoso.com

capture00

Select Dtabase Engine Service,Reporting Service and Management tools

capture

Optionally,we can create dedicated instance

capture1

Specify service accounts we created earlier and collation:

capture3

capture4

Install and configure Reporting Service:

Capture5.PNG

SQL server configuration:

We need to open ports for SQL Server,1433 (instance connection) and 4022 (Service Broker)

New-NetFirewallRule -Displayname "Allow port 1433" -direction inbound -LocalPort 1433 -Protocol tcp -Action allow
New-NetFirewallRule -Displayname "Allow port 4022" -direction inbound -LocalPort 4022 -Protocol tcp -Action allow

Prior installation,SCCM checks if SQL server’s memory is limited,if not it throws an warning,to suppres it,set memory boundaries for SQL server,open SQL Server management studio:

Untitled7

Right click SQL server name and choose properties:

Untitled8

Set min/max memory:

Untitled9

Configure static TCP port:

capture00

capture12

capture13

capture14

Add SCCM computer account to local administrator group of SQL server:

untitled

Installing SCCM

Capture00.PNG

capture00

Capture.PNG

Choose path for file needed by SCCM server

capture

Name site code and name

Capture8.PNG

Specify SQL server and instance:

capture

Configure configuration method:

capture

Install Management Pack and Distribution Point:

capture10

Choose whether You want to update SCCM:

capture11

capture

 

And we are done !!!

Capture00.PNG

 

 

 

 

Configuration Manager 1602 introduced the support of SQL Server AlwaysOn Availability Groups
AlwaysOn Availability Groups provide high availability for multiple databases, and they can make use of multiple secondary replicas. Each secondary SQL Server replica has its own copy of the protected databases. AlwaysOn Availability Groups continuously synchronize transactions from the primary replica to each of the secondary replicas. This replication can be configured as synchronous or asynchronous to support local high availability or remote disaster recovery.

In this guide I used lab consisting of 2 SQL servers 2012 (SQL and SQL1) which will be member nodes of windows cluster named SQLCluster,1 machine where SCCM server 1602 will be installed,SCCM will be using Always on availability group instance named HA.Because this is lab environment,sccm server will be iSCSI target for SQL and SQL1,but in production environment iSCSI target should be dedicated machine

Installing windows iSCSI target
Install iSCSI Target Server role:

Untitled.png

Add 2 or more HDD’s,initialize it and format as NTFS partitions then create iSCSI disk

Untitled

Select disk,click next,choose Size and click Next again

Untitled.png

New-iSCSI target:

Untitled.png

Click Add to specify which hosts can access this iSCSi target:

Untitled.png

I added sql and sql1 (192.168.0.12/13)

Untitled.png

Untitled.png

Repeat same procedure for other disk(s)
Now on future cluster members (SQL and SQL1) from control panel click iSCSi initiator

Untitled.png

In targets type IP of iSCSI target server-Quick connect

Untitled

Initialize new disks and format it as NTFS on SQL and SQL1
Now,on both servers install Failover cluster features

Untitled.png

On either SQL and SQL1 open Cluster manager-Validate cluster

Untitled.png

Add both servers:

Untitled.png

Run full tests

Untitled.png

Untitled.png

Click Finish,create cluster wizard opens automatically,enter unused cluster name and unused IP.
If all went well,cluster is created and cluster computer name and IP address will be added automatically

Untitled.png

Now on both nodes install SQL server (standalone installation)

Untitled.png

Note!!,on another node repeat the same procedure but DON’T install reporting server feature
(because Reporting Server/Reporting SeverTemp database will be configured for replication)
Now,when SQL server is installed on both nodes (SQL and SQL1),let’s create Always on Availability group)
First,we need to enable this feature,on both node do the same procedure:
add SCCM computer account to local Administrator group:

Untitled.png

In SQL server configuration manager,right click on SQL Server (MSSQLSERVER)-properties

Untitled.png

Check Enable AlwaysOn Availability Groups and the restart MSSQLSER service)

Untitled

Now on node when Reporting services node is installed,open SQL server management studio,right click
Reporting Server database-Properties-Options-Recovery Model-Full

Untitled.png

Untitled.png

Again,right click ReportingsServer database-Task-BackUp

Untitled.png

Choose path and select OK

Untitled.png

Untitled.png

Repeat same procedure for ReportServerTemp database,these are conditions for database to be added to replication group

Now right click AlwaysOn High Availability-New Availability Group Wizard

Untitled

Set a name and click next,choose databases and click next again

Untitled.png

Click Add replica

Untitled.png

Type name of second node (where these 2 databases will be replicated) and click OK

Untitled

Enable automatic failover and set readability of database copy on secondary site to Yes

Untitled.png

Choose shared folder where wizard will copy databases chosen for replication and then restore them on
Secondary site.Note!!,on this shared folder give computer cluster account (SQLCluster- created by new cluster wizard) full NTFS permission

Untitled.png

Untitled.png

Now we need to create Availability Group Listener,it’s virtual DNS name and IP address which will be mapped to this Availability group.When install SCCM server this name will be submitted to SCCM wizard when we are prompted for SQL server where SCCM database will be installed.Right click on Availability Group listener-Add listener

Untitled.png

Type unused DNS name and IP address (the same procedure as when you created windows cluster at the beginning)

Untitled.png

Installing SCCM
When prompted for SQL server name type in name of Availability group listener

Untitled.png

And network location for database backup

Untitled.png

After installation SCCM database will be automatically added  to availability group

Untitled.png

In SCCM console,listener,and both nodes are shown

Untitled.png

Upgrading SCCM 1511 to 1602

Posted: June 22, 2016 in SCCM

We cannot upgrade directly from SCCM 2012 to 1602,we must first upgrade from 2012 to 1511

Download PowerShell script https://msdnshared.blob.core.windows.net/media/2016/03/EnableUpdateRing.txt
save it with ps1 extension and run it (pass CAS or stand-alone Primary server as parameter)

1.png

In SCCM console,click Administration,expand Cloud services-Updating and servicing,you should se update as “downloading”

1.png

If downloading is stuck for a quite long time,restart SMS_EXECUTIVE service

Downloading  progress can be monitored from log files (%SYSTEMDRIVE%\Microsoft Configuration Manager\Logs\dmpdownloader.log)

1.png

When download is done,status will be changed to available-right click and choose Run prerequisite check

1.png

Status can be seen from ConfigMgrPreq.log on root drive

 

1.png

When prerequisite check passed,install 1602 update,right click on it and choose Install Update Pack

1.png

Select features:

1.png

You can test upgrade on specific collection,or proceed without testing

1.png

Installation status can be monitored from (surprisingly,log files,CMUpdate.log)

1.png

If all went fine,You’ll get situation as in picture below:

1

Reopen SCCM console,you should see message like this:

1

 

When click OK,console upgrade begins:

1

Upgrade status can be seen in ConfigMgrAdminUISetupVerbose.log lof file,located in root drive

1.png

If everything is OK,you should see next picture:

1.png

Open SCCM console-about:

Now click Software library-Application Management-Packages-Update distribution points for Configuration Manager Client package and Configuration Manager Client Piloting Package

1.png

                 Upgrading SCCM clients on workstations/servers

 

Now we need to upgrade SCCM client,click Administration-Sites-Hierarchy Settings,click on Client upgrade tab and check Upgrade all clients in the hierarchy using production alerts

1.png

Create dynamic collection based on query: select *  from  SMS_R_System where SMS_R_System.ClientVersion != “5.00.8355.1000”

Collection will be populated with computers which have no SCCM client version 5.00.8355.1000

In add rule choose query rule

1.png

Click Edit query statement

1.png

Show query language:

Type select *  from  SMS_R_System where SMS_R_System.ClientVersion != “5.00.8355.1000”

and click OK,all computer with old SCCM clients will be put in this collection.
Now,right click on that collection and choose “Install client”

1.png

 

Upgrading Secondary site

Upgrading secondary site is quite easy,right click secondary site and choose upgrade.

1.png

1.png

Click on Show install status to see upgrade progress

1.png

You can also track upgrade status from secondary site server,log  file in %SYSTEMDRIVE%\ConfigMgrSetup.log

1.png

If all is all right,we should get next picture:

1.png

As example,i used CCleaner,from default location (C:\Program Files\CCleaner) i created shortcut in installation folder, (Right click CCleaner64.exe),if i were using x86 OS i would Right click CCleaner.exe)

Untitled.png

 

 

Untitled

Copy whole CCleaner folder to shared empty folder on SCCM server (i created empty folder install)

create batch script in install folder

Batch will take parameter (Install folder path),exported to %~1 variable,if that path doesn’t exist,it will be created

if not exist "%~1" mkdir "%~1"

rem ~dp0 is current directory (where .bat exists),it will copy all from CCleaner folder to path specified as .bat parameter (parameter will be specified later on),and will copy shortcut to user Desktop

copy /y "%~dp0CCleaner\*.*" "%~1"
copy /y "%~dp0CCleaner\ccleaner.lnk" "%Public%/Desktop"

Don’t forget to share folder install and to give SCCM server computer account Full Control NTFS permissions

Untitled.png

 

You can now deploy CCleaner as Application or as a package

Package = “run a command”
Application = “keep a program installed on this system”

Software Center can deploy and manage both packages and applications, but the Installed Software tab in Software Center only shows applications (a package could be simply a script that was run, not a real application that was installed). The Application Catalog supports both packages and applications, but there is limited information for a package that can be shown to users in the application catalog.

I decided to deploy CCleaner as Application.

From SCCM console click Software Library-Applications-Create Application

Untitled1.png

Click Manually specify the application information

Untitled2.png

Give application name and (optionally) version number

Untitled3.png

Click Next

Untitled4.png

For deployement tipe click Add

Untitled5

Again,click manually

Untitled6.png

Specify app name and click Next

Untitled7.png

Specify path to shared folder where bat file and CCleaner folder is located,

in installation progam type bat file name and parameter (Path for folder where CCleaner files will be located).

Script will check if folder specified in path  (C:\Program Files\CCleaner) exists,if not it first will create it and copy all files from \install\CCleaner folder on SCCM server to C:\Program Files\CCleaner on client computer

Untitled9

Specify how SCCM will check if program we want to deploy already exists,

click Add Clause

Untitled10.png

 

 

 

Untitled11.png

Choose install for system and specify whether app should be installed if user is logged on

Untitled12.png

Now when CCleaner is published,we need to deploy it,right click on app and select Deploy

Untitled13.png

 

Untitled14.png

Choose Distribution Point

Untitled15.png

 

Untitled16.png

 

Untitled17.png

After wizard finishes,CCleaner is available in Application Catalog on client computer,after click Install,it will create folder C:\Program Files\CCleaner

copy all files from CCleaner folder on SCCM server to C:\Program Files\CCleaner on client computer and will throw shortcut to desktop also

 

Untitled18.png

The primary site serves clients in well-connected networks.We can install secondary sites to extend the primary site for managing devices that have slow network connectivity to the primary site.If secondary site is not deployed, clients will submit inventories and download policies to the primary site that may be located in the remote location on a slow link.

You can  install secondary sites in SCCM 2012 in following scenarios:

  • More than 500 clients in a remote location
  • Need a local Management Point
  • Need a local Software Update Point
  • Need a local State Migration Point

 

In this post i simulated situation where 2 site exist,connected via VPN tunnel.I combined GNS3 and VMWare virtual machines.

Untitled0

Guide for creating site to site VPN can be found here.I covered installing primary SCCM site in this post

Preparing server where secondary SCCM 2012 site will be installed

Roles:

Web Server (IIS)

  • Application Development:
    • ISAPI Extensions
  • Security:
    • Windows Authentication
  • IIS 6 Management Compatibility
    • IIS 6 Metabase Compatibility
    • IIS 6 WMI Compatibility\

Features:

  • Remote Differential Compression
  • BITS
  • .NET Framework 3.5
  • .NET Framework 4

You can install them using this PowerShell code:

install-windowsfeature web-server, Web-App-Dev,web-isapi-ext,web-windows-auth,web-mgmt-compat,web-metabase,web-wmi,rdc,bits,net-framework-core

Open ports 1433 and 4022 (SQL),135 (RPC/WMI) and 445 (SMB)

New-NetFirewallRule -Displayname "Allow port 1433" -direction inbound -LocalPort 1433 -Protocol tcp -Action allow
New-NetFirewallRule -Displayname "Allow port 4022" -direction inbound -LocalPort 4022 -Protocol tcp -Action allow
New-NetFirewallRule -Displayname "Allow port 135" -direction inbound -LocalPort 135 -Protocol tcp -Action allow
New-NetFirewallRule -Displayname "Allow port 445" -direction inbound -LocalPort 445 -Protocol tcp -Action allow

Add SCCM server computer account (SCCM-192.168.10.11) to local administrator group of server where we will install secondary site (SCCM1 -192.168.30.11)

Run next commands on secondary (SCCM1) server

#to which computer SCCM needs to be added to Local Administrators Group

$Group = [ADSI]"WinNT://SCCM1/Administrators"

#computer which needs to be added to Administrator group to SCCM1 server

$Computer = [ADSI]"WinNT://test.com/SCCM$"

#Adding SCCM to Local Administrator groups in SCCM1 

$Group.Add($Computer.Path)

 

Give the Secondary Site computer account (SCCM1) full control of the System Management container. This will allow the Secondary Site Server to publish information about itself to Active Directory

In Active Directory Users and Computers click View-Advanced Features:

Untitled

In Object Types click computers

Untitled1

Add computer account of secondary server ang give it full controll

Untitled2.png

During installing primary site,we are prompted to choose folder where SCCM will download updates,among updates it will download SQL Server Express.

I copied content of this folder to shared folder on secondary  server (SCCM1) ,and gave SCCM and SCCM1 computer account (where main SCCM site is located) Full Control NTFS permissions

In this folder i copied SMSSETUP folder from installation media

Untitled.png

Next,in SMSSETUP folder,create another folder Redist

During installation of primary site,on the Prerequisite Downloads page, wizard ask for folder location where to download the updates

Untitled5

From that location on primary (SCCM) server,copy all files to Redist folder on secondary server (SCCM1)

Untitled0.png

On primary server,from SCCM console click Administration-Sites-Create Secondary Site

Untitled.png

Enter site code,name and server where secondary SCCM site will ne installed

Untitled0.png

Enter path to shared folder where installation files are located

Untitled0.png

New instance of SQL Server Express will be installed

Untitled.png

Because we already installed IIS,don’t check Install and configure IIS,optionally,we can install branch cache,i am using self-signed certificate,it’s not advisable for production

Untitled.png

Specify Drive Space for Distribution Point

Untitled.png

Choose wether or not to set boundry groups.Boundaries represent network locations on the intranet where Configuration Manager clients are located. Boundary groups are logical groups of boundaries that provide clients access to resources

Untitled.png

We can check installation status clicking on “Show Install Status”

Untitled.png

Check sender.log file on primary server

Untitled0.png

And ConfigMgrSetup.log on root drive on secondary server (SCCM1)

Untitled0.png