The following article will cover a basic install of System Center Service Manager 2012 R2.   The concept is to perform a limited deployment of SCSM, similar to our deployment guide on TechNet:  http://technet.microsoft.com/en-us/library/hh519675.aspx  The deployment guide on TechNet demonstrates Service Manager in a Two Server model (typical for lab and test environments), and a Four Server Model (More typical for a scaled out production environment).  However for this article, I will be choosing a 3 server model, where all the SQL components are installed on a single SQL server, with a dedicated SCSM Management server, and dedicated SCSM Data Warehouse management server.  I feel this is a more typical scenario for lab testing and pilot environments where we don't want to deploy SQL on the SCSM management servers themselves, but don't need two independent SQL servers.  This is to be used as a template only, for a customer to implement as their own pilot or POC, or customized deployment guide. It is intended to be general in nature and will require the customer to modify it to suit their specific data and processes.

This is not an architecture guide or intended to be a design guide in any way. This is provided "AS IS" with no warranties, and confers no rights. Use is subject to the terms specified in the Terms of Use.

Server Names\Roles:

• DB02                 SQL Database Services, SQL Analysis Services, SQL Reporting Services.
• SCSM01            Management Server
• SCSM01DW      Data Warehouse Management Server

Windows Server 2012 R2 will be installed as the base OS for all platforms.  All servers will be a member of the AD domain.

SQL 2012 Enterprise with SP1 will be the base standard for all SQL Database, Analysis, and Reporting services. 

High Level Deployment Process:

1.  In AD, create the following accounts and groups, according to your naming convention:

• DOMAIN\scsmsvc                 SM Server service account
• DOMAIN\scsmwf                  SM Mail Enabled Workflow account
• DOMAIN\scsmrep                 SM reporting and analysis account
• DOMAIN\SQLSVC                 SQL service account
• DOMAIN\SCSMadmins         SM Administrators security group

2.  Add the three SCSM service accounts, and the domain user accounts for yourself and your team to the “SCSMadmins” group.

3.  Install Windows Server 2012 R2 to all server role servers.

4.  Install Prerequisites and SQL 2012 wSP1.

5.  Install the Management Server

6.  Install the Data Warehouse Server

7.  Post install configurations 

Prerequisites:

1.  Install Windows Server 2012 R2 to all Servers

2.  Join all servers to domain.

3.  Add the “SCSMAdmins” domain global group to the Local Administrators group on each server.

4.  On the SCSM and SCSMDW server, Open Powershell as an administrator, and install .NET 3.5 by running: 

Install-WindowsFeature NET-Framework-Core

***Note – .NET 3.5 is removed from the OS by default, and therefore the content is not present in the OS to add this feature.  You might need to provide a “–source” parameter to installation media for Windows Server 2012 R2 in order to add this.  Such as “-source D:\sources\sxs”

5.  On the SCSM and SCSMDW server, install the SQL 2012 Native Client, and the SQL 2012 Analysis Management Objects, from http://www.microsoft.com/en-us/download/details.aspx?id=29065. 

6.  Install all available Windows Updates.

7. Install SQL 2012 with SP1 to the DB server role

• Setup is fairly straightforward. This document will not go into details and best practices for SQL configuration. Consult your DBA team to ensure your SQL deployment is configured for best practices according to your corporate standards.
• Run setup, choose Installation > New SQL Installation…
• When prompted for feature selection, install ALL of the following:
  • Database Engine Services
  • Full-Text and Semantic Extractions for Search
  • Analysis Services
  • Reporting Services - Native
• Optionally – consider adding the following to ease administration:
  • Management Tools – Basic and Complete (for running queries and configuring SQL services)
• On the Instance configuration, choose a default instance, or a named instance. Default instances are fine for testing and labs. Production clustered instances of SQL will generally be a named instance. For the purposes of the POC, choose default instance to keep things simple.
• On the Server configuration screen, set SQL Server Agent to Automatic.  You can accept the defaults for the service accounts, but I recommend using a Domain account for the service account.  Input the DOMAIN\sqlsvc account and password for Agent, Engine, Analysis, and Reporting.
• On the Collation Tab – you can use the default which is SQL_Latin1_General_CP1_CI_AS or choose another supported collation.  The default is only supported for English-only installations.  See the product documentation for SCSM for additional details.
• On the Account provisioning tab – add your personal domain user account or a group you already have set up for SQL admins. Alternatively, you can use the SCSMAdmins global group here. This will grant more rights than is required to all SCSMAdmin accounts, but is fine for testing purposes of the POC.
• On the Data Directories tab – set your drive letters correctly for your SQL databases, logs, TempDB, and backup.
• On the Analysis Services screen, add your personal domain user account or a group you already have set up for SQL admins. Alternatively, you can use the SCSMAdmins global group here. This will grant more rights than is required to all SCSMAdmin accounts, but is fine for testing purposes of the POC.  Customize data directories for Analysis file locations if needed, and click Next.
• On the Reporting Services Configuration – choose to Install and Configure. This will install and configure SRS to be active on this server, and use the default DBengine present to house the reporting server databases. This is the simplest configuration. If you install Reporting Services on a stand-alone (no DBEngine) server, you will need to configure this manually.
• Continue accepting defaults until you reach Install.  Installation will run then complete.
• You will need to disable Windows Firewall on the SQL server, or make the necessary modifications to the firewall to allow all SQL traffic.  See http://msdn.microsoft.com/en-us/library/ms175043.aspx          

             

Step by step deployment guide:

1.  Install the Management Server role on SCSM. You can also refer to: http://technet.microsoft.com/en-us/library/hh519668.aspx

• Log on using your personal domain user account that is a member of the SCSMAdmins group.
• Run Setup.exe
• Click Install > Service Manager Management Server
• Provide a Name, Org, and a product key, or select to install the 180 day evaluation.  Accept the license agreement and click Next.
• The Prereq checker runs.  Observe any critical or warnings.  At this point you should install the Report Viewer from the link in the checker, as that ships with the SCSM media.  Check prereqs again.  Common issues at this point will be memory and CPU checks throwing a warning.  This is fine for a lab, but should be corrected for any pilots or production work.  Click Next.
• For the Management Server role, we will use a remote database server.  Input the DB server name and choose an instance.  You must be logged on with an account that has SA rights over to remote SQL server in order to create and configure the DB.  If you get an error about the collation, click OK.  This is normal for SQL_Latin1_General_CP1_CI_AS.  See:  http://blogs.technet.com/b/momteam/archive/2012/05/25/clarification-on-sql-server-collation-requirements-for-system-center-2012.aspx  Select to create a new database, accept default size, and modify the path for the DB files if necessary.  Click Next.
• Choose a Service Manager Management group name.  If you also have OpsMgr in the environment, its a best practice to always use distinct MG names.  Choose your group DOMAIN\SCSMAdmins.  Click Next.
• Input the Service manager service account we created above.  Test the credentials, then click Next.
• Input the Service manager workflow account we created above.  Test the credentials, then click Next.
• Join the customer experience program, or not.  Next.
• Choose to leverage Microsoft Update, or not.  Next.
• Click Install.  When setup completes, backup and save the encryption key for this management group.

2.  Install the Data Warehouse Management Server role on SCSMDW. You can also refer to: http://technet.microsoft.com/en-us/library/hh519780.aspx.

