At a recent client engagement we discovered that Microsoft SQL Server Enterprise edition was installed on the ConfigMgr Primary Site Server.  Technically this is not a problem, but it is only needed if you expect to have more than 50,000 clients*.  As this environment wasn’t anywhere close to the limit so there was no need to pay the extra licensing cost of Enterprise edition (Standard edition comes with the ConfigMgr licenses).

These steps are largely based on the Jonathan Kehayias approach as documented by Brady Upton at MSSQLTips.

Steps for SQL Enterprise to Standard downgrade

• On each database verify that no Enterprise features are utilized (SELECT * FROM sys.dm_db_persisted_sku_features)

select ‘Master’ as [Database], * from [master].[sys].[dm_db_persisted_sku_features]

select ‘Model’ as [Database], * from Model.[sys].[dm_db_persisted_sku_features]

select ‘msdb’ as [Database], * from msdb.[sys].[dm_db_persisted_sku_features]

select ‘tempdb’ as [Database], * from tempdb.[sys].[dm_db_persisted_sku_features]

select ‘CM_P01’ as [Database], * from CM_P01.[sys].[dm_db_persisted_sku_features]

select ‘SUSDB’ as [Database], * from SUSDB.[sys].[dm_db_persisted_sku_features]

select ‘ReportServer’ as [Database], * from ReportServer.[sys].[dm_db_persisted_sku_features]

select ‘ReportServerTempDB’ as [Database], * from ReportServerTempDB.[sys].[dm_db_persisted_sku_features]

• Document databases, security, maintenance plans, and jobs
• Verify the SQL version number and ensure install files are available (SELECT @@VERSION)
• Stop and disable backup software
• Stop ConfigMgr, IIS, and Windows Update services (set to disabled if desired)
• Backup databases (system and user)
• Stop SQL services
• Copy the master, model and msdb database files (.mdf and .ldf) to another location
• Uninstall SQL Enterprise instance (all features)
  • The Shared tools do not have to be uninstalled; however, if they are not then reporting the SQL edition in the future will be confusing
• Reboot
• Install new SQL Standard instance as required by ConfigMgr being sure to keep the same instance name and file/folder paths.
  • Review the Required and Optional configurations for SQL server (64-bit, SQL_Latin1_General_CP1_CI_AS, Database Engine, Windows Authentication, min/max Memory, nested triggers, CLR integration, static TCP ports, etc.)
  • If the original SQL ConfigurationFile.ini is still around, installing based on this file can make all of the configurations fool proof.
• Patch SQL to the same version as before
• Verify the SQL version and edition (SELECT @@VERSION)
• Stop SQL Server and copy/restore the system databases
• Configure Trace flags (see section below)
• Start SQL server and verify databases, security, and jobs are as before
  • If login fails, use PSEXEC to start SQL Management Studio as the SYSTEM account, then recreate any SQL Logins needed
• Enable common language runtime (CLR) integration (sp_configure ‘clr enabled’,1; reconfigure)
• Enable and start IIS, and Windows Update services… verify WSUS is working
• Enable and start ConfigMgr services
• Verify event Viewer and ConfigMgr logs and monitoring to ensure ConfigMgr is healthy
• Re-enable and start backup software

SQL Trace Logs

Using this method is simple and easy, but there is one additional thing to keep in mind… SQL Trace flags (thanks Allen for pointing this out).  When installing SQL, trace flags are not enabled / added by default; this is taken care of by the ConfigMgr installation.  Since we are not doing a ConfigMgr installation or a site reset, etc. these options need to be added manually.

• Open SQL Server Configuration Manager
• Navigate to SQL Server Services -> SQL Server… -> Properties
• Add "-T8295"
• Add "-T4199"
• Apply, stand on one foot, OK, Close

Executing DBCC TRACEON (4199,-1) and DBCC TRACEON (8295,-1) in SQL Server Management Studio will enable these flags as seen by executing DBCC TRACESTATUS (-1).  However, this only affects the current session and they need to be added as startup flags.

SQL and ConfigMgr References

• Downgrade from SQL Server Enterprise Edition to Standard Edition @ https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/
• Supported operating systems for sites and clients for System Center Configuration Manager @ https://technet.microsoft.com/en-us/library/mt589738.aspx#bkmk_ClientNumbers
• Support for SQL Server versions for System Center Configuration Manager @ https://technet.microsoft.com/en-us/library/mt589592.aspx
• SQL Server for ConfigMgr 2012, eBook and Top 10 Database Issues@  http://blogs.technet.com/b/smartinez/archive/2013/03/06/sql-for-configmgr-2012.aspx
• SQL Trace Flags and ConfigMgr @ http://blogs.technet.com/b/smartinez/archive/2014/04/25/sql-trace-flags-and-configmgr.aspx
• Microsoft KB974006 @ https://support.microsoft.com/en-us/kb/974006

Additional / Related References

