Often Overlooked Step when Configuring WSUS to Use SQL Server Always On

Documenting this issue on my personal tech blog because I got stuck with this for the past couple of weeks.

Scenario

• Windows Server 2016, for ConfigMgr Primary Site Server and multiple Software Update Points
• Multiple SUPs mean multiple WSUS
• WSUS Servers using a common SQL Server, per Microsoft best practice
• SQL Server is running Always On AG

 

Problem

Once all the WSUS Servers are configured, and the SUSDB is added to the Availability Group, both WSUS still looks ok. But when you execute failover, one or all all of the WSUS will fail.

 

Possible Reason

When you run the postinstall of WSUS, it configures the SUSDB, and adds the required logons on the current Primary Replica. But when you add the SUSDB into the Availability Group, the logons are not created on the current Secondary Replica(s) as of that time. Therefore you have to add a logon on all SQL Server Replicas of all WSUS Servers that will use the said SUSDB.  This is an often overlooked step, as per this forum post.

I haven’t seen an official support statement for or against using WSUS in an AlwaysOn availability group.

That said, as the only way you’re going to be able to make use of an AlwaysOn (unless it’s part of a System Center deployment) is by changing the database settings found in the registry under “HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup” (based on WSUS on Server 2012 R2), so I doubt it’s something they’ve seriously thought about.
In principle, the implementation steps would look like this:
1. Set up WSUS as per normal using the actual hostname of the initial SQL Server.
2. Take a back up of the WSUS database (a pre-requisite for including in the availability group).
3. Add the WSUS database to the preferred availability group.
4. Create a login for the computer account of the WSUS server on each SQL Server that is part of the AlwaysOn group (an often overlooked step until a failover actually occurs).
5. Stop the WSUS service.
6. Update the registry settings.
7. Start the WSUS service.

Referencing myself on the existing logins of the current Primary Replica, I created logins on the other Secondaries for all my WSUS Servers, and have given them public, securityadmin and sysadmin roles. My multiple WSUS worked after this step.

And when I say “works,” it means that I can open the WSUS console

• Simultaneously on all WSUS Servers at the same time
• After failing over to all my Always On replicas

 

Summary:

This was how I setup my multiple WSUS (assumes Always On AG is fully setup)

1. Create WSUS1, use SQL and connect it to the AG Listener
2. Create WSUS2, use SQL and connect it to the AG Listener
3. Configure SUSDB for Always On requirements (Full Recovery Model, perform Full Backup)
4. Add SUSDB to Availability Group
5. Add all the necessary logins of all WSUS Servers on all SQL Server replicas
6. Test
   • Connect WSUS1 and WSUS2 consoles, both should display properly
   • Failover one by one to all SQL Replicas, both WSUS consoles should still  display properly

Hope this helps!

jay paloma  |  10 sep 2017  |  singapore

SCCM 1606 SQL Server Views Documentation

If you are working on SCCM custom reports, you may have wished that there should be a reference out that you could use as reference to navigate through the countless database views available in SQL Server for SCCM. There is in fact such a reference, published by Microsoft last November 2016

Download the SCCM ConfigMgr SQL Views reference from here: https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b

Other useful references

• SQL Server Views in System Center 2012 Configuration Manager
• Hardware Inventory SQL Views
• Discovery SQL Views
• Software Update SQL Views

jay paloma  |  26 mar 2017  |  singapore

Windows 7 Deployment Basics

I recently conducted a Windows 7 Deployment Basics webcast last 8th Dec 2011, courtesy of Microsoft Australia/New Zealand Windows Client team. Once they give me the link, I will update this page

Microsoft Security Compliance Manager

Microsoft recently launched the Microsoft Security Compliance Manager. According to the Microsoft website:

Take advantage of the experience of Microsoft security professionals, and reduce the time and money required to harden your environment. This end-to-end Solution Accelerator will help you plan, deploy, operate, and manage your security baselines for Windows client and server operating systems, and Microsoft applications. Access the complete database of Microsoft recommended security settings, customize your baselines, and then choose from multiple formats—including XLS, Group Policy objects (GPOs), Desired Configuration Management (DCM) packs, or Security Content Automation Protocol (SCAP)—to export the baselines to your environment to automate the security baseline deployment and compliance verification process. Use the Security Compliance Manager to achieve a secure, reliable, and centralized IT environment that will help you better balance your organization’s needs for security and functionality.