• The first step for the DW install, is to prepare the SRS server.  We must perform this anytime the SQL Reporting server is installed remotely, on a different server than the SCSM Data Warehouse Management server.  See the following for instructions:  http://technet.microsoft.com/en-us/library/hh519664.aspx
• Once you have prepared the remote SRS server, log on to the SCSMDW server using your domain user account that is a member of the SCSMAdmins group.
• Run Setup.exe
• Click Install > Service Manager Data Warehouse Management Server
• Provide a Name, Org, and a product key, or select to install the 180 day evaluation.  Accept the license agreement and click Next.
• The Prereq checker runs.  Observe any critical or warnings.  Common issues at this point will be memory and CPU checks throwing a warning.  This is fine for a lab, but should be corrected for any pilots or production work.  Click Next.
• For the DW Management Server role, we will use a remote database server.  Input the DB server name for each database and choose an instance.  You must be logged on with an account that has SA rights over to remote SQL server in order to create and configure the DB.  If you get an error about the collation, click OK.  This is normal for SQL_Latin1_General_CP1_CI_AS.  See:  http://blogs.technet.com/b/momteam/archive/2012/05/25/clarification-on-sql-server-collation-requirements-for-system-center-2012.aspx  Select to create a new database, accept default size, and modify the path for the DB files if necessary.  Click Next.  This wizard allows us to scale out service manager across multiple SQL servers for the best performance, but for this purpose, we will be deploying to a single SQL server for all database components.
• On the Configuration screen, provide a Management Group name.  A good rule of thumb is to use your SCSM management group name we used above, prefixed by DW_.  I will use DW_SCSM.  Choose your SCSMAdmins group.  Next. 
• On the reporting server screen, type in the name of the remote SSRS server, and choose an instance.  We will validate the URL before letting you continue.  Check the box that confirms you have manually prepared this server for SSRS integration with SCSM.
• For the service account, enter in DOMAIN\scsmsvc, and test the credential.
• For the reporting account, enter in DOMAIN\scsmrep, and test the credential.
• For the Analysis Services OLAP screen, input the remote DB server name, and choose an instance.  Create a new database, and provide a path if needed different than the default.
• For the Analysis Services credential, we will use the same credential that we used for reporting:  DOMAIN\scsmrep.  This account MUST be a local administrator on the SQL Analysis server, so ensure that is done in advance.
• Choose whether to join the CEIP, and click Next.
• Choose whether to use Microsoft update, and click Next.
• Choose Install.  When setup completes, backup and save the encryption key for this management group.

3.  Verify the installation:  You can also refer to:  http://technet.microsoft.com/en-us/library/hh519793.aspx

• Log on SCSM01 using your domain user account that is a member of the SCSMAdmins group.
• Open the Service Manager Console.  Connect to SCSM01.
• Ensure the console opens.

4.  Register the Data Warehouse.  You can also refer to http://technet.microsoft.com/en-us/library/hh519811.aspx

• In the Service Manager console – select Administration.
• Click the link to Register the Service Manager Data Warehouse.  This launches a wizard.
• Input the DW server name, and select Test Connection.  Next.
• Accept the default Run As account, and click Next.
• Type in the password for the service account, and Next.
• Click Create.  Click Close.  Click OK.
• This process takes a considerable amount of time to complete (two hours or more).  To validate this – in the console select Data Warehouse > Data Warehouse Jobs.  Examine MPSyncJob details.  When it is done, all batches will be in Associated status, and you will see at least the following 5 jobs in the DW Jobs view:
  • Extract_<Service Manager management group name>
  • Extract_<Data Warehouse management group name>
  • Load.Common
  • Transform.Common
  • MPSyncJob

5.  Deploy the Self-Service Portal.

• http://technet.microsoft.com/en-us/library/hh667344.aspx
• The Self-Service Portal consists of two elements: a SharePoint website and a web content server.  Typically I will deploy a single server running Windows Server 2008R2 and SharePoint 2010 Foundation, then apply SharePoint SP1, then request an SSL cert for the machine via IIS, then install the Web Content and SharePoint webparts on that single server.  There is a pretty good walk-thru here:  http://blogs.perficient.com/microsoft/2012/10/service-manager-2012-self-service-portal-step-by-step-part-1/  
• The steps would be something like:
• Deploy a Windows Server 2008R2 VM for the SSP
• Deploy a Windows Server 2008R2 VM for the SQL database running SQL 2008R2 SP1
• Install SharePoint 2010 Foundation on the SSP server (do not run config wizard)
• Install SharePoint 2010 SP1 update.
• Run SharePoint config Wizard.
• Add a SSL certificate to the SSP server in IIS via Create Domain Request.
• Install the SSP (both parts) using :444 first for the content server, and then :443 for the portal.  (defaults are backwards)

6.  Configure the Active Directory Connector

• http://technet.microsoft.com/en-us/library/hh519809.aspx

7.  Configure the Operations Manager Alert Connector and CI Connector

• http://technet.microsoft.com/en-us/library/hh524270.aspx
• Import Management Packs
• Create Alert Connector
• Create CI Connector

8.  Configure the Configuration Manager CI Connector

• http://technet.microsoft.com/en-us/library/hh519733.aspx

9.  Configure the Orchestrator Connector:

• http://technet.microsoft.com/en-us/library/hh495619.aspx
• The Account used in the connector wizard needs to have Read and List permissions on the Root Runbook folder in Orchestrator Run book designer for the connector wizard to complete successfully.  The documentation does not list this information.

10.  Configure the SCVMM Connector

• http://technet.microsoft.com/en-us/library/hh519785.aspx
• The service account to connect to SCVMM requires rights to the SCVMM server OS and to read properties from SCVMM itself.

11.  Set up and configure Notifications:

• http://technet.microsoft.com/en-us/library/hh519719.aspx

12.  Configure SCOM agents for monitoring

• The SCOM agent is installed by default on all SCSM 2012 SP1 and later servers, it is not configured.
• Open the control panel on your SCSM servers and add your SCOM management group information.
• Ensure your SCOM deployment allows manually installed agents.
• http://technet.microsoft.com/en-us/library/hh524312.aspx

System Center Orchestrator 2012 is extremely easy to setup and deploy.  There are only a handful of prerequisites, and most can be handled by the setup installer routine.

The TechNet documentation does an excellent job of detailing the system requirements and deployment process:

http://technet.microsoft.com/en-us/library/hh420337.aspx

The following document will cover a basic install of System Center Orchestrator 2012 at a generic customer.  This is to be used as a template only, for a customer to implement as their own pilot or POC deployment guide.  It is intended to be general in nature and will require the customer to modify it to suit their specific data and processes.

SCORCH can be scaled to match the customer requirements. This document will cover a typical two server model, where all server roles are installed on a single VM, and utilize a remote SQL database server.

This is not an architecture guide or intended to be a design guide in any way.

Server Names\Roles:

SCO01          Orchestrator 2012 role server

• Management Server
• Runbook Server
• Orchestrator Web Service Server
• Runbook Designer client application

DB01                  SQL 2012 with SP1 Database Engine Server

Windows Server 2012 R2 will be installed as the base OS for all platforms.  All servers will be a member of the AD domain.

SQL 2012 with SP1 will be the base standard for all database services. SCORCH only requires a SQL DB engine (locally or remote) in order to host SCORCH databases.

High Level Deployment Process:

1.  In AD, create the following accounts and groups, according to your naming convention:

a.  DOMAIN\scorchsvc                       SCORCH Mgmt, Runbook, and Monitor Account

b.  DOMAIN\ScorchUsers                   SCORCH users security global group

c.  DOMAIN\sqlsvc                              SQL Service Account

2.  Add the domain user accounts for yourself and your team to the ScorchUsers group.

3.  Install Windows Server 2012 R2 to all server role members.

4.  Install Prerequisites.

5.  Install the SCORCH Server.

Prerequisites:

1.  Install Windows Server 2012 R2 on all servers.

2.  Join all servers to domain.

3.  Ensure SCORCH server has a minimum of 1GB of RAM.

4.  On the SCORCH server, .Net 3.5SP1 is required. Setup will not be able to add this feature on Windows Server 2012.  Open an elevated PowerShell session (run as an Administrator) and execute the following:

Add-WindowsFeature NET-Framework-Core

***Note – .NET 3.5 source files are removed from the WS2012 R2 operating system.  You might require supplying a source path to the installation media for Windows Server 2012 R2, such as:   Add-WindowsFeature NET-Framework-Core –source D:\sources\sxs

5.  On the SCORCH server, IIS (IIS Role) is required. Setup will add this role if not installed. 

6.  On the SCORCH .Net 4.0 is required. This is included in the WS2012 R2 OS (.NET 4.5)

7.  Install all available Windows Updates as a best practice.

8.  Add the “DOMAIN\scorchsvc” domain account explicitly to the Local Administrators group on the SCORCH server.

9.  Add the “DOMAIN\ScorchUsers” global group explicitly to the Local Administrators group on the SCORCH server.

10.  On the SQL database server, install SQL 2012 R2.

• Setup is fairly straightforward. This document will not go into details and best practices for SQL configuration. Consult your DBA team to ensure your SQL deployment is configured for best practices according to your corporate standards.
• Run setup, choose Installation > New Installation…
• When prompted for feature selection, install ALL of the following:
• Database Engine Services

• If you are going to be deploying a shared SQL server for other System Center components, you might consider adding:

