Since last post I continue explore new SQL MP...
Monitoring under the "Low Privilege" for SQL 2012 cluster it is one of the interesting features in the new SQL MP 6.4.0.0!
It is good to know that now it is possible to do SQL 2012 Cluster monitoring with not SA permissions, which means operations are unable to access to user's confidential information.
Usually Run As and monitoring under the "Low Privilege" configuration is not trivial, so I want to share with you this guide.
Meet with my sandbox:
- Active Directory server
- SCOM 2012 SP1 server on Windows 2012 server
- 2-nodes Windows Server 2012 cluster with SQL 2012 Cluster Instance
- iSCSI software target server - shared storage for my cluster
Ok let’s go!
- AD configuration:
1. In Active Directory, create three domain users that will be commonly used for low-privilege access to all target SQL Server instances:
a. SQLDefaultAction
b. SQLDiscovery
c. SQLMonitor
2. Create a domain group named SQLMPLowPriv and add the following domain users:
a. SQLDiscovery
b. SQLMonitor
Just Open Active Directory Users and Computers and create users and Groups:
3. Grant special permission: Read-only Domain Controllers - “Read Permission” to the SQLMPLowPriv
Open SQLMPlowPriv properties in Active Directory Users and Computers, go to Security tab and click Advanced:
On Advanced settings click add:
Type Read-only Domain Controllers, click ok:
Grant Read permission and save settings:
2. Cluster node configuration
NOTE: Whatever setting you made on first cluster node, the same must be done on a second node of a cluster.
1. On the agent machine, add the SQLDefaultAction and SQLMonitor domain users to the “Performance Monitor Users” local group.
2. If the operating system of the agent machine is Windows Server 2008 or above, add the SQLDefaultAction and SQLMonitor domain users to “EventLogReaders” local group.
3. Add the SQLDefaultAction domain user and SQLMPLowPriv domain group as members to the local Users group.
4. Configure the “Log On Locally” local security policy setting to allow the SQLDefaultAction domain user and SQLMPLowPriv domain group users to log on locally.
5. Grant Read permission on HKLM:\Software\Microsoft\Microsoft SQL Server registry path for SQLDefaultAction and SQLMPLowPriv
6. Grant “Execute Methods”, “Enable Account”, “Remote Enable”, “Read Security” permissions for root, root\cimv2, root\default, root\Microsoft\SqlServer\ComputerManagement11 WMI namespaces to SQLDefaultAction and SQLMPLowPriv
Start WMImgmt.msc console from the command line, open properties
Open Root, click security
Add SQLDefaultAction and SQLMPLowPriv and grant permissions. Do the same for all needed WMI namespaces. Do not forget do the same on second node!
7. Grant Read permission on HKLM:\Software\Microsoft\Microsoft SQL Server\[InstanceID]\MSSQLServer\Parameters registry path for SQLMPLowPriv for each monitored instance
This step is required because it does not inherits parameters from the top level, but MP need access to this registry key
Time for cluster-specific configuration:
1. Grant “Remote Launch” and “Remote Activation” DCOM permissions to the SQLMPLowPriv, SQLDefaultAction using DCOMCNFG
Run from command prompt DCOMCNFG command to open Component Services management console, than open My computer properties
Edit Defaults of Launch and Activation Permissions:
Note: On some configurations Defaults doesn't work, so need also set Limits.
Grant Permissions - do not remove Local Launch, just add Remote Launch and Activation:
2. Allow Windows Remote Management through the Windows Firewall
*In fact I just turned Firewall off, for simplicity, because otherwise I have to spent time to add exceptions for SCOM agent and so on.
3. Grant “Read” and “Full Control” access for the cluster to the SQLMPLowPriv using Failover Cluster Manager
In FC Manager open cluster properties:
On a Cluster Permissions tab grant Read and Full control permissions:
3. SQL Server configuration
1. In SQL Server Management Studio, create a login for “SQLMPLowPriv” on all SQL Server instances to be monitored on the agent machine, and grant the following permissions to each “SQLMPLowPriv” login:
a. VIEW ANY DEFINITION
b. VIEW SERVER STATE
c. VIEW ANY DATABASE
2. Create a SQLMPLowPriv user that maps to the SQLMPLowPriv login in each existing user database, master, msdb, and model. By putting user in the model database, it will automatically create a SQLMPLowPriv user in each future user-created database. See the code sample below. You will need to manually provision the user for attached and restored databases.
3. Add the SQLMPLowPriv user on msdb to the SQLAgentReaderRole database role.
4. Add the SQLMPLowPriv user on msdb to the PolicyAdministratorRole database role.
All steps above are easy with using sample scripts which inside the guide:
Results of second script will provision SQLMPLowPriv to all user databases.
I’ve also perform above steps for SQLDefaultAction
I’ll skip tasks configuration, lets move to Operations Manager configuration.
4. Operations Manager Configuration
1. Import the SQL Server Management Pack if it has not been imported.
I have absolutely clean environment, so I have to import MP first:
2. Create a SQLDefaultAction, SQLDiscovery and SQLMonitor Run As accounts with “Windows” account type.
In Operations Console go to Administrator, open Run As Configuration/Accounts, create new accounts
I recommend to use more secure Run AS accounts distribution, as in this case you explicitly map account to computer.
On absolutely clean environment I have to deploy agents on cluster nodes before next step
Do not forget set Allow Agent act as a proxy setting for both cluster nodes! Cluster will be not discovered in such case
3. On the System Center Operations Manager console, configure the Run As profiles for the SQL Server Management Pack as following:
a. Set the “SQL Server Default Action Account” Run As profile to use the SQLDefaultAction Run As account.
Create Group and include Cluster node Agents in this group - it allows to scope down Run As profiles to SQL Cluster in next steps
O
pen Run As Configuration/Profiles, configure profiles - do not furget select group created on previous step as a target! Useing all target objects is not safe - if appropriate security is not configured on some machine or distribution is not set monitoring will stop work properly on thouse machines
Do not forget to set run as account distribution – add both nodes of cluster
Do the same for SQL Discovery and SQL Monitor
b. Set the “SQL Server Discovery Account” Run As profile to use the SQLDiscovery Run As account.
c. Set the “SQL Server Monitoring Account” Run As profile to use the SQLMonitor Run As account.
Next step is map user account with enough permissions to the Windows Cluster Action Account, I’ve used freshly created Cluster admin account, otherwise Windows Cluster may be not discovered:
Same as for other accounts do not forget to configure account distribution – map both nodes for Cluster admin account.
That’s all, configuration is ready!
Now let's wait while Windows Failover cluster will be discovered, than SQL discovery will be stared.
It may take for a while… Of course there are several ways to push the discovery, but for reasons of clarity I will not touch my environment.
After about 10 minutes virtual nodes of a cluster are discovered:
One virtual node it is WFC, second it is SQL Cluster name.
Finally, after another 10 minutes SQL Server Cluster is discovered and monitored!
Databases are also discovered and monitored!
Let's do simple test - take user DB offline
After some time Alert appeared and DB is in critical State
Cheers!!!