• How to Move databases for SCCM 2012 @ http://smsimpossible.blogspot.com/2012/09/how-to-move-databases-for-sccm-2012.html
• How to Move the Site Database @ https://technet.microsoft.com/en-us/library/bb680707.aspx
• Move the site database in SCCM 2007 R2 @ http://blogs.technet.com/b/sudheesn/archive/2010/11/08/move-the-site-database-in-sccm-2007-r2.aspx
• ConfigMgr 2007: How to move the Site Database @ http://blogs.technet.com/configurationmgr/archive/2010/01/28/configmgr-2007-how-to-move-the-site-database.aspx 
• Moving the Report Server Databases to Another Computer @ https://msdn.microsoft.com/en-us/library/ms156421%28v=sql.110%29.aspx 
• Migrate a Reporting Services Installation (Native Mode) @ https://technet.microsoft.com/en-us/library/ms143724%28v=sql.110%29.aspx 
• Backup and Restore Operations for Reporting Services @ https://technet.microsoft.com/en-us/library/ms155814%28v=sql.110%29.aspx
• How to move a 2005 Reporting Services database from a computer that is running Reporting Services to another computer @ https://support.microsoft.com/en-us/kb/842425 
• SQL Server Upgrade for the Site Database Server @ https://technet.microsoft.com/en-us/library/gg682077.aspx?#BKMK_SupConfigUpgradeDBSrv

Chad April 20, 2016

Posted In: Uncategorized

In a new ConfigMgr 2012 R2 SP1 environment the Reporting Services point was proving a bit challenging to install.  After setting all of the required permission it was finally happy. 

Required Permissions

On the SQL Server Reporting Services server, an account (in this case a domain user "functional" or "service" account) needed the following:

• Membership in the server’s local Administrators group
• SSRS Site Settings -> System Administrator, System User
• SSRS Folder Settings on the root folder -> Browser, Content Manager, My Reports, Publisher, Report Builder

Scenario Details

This ConfigMgr environment has a complex configuration with 3 different SQL servers in play.

• Server1: ConfigMgr Primary Site server
• Server2: server running SQL Server Database Engine role and configured as the ConfigMgr Site Database server
• Server3: server running SQL Server Reporting Services role
• Server4: server running SQL Server Database Engine role with only the ReportServer database

With Server1 (ConfigMgr) and Server2 (SQL DB) configured and most functionality working (software deployment, software update deployment, OS deployment, inventory, etc.) it was time to install Reporting Services point.  We created a new ConfigMgr Site Server for Server3 using a domain user account.  However, when attempting to install the Reporting Services point the following error was encountered:

• Unable to locate any configured SRS instances on the server.  Verify SRS is installed, accessible, and correctly configured.
• The "Reporting Services server instance" is blank.

We knew that SSRS was installed, configured, and working as there were two other applications already using the SRS instance in a production capacity.

We verified the domain user account could actually access the SSRS website from Server1 (the ConfigMgr server) and discovered

• the account was a member of the local Administrators group
• the account had System Administrator rights to the SSRS Site
• the account could not see any existing reports or report folders and the following message displayed: "User ‘<domain>\<userID>’ does not have required permissions.  Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed."

As the screen shot shows, UAC was actually disabled already.

Once the domain user account was given rights to the root folder, the Reporting Services point role could see the Reporting Services server instance the the role installed without issue.

After the role installed, ConfigMgr reset its own permissions to give the domain user account only "ConfigMgr Report Users, ConfigMgr report Administrators" roles.

Also, the logs for the Reporting Services point are on the SSRS server (Server3 in this case), and located at C:\SMS\logs

Chad August 27, 2015

Posted In: Uncategorized

When preparing a new Windows Server 2012 R2 system for a new ConfigMgr 2012 R2 site, I ran into an error installing the Windows ADK.  In this case it is version 10; however, I believe the same scenario would apply to 8.1 Update, 8.1, 8, etc. 

The installation appears to be working, then performs a rollback with the following error:

Image path is [\??\C:\Program Files (x86)\Windows Kits\10\Assessment and Deployment Kit\Deployment Tools\amd64\DISM\wimmount.sys

Could not acquire privileges; GLE=0x514

Returning status 0x514

Solution / Workaround

What eventually worked around the problem was to run the installation as the SYSTEM account.

Getting to the workaround

Multiple other solutions were attempted before resorting to the SYSTEM account for installation including

• Run as Administrator
• Run a Command Prompt as Administrator then run ‘ADKsetup.exe‘
• multiple system reboots
• manually creating the folder structure and file
• manually running the ‘Windows Deployment Tools-x86_en-us.msi‘.  Strangely this succeeded; however, when re-running the ADKsetup.exe, the same failure occurred.
• manually running the ‘Windows System Image Manager on amd64-x86_en-us.msi‘.  Strangely this succeeded; however, when re-running the ADKsetup.exe, the same failure occurred.
• both the system drive (Drive C:) and a data drive (E:) were attempted
• disabling the antivirus / antimalware software (Sophos)

None of these made any difference.

Several forum posts were found with a resolution pointing to a blog that has since been taken down.  That solution required removing the computer from the domain.  This solution was not attempted.

Executing the workaround

There are a few ways to gain SYSTEM account access; however, I took the interactive route and used PSEXEC from Sysinternals.

From an elevated Command Prompt (Run as Administrator), run ‘PSEXEC.exe -s -d -i cmd.exe‘

A new Command Prompt will be generated.  run ‘whoami‘ to ensure you are running as SYSTEM, then run ‘adksetup.exe‘

Thanks to Adam for working through the issue with me.

Chad August 17, 2015

Posted In: Uncategorized