• Full-Text and Semantic Extractions for Search
• Reporting Services – Native
• Optionally – consider adding the following to ease administration:
• Management Tools – Basic and Complete (for running queries and configuring SQL services)
• On the Instance configuration, choose a default instance, or a named instance. Default instances are fine for testing and labs. Production clustered instances of SQL will generally be a named instance. For the purposes of the POC, choose default instance to keep things simple.
• On the Server configuration screen, set SQL Server Agent to Automatic.  You can accept the defaults for the service accounts, but I recommend using a Domain account for the service account.  Input the DOMAIN\sqlsvc account and password for Agent, Engine, and Reporting.
• On the Collation Tab – you can use the default which is SQL_Latin1_General_CP1_CI_AS or choose another supported collation.
• On the Account provisioning tab – add your personal domain user account or a group you already have set up for SQL admins. Alternatively, you can use the ScorchUsers global group here. This will grant more rights than is required to all ScorchUsers accounts, but is fine for testing purposes of the POC.
• On the Data Directories tab – set your drive letters correctly for your SQL databases, logs, TempDB, and backup.
• On the Reporting Services Configuration – choose to Install and Configure. This will install and configure SRS to be active on this server, and use the default DBengine present to house the reporting server databases. This is the simplest configuration. If you install Reporting Services on a stand-alone (no DBEngine) server, you will need to configure this manually.
• Setup will complete.
• You will need to disable Windows Firewall on the SQL server, or make the necessary modifications to the firewall to allow all SQL traffic.  See http://msdn.microsoft.com/en-us/library/ms175043.aspx    

Step by step deployment guide:

1.  Install SCORCH 2012 R2:

• Log on using your domain user account that is a member of the ScorchUsers group.
• Run SetupOrchestrator.exe
• Click Install
• Supply a name, org, and license key (if you have one) and click Next.  If you don't input a license key it will install Eval version.
• Accept the license agreement and click Next.
• Check all boxes on the getting started screen, for:
  • Management Server
  • Runbook Server
  • Orchestration Console and Web Service
  • Runbook Designer
• On the Prerequisites screen, check the boxes to activate/remediate any necessary prerequisites (such as IIS and .NET), and click Next when all prerequisites are installed.
• Input the service account “scorchsvc” and input the password, domain, and click Test. Ensure this is a success and click Next.
• Configure the database server. Type in the local computer name if you installed SQL on this SCORCH Server, or provide a remote SQL server (and instance if using a named instance) to which you have the “System Administrator” (SA) rights to in order to create the SCORCH database and assign permissions to it. Test the database connection and click Next.
• Specify a new database, Orchestrator. Click Next.
• Browse AD and select your domain global group for ScorchUsers. Click Next.
• Accept defaults for the SCORCH Web service ports of 81 and 82, Click Next.
• Accept default location for install and Click Next.
• Select the appropriate options for Microsoft Update, Customer Experience and Error reporting. Click Next.
• Click Install.
• Setup will install all roles, create the Orchestrator database, and complete very quickly.

Post install procedures:

1.  Open the Deployment Manager, Orchestration Console, and Runbook designer. Ensure all consoles open successfully.

2.  Lets register and then deploy Integration Packs that enable Orchestrator to connect to so many outside systems.

Download the toolkit, add-ons, and IP’s for SCORCH 2012 R2.  http://www.microsoft.com/en-us/download/details.aspx?id=39622

• Make a directory on the local SCORCH server such as “C:\IntegrationPacks”
• Copy to this directory, the downloaded IP’s, such as the following:
  • SC2012R2_Integration_Pack_for_Azure.oip
  • SC2012R2_Integration_Pack_for_Configuration_Manager.oip
  • SC2012R2_Integration_Pack_for_Data_Protection_Manager.oip
  • SC2012R2_Integration_Pack_for_Operations_Manager.oip
  • SC2012R2_Integration_Pack_for_REST.oip
  • SC2012R2_Integration_Pack_for_Service_Manager.oip
  • SC2012R2_Integration_Pack_for_Virtual_Machine_Manager.oip
  • System_Center_2012_R2_Integration_Pack_for_ActiveDirectory.oip
  • System_Center_2012_R2_Integration_Pack_for_ExchangeAdmin.oip
  • System_Center_2012_R2_Integration_Pack_for_ExchangeUser.oip
  • System_Center_2012_R2_Integration_Pack_for_FTP.oip
  • System_Center_R2_Integration_Pack_for_SharePoint.oip                  
• Open the Deployment Manager console
• Expand “Orchestrator Management Server”
• Right click “Integration Packs” and choose “Register IP with the Orchestrator Management Server”
• Click Next, then “Add”.  Browse to “C:\Integration Packs” and select all of the OIP files you copied here.  You have to select one at a time and go back and click “Add” again to get them all.
• Click Next, then Finish.  You have to accept the License Agreement for each IP. 
• Now when you select “Integration Packs” you can see these IP’s in the list.
• Right Click “Integration Packs” again, this time choose “Deploy IP to Runbook server or Runbook Designer”.
• Click Next, select all the available IP’s and click Next.
• Type in the name of your Runbook server role name, and click Add.
• On the scheduling screen – accept the default (which will deploy immediately) and click Next.
• Click Finish.  Note the logging of each step in the Log entries section of the console.
• Verify deployment by expanding “Runbook Servers” in the console.  Verify that each IP was deployed.
• Open the Runbook Designer console.
• Note that you now have these new IP’s available in the designer for your workflows.

Additionally – you can download more IP’s at:

http://technet.microsoft.com/en-us/library/hh295851.aspx

Such as the VMware VSphere IP, or the IBM Netcool IP.

Additionally – check out Charles Joy’s blog on popular codeplex IP’s which have been updated for Orchestrator:

http://blogs.technet.com/b/charlesjoy/

Update 11/29/2012:  This article is current as of the 6.3.173.1 version of the SQL MP.

 

Using RunAs accounts and profiles is an often poorly understood area of OpsMgr.  As I began to investigate setting this up for the SQL MP, I quickly realized how little I understood it.  Chatting with my PFE peers, I found that while everyone felt they had a “big picture” idea of how to configure it, nobody I talked to really understood all the options and impact.

This post will be a primer on the basics of using RunAs accounts and profiles, and an in depth example of applying them to the SQL Management pack under different typical scenarios.

 

Why do we have RunAs accounts?

 

We use RunAs accounts when we need a Management Pack workflow to run under the credentials of a different account than the default agent action account. 

Generally, the situation is that the default agent action account does not have enough rights and privileges to perform the action that must be taken in the management pack.  This is typical under two scenarios:

• The application being monitored has its own security model and access list, and does not necessarily share the rights of the Operating System (e.g. SQL Server)
• The default agent action account is running under a low-priv domain user account, and does not have enough rights for typical management pack operations (rare)

MOST customers I work with use Local System as the default agent action account.  This is also what I always recommend, unless a company has a VERY specific and well thought out reason they can’t do this.  Local System is a low maintenance account on each agent that generally has local administrative rights to that operating system only.  Another alternative is to use a domain user account as the default agent action account, and place this account in the Local Administrators group on the operating system.

In SOME cases, the application being monitored does not allow Local System (or a local admin/domain user account) to have any, or enough rights to the application.  When this happens, we need to use a RunAs account and profile.

 

The SQL Example

 

When SQL 2005 is installed by default, it places some default security to the SQL instance.  BUILTIN\Administrators (Local Administrators Group from the OS) and NT AUTHORITY\SYSTEM (Local System) are automatically added to the SQL Security access list, and provided with SA (SysAdmin) server role access to the instance.

When SQL 2008 is installed by default, it no longer places BUILTIN\Administrators in the SQL security access list.  The install of SQL 2008 now prompts the installer to give SQL a user account, or Group, to grant SA (SysAdmin) rights to.  If installing on a standalone instance, NT AUTHORITY\SYSTEM (Local System) is still granted SA rights.  If installing on a clustered instance, NT AUTHORITY\SYSTEM (Local System) is granted public rights, but not SA.

When SQL 2012 is installed by default – we no longer place BUILTIN\Administrators in the security access list, AND we restrict NT AUTHORITY\SYSTEM to “public” access.

Most savvy SQL customers would always remove the BUILTIN\Administrators login to SQL to harden the SQL security access.   Some will additionally restrict NT AUTHORITY\SYSTEM (Local System) to have public server role only, removing the SA rights.  There are pros and cons to restricting Local System from SA, that isn't the purpose of this blog post.

 

 

 

Following are the most common scenarios I run into, in the field:

 

Scenario #1.  You use Local System as the default agent action account. 