Let’s see what SCM can do

1. Security policies for Windows versions. SCM comes with default security policies for Microsoft products, and for Windows servers, even the different server roles. This is cool for me as an infra guy!

2. View policy details. With SCM you can view the details of the different security policies if your objective is just to visually compare

3. Export, compare and export the baseline to Excel. These are what you can do to a specific security policy

4. Update baselines for new Microsoft products. Products launched after SCM will also be updated by downloading baselines from Microsoft.

5. Download Microsoft Security Guides. The security guides for Microsoft products are now included in Microsoft Security Compliance Manager. Personally — this is how I discovered this tool, because I was looking for the security guide for Windows Server 2008 R2!

6. Create a GPO Backup of the policy and restore it to a GPO. After you customize the security policy, you can export the policy as a GPO backup, then import it to a GPO in Active Directory.

You can download Microsoft Security Compliance Manager here

Download: Microsoft Security Compliance Manager

Download Microsoft Security Compliance Manager here

About Microsoft Security Compliance Manager

Take advantage of the experience of Microsoft security professionals, and reduce the time and money required to harden your environment. This end-to-end Solution Accelerator will help you plan, deploy, operate, and manage your security baselines for Windows client and server operating systems, and Microsoft applications. Access the complete database of Microsoft recommended security settings, customize your baselines, and then choose from multiple formats—including XLS, Group Policy objects (GPOs), Desired Configuration Management (DCM) packs, or Security Content Automation Protocol (SCAP)—to export the baselines to your environment to automate the security baseline deployment and compliance verification process. Use the Security Compliance Manager to achieve a secure, reliable, and centralized IT environment that will help you better balance your organization’s needs for security and functionality.

Key Features & Benefits

• Centralized Management and Baseline Portfolio: The centralized management console of the Security Compliance Manager provides you with a unified, end-to-end user experience to plan, customize, and export security baselines. The tool gives you full access to a complete portfolio of recommended baselines for Windows® client and server operating systems, and Microsoft applications. The Security Compliance Manager also enables you to quickly update the latest Microsoft baseline releases and take advantage of baseline version control.
• Security Baseline Customization: Customizing, comparing, merging, and reviewing your baselines just got easier. Now you can use the new customization capabilities of the Security Compliance Manager to duplicate any of the recommended baselines from Microsoft—for Windows client and server operating systems, and Microsoft applications—and quickly modify security settings to meet the standards of your organization’s environment.
• Multiple Export Capabilities: Export baselines in formats like XLS, Group Policy objects (GPOs), Desired Configuration Management (DCM) packs, or Security Content Automation Protocol (SCAP) to enable automation of deployment and monitoring baseline compliance.
• Security baselines for Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Hyper-V, Windows 7, Windows Vista, Windows XP, BitLocker Drive Encryption, Windows Internet Explorer 8, Microsoft Office 2010, and Microsoft Office 2007 SP2
• Setting packs for Windows 7, Internet Explorer 8

The Security Compliance Manager Getting Started Guide is now available to download. Download this short guide to quickly set up and customize the Security Compliance Manager (SCM) tool. The guide also includes brief instructions on using the SCM tool to deploy and monitor security baselines for the latest server and client operating systems from Microsoft.

MSDN Blogs: What Does it Mean to be Compatible with Windows 7?

There are a number of terms thrown around in the world of app compat that are vaguely defined at best. Now, I’m not one to suggest that tedium is a good practice in general, but in some cases understanding the true technical definition of a term can lead to significant time savings when that definition is applied to a project.

Compatible is the most important one. What does it mean to call an application Compatible with Windows 7? (Some people refer to this as Windows 7 Compliant also.) I have found some significant variation in peoples’ understanding of this, and the wrong understanding can lead to you chasing unachievable goals.

More

Hello world!

I will move my stuff little by little from Security is a State of Mind http://msforums.ph/blogs/jpaloma to here.

jay paloma  |  12 oct 2010  |  singapore