You accept the default SQL permissions, or modify them to ensure that Local System has the “SA” role to the SQL instance.  In this case – the default agent action account has full rights to the Operating System and to SQL.  No other configuration or use of Run-As accounts is necessary.  The SQL MP will discover and monitor the SQL instances.  This is not the most secure scenario, but likely the simplest to manage.

Scenario #2.  You use a Domain User account as the default action account. 

This account is a member of the Local Administrators group on the server OS.  This domain user account has been delegated “SA” rights in SQL explicitly, or via group membership.  In this case – the default agent action account has full rights to the Operating System and to SQL.  No other configuration or use of Run-As accounts is necessary.  The SQL MP will discover and monitor the SQL instances.  (Hint – you might consider just using this special account as the default agent action account ONLY for your SQL servers).  This is more secure than scenario #1 above, but is more difficult to manage in some cases.

Scenario #3.  You use Local System as the default action account.

However, the SQL team has restricted the NT AUTHORITY\SYSTEM (Local System) SQL login, and removed the “SA” right.  In this case, the Local System account has full rights to monitor the server OS, however, does not have enough rights to discover and monitor the SQL application.  In this case – we would use a Run-As account(s) to manage access for the SQL workflows only, to execute under this Run-As account.  This account(s) can be created and fully managed by the SQL team.  This is the MOST common scenario.

Scenario #4.  You use a Domain User account as the default action account.

This account is a member of the Local Administrators group on the OS.  It is used by the OpsMgr team as their agent account.  However, the SQL team has restricted or deleted the BUILTIN\Administrators SQL login, thereby removing the “SA” right from local admins.  The SQL team will not allow this account, which they do not control, to have any access to SQL.  In this case, the default agent action domain user account has full rights to monitor the server OS, however, does not have enough rights to discover and monitor the SQL application.  In this scenario – we would use a Run-As account(s) to manage access for the SQL workflows only, to execute under this Run-As account.  This account(s) can be created and fully managed by the SQL team.

 

So – in summary – the most common scenarios are:

• Use a default agent action account that has Local Admin rights to the OS and SA rights to SQL
• Use a default agent action account that has no rights to SQL and therefore configure RunAs accounts and profiles to gain access to SQL.

 

Moving forward…  in this example, we have decided we want to keep using Local System for the default agent action account, and set up a RunAs account for access to SQL.  At a very high level, this will involve the following:

• Create a Run As account using a Domain User credential.
• Associate this Run As account with one or more Run as profiles.

***Note:  In the next example – we will create only ONE Run As account for handling all SQL workflows.  The SQL MP guide has a very in depth (but complicated) set of instructions for setting up multiple groups and accounts and this is really way more complicated than most customers need to mess with.  This RunAs account will be granted Local Admin over the OS and SA (Sys Admin) rights to SQL.  (lower priv can be established, see SQL MP guide if you really need to go there)

 

Step 1:  Create a Run As Account

 

1.  Open the console and browse to Administration pane > Run As Configuration > Accounts. 

2.  Create a Run As Account of type = Windows

3.  Give it a good display name, such a “SQL MP Monitoring Account”

4.  Type in the credentials for username, password, and domain.

5.  On the next pane – you are asked about a distribution security option.  You need to choose “Less Secure” or “More Secure”.  This REALLY warrants a good discussion.

“Less Secure” just means distribute this credentials to ALL Health Services in the management group.

“More Secure” just means distribute this credential ONLY to Health Services that I EXPLICITLY define.

I really don’t like the terminology we chose of “Less Secure”.  I think they were trying to stress that using “more secure” is a better way to ensure that the tightest security model is upheld.  Theoretically, someone could take an agent management machine they had access to, and hack the credential presented until they got the password.  This model has completely changed from SP1, where were distributed the credential anywhere that needed it, automatically.  This presented a risk, because a server admin who didn’t get access to the credential, could theoretically “fake” that he had an application which needed the credential by placing a dummy registry entry, having this class discovered, get the credential distributed, and start trying to hack the credential.  "The new “more secure” absolutely controls the distribution of the Run As credential, and only OpsMgr admins have access to this.

Less Secure really isn't a valid option.  The reason for this, is that the agent, as soon as it receives a RunAs credential, performs a series of tests to make sure that we can use the Run As credential.  This includes testing for the “Log On Locally”.  If you create a Run As account, and choose “Less Secure” you will immediately get a flood of alerts from all your Domain Controllers, Exchange servers, and any other servers that restrict the Log on Locally right.  In enterprise server environments, this is very typical to remove “Domain Users” or the local “Users” group from this user right via group policy – or to deny “Log on Locally” for service accounts.  This essentially makes “Less Secure” unusable for any practical purpose.

Therefore we WILL be using “More Secure”. 

Now that we have that settled, this means we need to choose the Health Services to distribute the Run As credential to.   Go ahead and finish creating the Run As Account using more secure, then open the properties of the newly created account.  There is a distribution tab:

 

Click “Add” and now we can add the Health Service objects we want to send this account to.  In this example – I am sending this credential to all my SQL servers.  Our only option here is to search by name.  If you have a good naming standard – this is fine.  If you don’t… this will be a bit painful initially.  Luckily, I have the term “SQL” in almost all my SQL server names, so this is easy enough for me – I type in “sql” and his search, and add all my SQL servers, one by one.

Click “OK” and you are done.

Behind the scenes – all of these Health Services are notified of a config update – they download their new config and get the new credential.

 

Step 2:  Associate the Run As Account to a Run As profile.

 

Now the fun begins!  Run As Profiles are objects that are shipped within Management Packs.  You don’t create RunAs Profiles in the UI – they will come delivered with the MP that needs them.  Each workflow or module in an MP can *optionally* attempt to load a Run As Profile (if it is associated with a Run As Account for that system).  If the Run As Profile is not associated with anything (default) then the workflow will run under the Default Agent Action Account credentials, like any other workflow.

Here is an XML example from the SQL MP:

The monitor which inspects SQL Service pack version is below:

<UnitMonitorType ID="Microsoft.SQLServer.2008.ServicePackVersion" Accessibility="Internal" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">

Note the highlighted part in yellow.  This instructs the workflow to try and run this workflow as the “SQLProbeAccount” profile if it is associated for this HealthService.

So… Workflow tries to load a profile > Profile is associated with a Run As account > Run As account contains a credential for execution.

What you will see – if a new MonitoringHost.exe process will spin up, and execute any workflows that need to run under this credential.

Make sense?

 

Ok – let’s associate!

 

The SQL MP includes 3 Run As profiles.  These are:

• SQL Server Discovery Account
  • Used for discoveries and discovery based datasources
  • In the XML – referenced as: “SQLDiscoveryAccount”
• SQL Server Monitoring Account
  • Use for monitoring workflows and monitoring based datasources
  • In the XML – referenced as: “SQLProbeAccount”
• SQL Server Default Action Account
  • Used to elevate Monitoring tasks in the console.
  • Used for when the default agent action account is extremely low priv (rare)
  • In the XML – referenced as: “SQLDefaultAccount”

There are different workflows in the SQL MP’s which will attempt to use these profiles.  What I typically recommend, is to just come up with a single RunAs account for access to SQL, and then use that same account for all profiles.

 

Let’s start with the “SQL Server Discovery Account” Profile.  (Oh how I WISH MP developers would name a PROFILE with the WORD “Profile” instead of “Account”…. but I digress)

1.  Open the Properties of this profile.  On the Run As Accounts page, click “Add”.

2.  Select your “SQL MP Monitoring Account”.

3.  Now we have to choose “All targeted objects” or “A selected class, group, or object”.  This warrants yet another deep dive discussion. 

 

 

Normally – the MP Author should let you pick “All targeted objects” and you can be on your way.  Choosing “All Targeted Objects” means that anywhere the profile is associated with a workflow – load and use the defined account (SQL MP Monitoring Account) on all systems.

It is a best practice for all management packs with a discovered class to use a “seed” discovery.  This discovery targets ALL agents (or servers, or operating systems) and runs a lightweight registry discovery.  Then, from the instances discovered in the “seed” discovery, you can target that new class, with your more in-depth role/application based discoveries.  Then – a secondary best practice – is to NEVER load a RunAs Profile against your seed discovery.  This is covered here:  http://social.technet.microsoft.com/wiki/contents/articles/worst-practice-adding-a-run-as-profile-to-your-seed-discovery.aspx 

In previous SQL MP’s – they lacked a seed discovery, and therefore it was more complicated to configure RunAs.  However, in this version, there is a seed discovery class that does not use RunAs, so we can now choose “All Targeted Objects”  Yay!

If you have a very complex environment, you could create groups of SQL computers and target those groups or classes much more granularly here, if necessary.

 

   

Next – let’s examine the SQL Server Monitoring Account profile.

 

1.  Open the Properties of the SQL Server Monitoring Account profile.  On the Run As Accounts page, click “Add”.

2.  Select your “SQL MP Monitoring Account”.

3.  Now we have to choose “All targeted objects” or “A selected class, group, or object”.

Since ALL workflows that leverage the SQL Server Monitoring Account profile are targeting a class that is a SQL specific discovered class, we can use “All Targeted Objects”.

 

4.  Done!

 

 

Last – let’s examine the SQL Server Default Action Account profile.

 

This is a new Run As profile that showed up with Version 6.1.314.36 of the SQL MP.  A deeper analysis shows that this profile is primarily used for:

• Tasks
• Monitoring workflows that don’t need any special rights for the datasource used (event log, perfmon, scripts that don’t access SQL)
• Write actions

Technically – this profile *shouldn’t* be needed.  If you didn’t associate it, we would use the default agent action account to handle all of these tasks, and this would work fine *except* for the tasks.  If you didn’t need or want to use the Tasks in the MP with elevated rights – then I’d lean toward ignoring this profile and leaving it un-associated.  If you DO need to use, and elevate the tasks provided in the MP to use a special account – then configure this profile:

 

  

 

 

Review:

 

Let’s take a break and review. 

• We need to use a Run As account whenever we need a Management Pack workflow to run under the credentials of a different account than the default agent action account. 
• The Run As account is the credential (username and password)
• The Run As account needs to be distributed to Health Services (More Secure and a Less Secure)
• Discoveries, Rules, Monitors (and their associated data sources) can be configured to use a Run As Profile.
• For a Run As profile to work, it must be associated correctly with a Run As account.
• There is a lot of flexibility in how we can associate the account to the profile.

 

Close – but no cigar?

 

So – the example above will work really well for customers, where all their SQL servers are tightly secured, and where all their SQL servers are managed by the same SQL team, in the same trusted domain, and can use the same SQL Run As Account.  This allows success for a large number of environments.

But – what if the corporate SQL team only “owns” or supports half of the SQL servers in the management group, while the other SQL servers are owned and supported by various application teams?

What if we want to monitor ALL SQL servers in the management group, but need to use different Run As accounts depending on their domain, or support team?  Maybe for some SQL servers, we don’t want to use any Run As accounts at all, and just use the default action account?  Read on:

 

Scenario #5   You use Local System as the default action account.

Contoso.com is a global IT services firm.  Their OpsMgr Management Group has about 500 SQL servers discovered, a mix of SQL 2005 and SQL 2008, with a handful of SQL 2008R2 instances.  Their corporate IT SQL team manages 400 of these SQL instances.  The remaining 100 SQL servers are not supported by the corporate SQL team, they are one-off SQL instances which are specific to applications, and managed by the application owners.

Of the 400 SQL servers supported by the corporate IT SQL team, 20 of these are in a highly secured domain disconnected from the corporate IT domain.

The goal is to develop a Run As strategy which will accommodate all SQL servers to be monitored.

All agents will be deployed as Local System for the default agent action account, as that is the standard for the Enterprise Monitoring Team.

All SQL security settings on the 400 corporate IT SQL team servers has been hardened and standardized.  The SQL security settings for the 100 application owners SQL instance  is largely unknown.  Some could be hardened, but it is assumed most will be left to default settings performed at the installation.

 

To configure the SQL MP and Run As – we will use a very similar plan to what we did above.  However, we will need to be more precise in two areas:

• Run As account distribution
• Use Groups for Run As profile association.

We have three unique scenarios – so we will have three Run As accounts to create:

• Corp IT SQL Run As account
• Corp IT SQL Run As account for the high security domain
• Non-Corp IT SQL team (Application owners) Run As account

We will also need to create 3 groups.  We will use these groups for the Run As profile associations, so different computers will use the correct Run As accounts.  These groups can contain Windows Computer objects, or DBEngine objects.  The Windows Computer object Hosts/Contains all other application classes, that is why you can use this one for your groups.  Alternatively – you can use Database Engine objects if you prefer…. as the DBEngine Hosts/Contains almost all the SQL classes in the MP that leverage a Run As profile.

• Corp IT SQL Computers
• Corp IT SQL Computers High Security
• Non Corp IT SQL Computers

 

Now – the steps will go something like this.

1.  Create the three RunAs accounts just like we did above.  Ensure each account will have Local Administrator and SQL SysAdmin rights over the correct OS and instance.

2.  Distribute the correct Run As accounts to the appropriate Health Services.  For Non-Corp IT SQL instance, we will attempt to use the Default Agent action account to monitor SQL.  If Local System does not have enough rights to SQL, we will then distribute and associate our RunAs account, and tell the application owner to make our special Run As account a local admins and SQL admin if they want their SQL server monitored.

3.   On the Run As Profile Associations – choose “Group” instead of “Class” this time.  Select the group from the object picker.

 

Here is how it will look when complete:

 

Now – we simply can make sure the right computers/engines are members of the appropriate group.  If you want a Non-Corp SQL instance to try and use the default agent action account for SQL monitoring – just don’t distribute the Run As account, and don’t add them to the “Non-Corp IT SQL Computers” group.  Then they will have NO association, no credential, and will try local system.  If this fails to work – you can simply have a standard document for the application owner for how to configure their SQL server for proper monitoring.  This takes the burden off the OpsMgr administrator.

 

The thing to keep in mind here – is the profile association works just like overrides.  More specific wins.  So you could have the group associations just like above, but if you had a special one-off instance that needed its OWN run as account – you can associate that specific DB Engine to a very specific Run As account, and it would take priority over the conflicting group association.

 

 

Conclusion

 

I hope this helps provide more answers than it does questions.  This is just one example of how to use the Run As in OpsMgr 2007 and 2012.  Other strategies could be formed.  Keep in mind – usually the simplest solution is best, so don’t over-complicate things.  Make a strategy that is the easiest to maintain, while providing good security separation.

Additionally – this example above is not the “most secure” configuration…. because we assumed our Run As account would have SA rights to SQL.  That is not technically required.  The SQL MP guide does a good job of documenting the minimum instance and database rights needed to grant a Run As account so it can fully monitor SQL.  That said – the Run As account and profile configuration (the focus of this article) will not be any different if you further restrict SQL rights – Run As will work exactly the same way.

 

  

 

***Thanks to many conversations with Jimmy Harper, Jesse Harris, Russ Slaten, Tim McFadden, and Bonnie Feinberg for assisting me with the data provided.

Download:  http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=10631

On 10/14/2013 The SQL MP was updated.  There is a huge number of updates, fixes, and new features that were released in this version (and the version which shipped in Sept 2013.)  See the download link for all the updates, or the MP guide that ships with this version.

SQL Server Management Pack version 6.4.1.0 includes the following changes:

• Fixed CPU Utilization Monitor
• Fixed SQL Server seed discovery for WoW64 environments
• Alert severity of Average Wait Time Monitor was changed to Warning, added consecutive sampling to reduce noise, threshold was changed to 250
• Alert severity of SQL Re-Compilation monitor was changed to Warning, threshold was changed to 25. The monitor was disabled by default.
• Minor fixes

SQL Server Management Pack version 6.4.0.0 includes the following changes:

• New Dashboard for SQL Server 2012 DB
• New Monitors and Rules – only for SQL 2008 and SQL 2012
  • Collect DB Active Connections count
  • Collect DB Active Requests count
  • Collect DB Active Sessions count
  • Collect DB Active Transactions count
  • Collect DB Engine Thread count
  • Thread Count monitor
  • Transaction Log Free Space (%) monitor
  • Transaction Log Free Space (%) collection
  • Collect DB Engine CPU Utilization (%)
  • CPU Utilization (%) monitor for DB engine
  • Buffer Cache Hit Ratio monitor
  • Collect DB Engine Page Life Expectancy (s)
  • Page Life Expectancy monitor
  • Collect DB Disk Read Latency (ms)
  • Collect DB Disk Write Latency (ms)
  • Disk Read Latency monitor
  • Disk Write Latency monitor
  • Collect DB Transactions per second count
  • Collect DB Engine Average Wait Time (ms)
  • Average Wait Time monitor
  • Collect DB Engine Stolen Server Memory (MB)
  • Stolen Server Memory monitor
  • Collect DB Allocated Free Space (MB)
  • Collect DB Used Space (MB)
  • Collect DB Disk Free Space (MB)
  • SQL Re-Compilation monitor
• SPN monitor improved
• Support for special symbols in DB names.
• Improved AlwaysOn seed discovery
• Run As configuration changes to support Low privilege for SQL Server 2012 Cluster
• Improved performance of AlwaysOn discovery
• Custom User Policy Discovery and Monitoring performance optimization
• Hided AG health object from Diagram view
• Minor changes

The MP download contains the following MP’s:

File	Display name
Microsoft.SQLServer.Library.mp	SQL Server Core Library
Microsoft.SystemCenter.Visualization.Component.Extensions.Library.mpb	The Visualization Component Extensions Library
Microsoft.SQLServer.2005.Discovery.mp	SQL Server 2005 (Discovery)
Microsoft.SQLServer.2005.Monitoring.mp	SQL Server 2005 (Monitoring)
Microsoft.SQLServer.2008.Discovery.mp	SQL Server 2008 (Discovery)
Microsoft.SQLServer.2008.Monitoring.mp	SQL Server 2008 (Monitoring)
Microsoft.SQLServer.2008.Mirroring.Discovery.mp	SQL Server 2008 Mirroring (Discovery)
Microsoft.SQLServer.2008.Mirroring.Monitoring.mp	SQL Server 2008 Mirroring (Monitoring)
Microsoft.SQLServer.2012.Discovery.mp	SQL Server 2012 (Discovery)
Microsoft.SQLServer.2012.Monitoring.mp	SQL Server 2012 (Monitoring)
Microsoft.SQLServer.2012.AlwaysOn.Discovery.mp	SQL Server 2012 AlwaysOn (Discovery)
Microsoft.SQLServer.2012.AlwaysOn.Monitoring.mp	SQL Server 2012 AlwaysOn (Monitoring)
Microsoft.SQLServer.Presentation.mp	SQL Server 2012 Presentation

The Microsoft.SQLServer.Presentation.mp, and the Microsoft.SystemCenter.Visualization.Component.Extensions.Library.mpb are for advanced dashboards and available only in SCOM 2012 SP1 and later.

If I were deploying this MP as an update, or to a new environment, there are some configuration scenarios I’d want to ensure I had covered.

1.  The first step is to import the MP’s that are applicable to your environments (DUH!)

2.  Next up, we need to configure security, in the form of Run As accounts.  I previously did a deep dive on configuring security for SQL on a previous version of this MP, available here  http://blogs.technet.com/b/kevinholman/archive/2010/09/08/configuring-run-as-accounts-and-profiles-in-r2-a-sql-management-pack-example.aspx.  I covered a lot of the concepts of RunAs there which I wont rehash fully here.  But I will cover what I consider the most common scenario.

Why do we have RunAs accounts?

We use RunAs accounts when we need a Management Pack workflow to run under the credentials of a different account than the default agent action account. 

Generally, the situation is that the default agent action account does not have enough rights and privileges to perform the action that must be taken in the management pack.  This is typical under two scenarios:

• The application being monitored has its own security model and access list, and does not necessarily share the rights of the Operating System (e.g. SQL Server)
• The default agent action account is running under a low-priv domain user account, and does not have enough rights for typical management pack operations (rare)

As a best practice, we leverage Local System as the default action account for the SCOM agent.

However, the SQL team has restricted the NT AUTHORITY\SYSTEM (Local System) SQL login, and does not allow  the “SA” (System Administrator) right for this account.  In this case, the Local System account has full rights to monitor the server OS, however, does not have enough rights to discover and monitor the SQL application and databases.  So, we need to configure and use a Run-As account(s) to manage access for the SQL workflows only, to execute under this Run-As account.  This account(s) can be created and fully managed by the SQL team.  This is the MOST common scenario.

         

MOST customers I work with use Local System as the default agent action account.  This is also what I always recommend, unless a company has a VERY specific and well thought out reason they can’t do this.  Local System is a low maintenance account on each agent that generally has local administrative rights to that operating system only.  Another alternative is to use a domain user account as the default agent action account, and place this account in the Local Administrators group on the operating system.  However, there are some management packs that don’t support this method and require local system for the default agent action account, so be warned.

The SQL Example

When SQL 2005 is installed by default, it places some default security to the SQL instance.  BUILTIN\Administrators (Local Administrators Group from the OS) and NT AUTHORITY\SYSTEM (Local System) are automatically added to the SQL Security access list, and provided with SA (SysAdmin) server role access to the instance.

When SQL 2008 is installed by default, it no longer places BUILTIN\Administrators in the SQL security access list.  The install of SQL 2008 now prompts the installer to give SQL a user account, or Group, to grant SA (SysAdmin) rights to.  If installing on a standalone instance, NT AUTHORITY\SYSTEM (Local System) is still granted SA rights.  If installing on a clustered instance, NT AUTHORITY\SYSTEM (Local System) is granted public rights, but not SA.

When SQL 2012 is installed by default – we no longer place BUILTIN\Administrators in the security access list, AND we restrict NT AUTHORITY\SYSTEM to “public” access.

Most savvy SQL customers would always remove the BUILTIN\Administrators login to SQL to harden the SQL security access.   Some will additionally restrict NT AUTHORITY\SYSTEM (Local System) to have public server role only, removing the SA rights.  There are pros and cons to restricting Local System from SA, that isn't the purpose of this blog post.

Moving forward…  in this example, we have decided we want to keep using Local System for the default agent action account, and set up a RunAs account for access to SQL.  At a very high level, this will involve the following:

• Create a Run As account using a Domain User credential.
• Associate this Run As account with one or more Run as profiles.

***Note:  In the next example – we will create only ONE Run As account for handling all SQL workflows.  The SQL MP guide has a very in depth (but complicated) set of instructions for setting up multiple groups and accounts and this is really way more complicated than most customers need to mess with.  This RunAs account will be granted Local Admin over the OS and SA (Sys Admin) rights to SQL.  (lower priv can be established, see SQL MP guide if you really need to go there)

Step 1:  Create a Run As Account

1.  Open the console and browse to Administration pane > Run As Configuration > Accounts. 

2.  Create a Run As Account of type = Windows

3.  Give it a good display name, such a “SQL MP Monitoring Account”

4.  Type in the credentials for username, password, and domain.

5.  On the next pane – you are asked about a distribution security option.  You need to choose “Less Secure” or “More Secure”.  This REALLY warrants a good discussion.

“Less Secure” just means distribute this credentials to ALL Health Services in the management group.

“More Secure” just means distribute this credential ONLY to Health Services that I EXPLICITLY define.

If you want to see a detail explanation of the differences, see my previous post on this HERE.  For this post, let me summarize:

“Less Secure” should likely never be used.  In my opinion, it is not very feasible for any typical deployment of OpsMgr.  Always use “More Secure”

Once you create the RunAs account, you can open it back up to handle distribution.  Go to the distribution tab:

Click “Add” and now we can add the Health Service objects we want to send this account to.  In this example – I am sending this credential to all my SQL servers.  Our only option here is to search by name.  If you have a good naming standard – this is fine.  If you don’t… this will be a bit painful initially.  Luckily, I have the term “SQL” or “DB” in almost all my SQL server names, so this is easy enough for me – I type in “DB” and hit search, and add all my SQL servers, one by one.

Click “OK” and you are done.

Behind the scenes – all of these Health Services are notified of a config update – they download their new config and get the new credential.

Step 2:  Associate the Run As Account to a Run As profile.

Now the fun begins!  Run As Profiles are objects that are shipped within Management Packs.  You don’t create RunAs Profiles in the UI – they will come delivered with the MP that needs them.  Each workflow or module in an MP can *optionally* attempt to load a Run As Profile (if it is associated with a Run As Account for that system).  If the Run As Profile is not associated with anything (default) then the workflow will run under the Default Agent Action Account credentials, like any other workflow.

Here is an XML example from the SQL MP:

The monitor which inspects SQL Service pack version is below:

<UnitMonitorType ID="Microsoft.SQLServer.2008.ServicePackVersion" Accessibility="Internal" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">

Note the highlighted part in yellow.  This instructs the workflow to try and run this workflow as the “SQLProbeAccount” profile if it is associated for this HealthService.

So… Workflow tries to load a profile > Profile is associated with a Run As account > Run As account contains a credential for execution.

What you will see – is a new MonitoringHost.exe process will spin up on the agent monitored server, and execute any workflows that need to run under this credential.

Make sense?

Ok – let’s associate!

The SQL MP includes 3 Run As profiles.  These are:

• SQL Server Discovery Account
  • Used for discoveries and discovery based datasources
  • In the XML – referenced as: “SQLDiscoveryAccount”
• SQL Server Monitoring Account
  • Use for monitoring workflows and monitoring based datasources
  • In the XML – referenced as: “SQLProbeAccount”
• SQL Server Default Action Account
  • Used to elevate Monitoring tasks in the console.
  • Used for when the default agent action account is extremely low priv (rare)
  • In the XML – referenced as: “SQLDefaultAccount”

There are different workflows in the SQL MP’s which will attempt to use these profiles.  What I typically recommend, is to just come up with a single RunAs account for access to SQL, and then use that same account for all profiles.

Let’s start with the “SQL Server Discovery Account” Profile.  (Oh how I WISH MP developers would name a PROFILE with the WORD “Profile” instead of “Account”…. but I digress)

1.  Open the Properties of this profile.  On the Run As Accounts page, click “Add”.

2.  Select your “SQL MP Monitoring Account”.

3.  Now we have to choose “All targeted objects” or “A selected class, group, or object”.  Essentially – you should choose “all targeted objects”.   Choosing “All Targeted Objects” means that anywhere the profile is associated with a workflow – load and use the defined account (SQL MP Monitoring Account) on all systems.

It is a best practice for all management packs with a discovered class to use a “seed” discovery.  This discovery targets ALL agents (or servers, or operating systems) and runs a lightweight registry discovery.  Then, from the instances discovered in the “seed” discovery, you can target that new class, with your more in-depth role/application based discoveries.  Then – a secondary best practice – is to NEVER load a RunAs Profile against your seed discovery.  This is covered here:  http://social.technet.microsoft.com/wiki/contents/articles/worst-practice-adding-a-run-as-profile-to-your-seed-discovery.aspx 

In previous SQL MP’s – they lacked a seed discovery, and therefore it was more complicated to configure RunAs.  However, in this version, there is a seed discovery class that does not use RunAs, so we can now choose “All Targeted Objects”  Yay!

If you have a very complex environment, you could create groups of SQL computers and target those groups or classes much more granularly here, if necessary.

      

Next – let’s examine the SQL Server Monitoring Account profile.

1.  Open the Properties of the SQL Server Monitoring Account profile.  On the Run As Accounts page, click “Add”.

2.  Select your “SQL MP Monitoring Account”.

3.  Now we can choose “All targeted objects”

Last – let’s examine the SQL Server Default Action Account profile.

1.  This is a new Run As profile that showed up with Version 6.1.314.36 of the SQL MP.  A deeper analysis shows that this profile is primarily used for:

• Tasks
• Monitoring workflows that don’t need any special rights for the datasource used (event log, perfmon, scripts that don’t access SQL)
• Write actions

Technically – this profile *shouldn’t* be needed.  If you didn’t associate it, we would use the default agent action account to handle all of these tasks, and this would work fine *except* for the tasks.  If you didn’t need or want to use the Tasks in the MP with elevated rights – then I’d lean toward ignoring this profile and leaving it un-associated.  If you DO need to use, and elevate the tasks provided in the MP to use a special account – then configure this profile.

***Note – the above practice will work for simple implementations.  You might see some errors where there are issues with a Run-As account for specific systems, such as where we detect SQL on the registry, but do not wish to monitor it.  For those systems you should disable the SEED discovery and run a Remove-SCOMDisabledClassInstance.  See an overview of that concept here:  http://om2012.wordpress.com/2013/06/09/how-to-remove-objects-from-monitoring-remove-scomdisabledclassinstance/

The kind of “noise” you will see – until you take care of the RunAs account security, will be alerts such as:

Run As Account does not exist on the target system or does not have enough permissions

Management Group: OMMG1. Script: DiscoverSQL2012FileGroups.js : Cannot login to database [DB01.opsmgr.net][MSSQLSERVER:AppController]

Operations Manager failed to start a process

The process started at 11:59:18 AM failed to create System.PropertyBagData, no errors detected in the output. The process exited with 0
Command executed: "C:\Windows\system32\cscript.exe" /nologo "GetSQL2008DBFilesFreeSpace.vbs" "DB04" "DB04.opsmgr.net" "MSSQLSERVER"
Working Directory: C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 454\13680\
One or more workflows were affected by this.
Workflow name: Microsoft.SQLServer.2008.Monitoring.DBLogFileSpaceMonitor
Instance name: mastlog
Instance ID: {8CF4EB67-40E8-CA73-22AE-455BC4A83547}
Management group: OMMG1

If you have not distributed the RunAs Account to a system, but we discovered SQL on it, you will see:

System Center Management Health Service Credentials Not Found Alert Message

An account specified in the Run As profile "Microsoft.SQLServer.SQLDiscoveryAccount" cannot be resolved.
This condition may have occurred because the account is not configured to be distributed to this computer. To resolve this problem, you need to open the Run As profile specified below, locate the account entry as specified by its SSID, and either choose to distribute the account to this computer if appropriate, or change the setting in the profile so that the target object does not use the specified account.
Note: you may use the command shell to get the Run As account display name by its SSID.
Management Group: OMMG1
Run As Profile: Microsoft.SQLServer.SQLDiscoveryAccount

Review:

Let’s take a break and review. 

• We need to use a Run As account whenever we need a Management Pack workflow to run under the credentials of a different account than the default agent action account. 
• The Run As account is the credential (username and password)
• The Run As account needs to be distributed to Health Services (More Secure and a Less Secure)
• Discoveries, Rules, Monitors (and their associated data sources) can be configured to use a Run As Profile.
• For a Run As profile to work, it must be associated correctly with a Run As account.
• There is a lot of flexibility in how we can associate the account to the profile.

Close – but no cigar?

So – the example above will work really well for customers, where all their SQL servers are tightly secured, and where all their SQL servers are managed by the same SQL team, in the same trusted domain, and can use the same SQL Run As Account.  This allows success for a large number of environments.

But – what if the corporate SQL team only “owns” or supports half of the SQL servers in the management group, while the other SQL servers are owned and supported by various application teams?

What if we want to monitor ALL SQL servers in the management group, but need to use different Run As accounts depending on their domain, or support team?  Maybe for some SQL servers, we don’t want to use any Run As accounts at all, and just use the default action account?  Read on:

Scenario #5   You use Local System as the default action account.

Contoso.com is a global IT services firm.  Their OpsMgr Management Group has about 500 SQL servers discovered, a mix of SQL 2005 and SQL 2008, with a handful of SQL 2008R2 instances.  Their corporate IT SQL team manages 400 of these SQL instances.  The remaining 100 SQL servers are not supported by the corporate SQL team, they are one-off SQL instances which are specific to applications, and managed by the application owners.

Of the 400 SQL servers supported by the corporate IT SQL team, 20 of these are in a highly secured domain disconnected from the corporate IT domain.

The goal is to develop a Run As strategy which will accommodate all SQL servers to be monitored.

All agents will be deployed as Local System for the default agent action account, as that is the standard for the Enterprise Monitoring Team.

All SQL security settings on the 400 corporate IT SQL team servers has been hardened and standardized.  The SQL security settings for the 100 application owners SQL instance  is largely unknown.  Some could be hardened, but it is assumed most will be left to default settings performed at the installation.

To configure the SQL MP and Run As – we will use a very similar plan to what we did above.  However, we will need to be more precise in two areas:

• Run As account distribution
• Use Groups for Run As profile association.

We have three unique scenarios – so we will have three Run As accounts to create:

• Corp IT SQL Run As account
• Corp IT SQL Run As account for the high security domain
• Non-Corp IT SQL team (Application owners) Run As account

We will also need to create 3 groups.  We will use these groups for the Run As profile associations, so different computers will use the correct Run As accounts.  These groups can contain Windows Computer objects.  The Windows Computer object Hosts/Contains all other application classes, that is why you can use this one for your groups. 

• Corp IT SQL Computers
• Corp IT SQL Computers High Security
• Non Corp IT SQL Computers

Now – the steps will go something like this.

1.  Create the three RunAs accounts just like we did above.  Ensure each account will have Local Administrator and SQL SysAdmin rights over the correct OS and instance.

2.  Distribute the correct Run As accounts to the appropriate Health Services.  For Non-Corp IT SQL instance, we will attempt to use the Default Agent action account to monitor SQL.  If Local System does not have enough rights to SQL, we will then distribute and associate our RunAs account, and tell the application owner to make our special Run As account a local admins and SQL admin if they want their SQL server monitored.

3.   On the Run As Profile Associations – choose “Group” instead of “Class” this time.  Select the group from the object picker.

Now – we simply can make sure the right computers/engines are members of the appropriate group.  If you want a Non-Corp SQL instance to try and use the default agent action account for SQL monitoring – just don’t distribute the Run As account, and don’t add them to the “Non-Corp IT SQL Computers” group.  Then they will have NO association, no credential, and will try local system.  If this fails to work – you can simply have a standard document for the application owner for how to configure their SQL server for proper monitoring.  This takes the burden off the OpsMgr administrator.

The thing to keep in mind here – is the profile association works just like overrides.  More specific wins.  So you could have the group associations just like above, but if you had a special one-off instance that needed its OWN run as account – you can associate that specific DB Engine to a very specific Run As account, and it would take priority over the conflicting group association.

What’s Next?     

Ok, that covers the security. 

Once this is done – lets spot check and make sure we are discovering our key components.

The first view should be the Microsoft SQL Server \ Computers view, where we should see all our computers that have SQL installed, and their Windows Computer rollup health:

Next up – expand Microsoft SQL Server \ Server Roles \ Database Engines view – and make sure all SQL instances are discovered:

Next – lets check out the discover databases view: 

Finally – check out the NEW Dashboard view (for SCOM 2012)  It is pretty slick.

Optional Configuration:

SQL Agent Job Discovery.

One of the first things you might notice is that SQL Agent Jobs are not discovered by default, SQL services only monitor if set to AUTO statup, and some rules and monitors are disabled out of the box to limit noise.  You might consider enabling these.  Most customers I know like to monitor SQL agent Jobs individually.  To discover these, I wrote a previous article at http://blogs.technet.com/b/kevinholman/archive/2011/08/05/how-to-monitor-sql-agent-jobs-using-the-sql-management-pack-and-opsmgr.aspx

Also – see the guide on how to configure additional optional configurations.

SQL Instance Discovery

You might also want to exclude specific SQL instances by name, or stop monitoring of SQL express or Windows Internal Database.  See:  http://blogs.technet.com/b/kevinholman/archive/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database.aspx

SQL Database Discovery

Ok, above we controlled which SQL Instances get discovered, but perhaps there are specific database you never wish to monitor by name.  We support excluding specific databases in the Database discovery.  For instance, perhaps you wish to never monitor your “model” database.  You could create a group, dynamically populated of SQL databases where name = Model, and then go disable all the rules and monitors in the MP which target the SQL database class.  OR – we can choose to never discover these DB’s in the first place.  Using an override on the database discovery, we can accomplish this.  There are two discoveries that handle discovering SQL 2008 and 2012 databases:

Override each one, and input the names of the DB’s you wish to undiscover and not monitor.  (comma separated)

  Save this Override to your SQL Overrides MP which you should create for storing all your SQL MP modifications:

Disabled workflows:

There are many disabled rules in the MP by default – once you have configured, discovered, and TUNED your default settings of the management pack, consider reviewing these.  Search for the section “Disabled Rules” in the MP guide under Key Monitoring Scenarios.

Next – check out the Performance views – there are many more counters collected in this newer version of the MP than in previous MP’s:

And the reports included:

 

Cool Resources:

Dynamic auto-distribution of SQL Run-As accounts to HealthServices:   http://stefanroth.net/2014/02/06/sql-server-management-pack-populate-run-as-account-with-agent-objects-in-one-shot-powershell/

 

Here is something that a customer brought to my attention, and is probably impacting you already.

 

They noticed that WMI on some of their Server 2008R2 monitored agents was consuming a large amount of memory – and continually increasing.  I started tracking this in SCOM by writing a rule to collect the Process\Private Bytes of all WMI processes (WmiPrvSE*) to check.

Sure enough – a handful (but not all strangely) of my Windows 2008 R2 monitored servers are exhibiting this behavior.  Below is a graph where see can see most processes are consuming ~20MB or less, but some are steadily increasing – consuming 400MB of RAM or more.

 

 

If it goes long enough – occasionally you might also see this in your event logs:

Log Name:      Application
Source:        Application Error
Date:          3/10/2010 4:24:35 PM
Event ID:      1000
Task Category: (100)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      VS5.opsmgr.net
Description:
Faulting application name: wmiprvse.exe, version: 6.1.7600.16385, time stamp: 0x4a5bc794
Faulting module name: ole32.dll, version: 6.1.7600.16385, time stamp: 0x4a5be01a
Exception code: 0xc0000005
Fault offset: 0x0000000000039389
Faulting process id: 0x180
Faulting application start time: 0x01cabfafa91cc252
Faulting application path: C:\Windows\system32\wbem\wmiprvse.exe
Faulting module path: C:\Windows\system32\ole32.dll
Report Id: b45b5a1d-2c93-11df-ac21-001b213a78be

 

It turns out there is a hotfix for Windows 2008 R2 – which addresses a possible leak when an application queries the Win32_Service class frequently.  A monitoring tool would do this – and therefore OpsMgr can accelerate this leak in the OS.

http://support.microsoft.com/kb/981314

 

This hotfix addresses this issue – I applied it to my servers – and they are no longer leaking memory from the WMI process.

 

 

I am adding this hotfix to my recommended hotfixes link, in the OS section.

 

http://blogs.technet.com/b/kevinholman/archive/2009/01/27/which-hotfixes-should-i-apply.aspx

 

 

These are some signs that this might be impacting you in OpsMgr:

 

You might get some alerts in the console like the following:

Workflow Runtime: Failed to run a process or script

The process started at 1:22:12 AM failed to create System.Discovery.Data. Errors found in output:

C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 5\4235\AlertUpdateConnectorDiscovery.vbs(16, 1) SWbemObjectSet: No more threads can be created in the system.

Command executed: "C:\Windows\system32\cscript.exe" /nologo "AlertUpdateConnectorDiscovery.vbs" {A7504CAE-3EA5-5B1F-CDA4-A4593E4D85FD} {F8AEF188-D663-9719-3FD8-94B2AF6F0726} SQL2V1.opsmgr.net
Working Directory: C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 5\4235\

One or more workflows were affected by this.

Workflow name: AlertUpdateConnector.ConnectorDiscovery
Instance name: SQL2V1.opsmgr.net
Instance ID: {F8AEF188-D663-9719-3FD8-94B2AF6F0726}
Management group: PROD1

Or:

Workflow Runtime: Failed to run a WMI query

Object enumeration failed

Query: 'SELECT DisplayName, Name, StartMode FROM Win32_Service WHERE Name="ClusSvc" and StartMode!="Disabled"'
HRESULT: 0x80041006
Details: Out of memory

One or more workflows were affected by this.

Workflow name: Microsoft.Windows.Cluster.Service.Discovery
Instance name: SQL2CLN1.opsmgr.net
Instance ID: {90476733-8FA9-1718-152C-932FF9AB9BC6}
Management group: PROD1

Or:

Workflow Runtime: Failed to run a WMI query

Object enumeration failed

Query: 'SELECT NumberOfProcessors FROM Win32_ComputerSystem WHERE DomainRole >1'
HRESULT: 0x800705af
Details: The paging file is too small for this operation to complete.

One or more workflows were affected by this.

Workflow name: System.Mom.BackwardCompatibility.Computer.Server.DiscoveryRule
Instance name: SQLDB1.opsmgr.net
Instance ID: {AF7C2749-FF52-E354-EEAE-8CFCA3541607}
Management group: PROD1

The details of the script or discovery or workflow are irrelevant.  What is relevant here is seeing the messages “No more threads can be created in the system” and “Out of memory” and “The paging file is too small for this operation to complete”.

Those are tell-tale signs of a memory leak or memory pressure, and in this case caused by WMI.

 

Sure enough – when I check this system, I can easily see there is an issue:

 

 

If you are running Server 2008R2 on ANY monitored system, it is highly likely that you need to apply this hotfix. 

I recommend it across the board for all Windows 2008 R2 monitored agents, until Windows Server 2008R2 SP1 releases, or something supersedes this.