Condividi tramite

SQL MP Run As Accounts – NO LONGER REQUIRED - The Thought Process

  • Articolo

If you are currently reading this, chances are you might have seen another post with a similar name that Kevin Holman published on 4/26/16 https://blogs.technet.microsoft.com/kevinholman/2016/04/26/sql-mp-run-as-accounts-no-longer-required/

Kevin did an awesome job of explaining how we can use Service Security Identifiers (SID’s) to provide the SCOM agent service running on a SQL server with access to SQL and enable monitoring without creating having to create Run As accounts, distribute them and associate them with Run As profiles.

I want to take a few minutes to explain why I went looking for a better solution to Run As account management when it comes to SCOM and SQL monitoring, and how I came to the conclusion (with some help) that a solution based on Service SID's could be a better way to manage access from SCOM to SQL.

Due to the requirements of the SQL management pack, many are faced with the issue of creating and managing Run As accounts to provide SCOM with access into the SQL instances it is configured to monitor.  Kevin has wrote multiple posts on this in the past on ways to try to make this process a better experience.  I myself am currently involved in a deployment of SCOM 2012 R2 at my customer, and like many customers, mine has SQL in their environment.  Also, like many customers my customer has many security policies that need to be adhered to, including changing account passwords on a regular basis.  Knowing that I had multiple instances of SQL to monitor, I started to evaluate my options to make things work with as little overhead management as possible.

By default, SCOM agents run under the context of Local System.  This works fine for the Windows management pack, as Local System can provide SCOM with access to all of the items that the Windows MP requires access to.  However, when we get to working with the SQL management pack, the Local System account usually does not have all of the access needed to connect into our SQL instances to provide monitoring.  So why not just edit permissions on our SQL instances to allow the Local System account with the required rights needed in SQL?  The security concern with using NT AUTHORITY\SYSTEM is that all other services running in that security context will also gain access to the SQL instance.  This goes against the idea of providing least required privileges and it could be seen as a security risk.

Next, I looked at the idea of using the traditional method of creating a domain account, providing it with access to SQL and distributing the account via SCOM.  As I mentioned, my customer requires regular password resets on their accounts.  So maybe I'll try and make life easier and just create one domain account so I have less to manage and provide it with access to ALL of my SQL servers and distribute it to ALL of the servers via SCOM?  No, that won't work either, by doing this I'll have created an account that has way too much access, again violating least privileged access.  So am I stuck at this point with creating a new domain account for each SQL instance, and all that goes along with managing that process?  What about when new SQL servers come online and the management involved there?  This seemed like more than I wanted my customer to have to manage.

So I reached out to others at Microsoft to see how I could handle this situation with the least amount of effort in managing everything.  Through some of my questioning about providing the Local System account with the necessary rights, I got a response from Brandon Adams, a Microsoft Premier Field Engineer who suggested that I look into the Service SID solution.  I had not heard about using Service SID's to grant permissions to Windows Services before, but it sounded very intriguing.  Brandon shared with me the following link, https://support.microsoft.com/en-us/kb/2667175 which Kevin also highlighted in his post, that talks about how System Center Advisor requires a Service SID based solution in order to work.  While this article talks about providing the Service SID with sysadmin level access in SQL, it is possible to still follow the low privilege guidance found in the SCOM management pack guide when performing this configuration in SCOM.  Knowing little at the time about Service SID's, Brandon also provided me with the following link, https://blogs.technet.microsoft.com/askperf/2008/02/03/ws2008-windows-service-hardening/ which provides even more details about Service SID's from back when they were being introduced as a part of Windows Server 2008.

Armed with this knowledge, I began testing and was rewarded with a much easier experience than I was used to when I had managed the Run As accounts via service accounts from Active Directory.  Knowing the work Kevin has put into making the Run As experience better over the past many of years, I reached out to him to get his thoughts and feedback.  After some successful tests on his side, Kevin went and took it the next level, creating a custom management pack that helps to work through most of this new process directly from the SCOM console.  The management pack can be found here: https://gallery.technet.microsoft.com/SQL-Server-RunAs-Addendum-0c183c32

Hopefully now that you are armed with this information you will be able to increase your adoption of SQL monitoring in your environment, and this simpler process will make those conversations with database administrators about granting accounts with access all across the SQL environment a little bit easier.

Comments

  • Anonymous
    May 02, 2016
    Hello, what about Managed Service Accounts ? They should be able to provide this automatic password update feature, providing SQL and SCOM are compatible with them (being Microsoft products, I sincerely hope they are).
    • Anonymous
      May 02, 2016
      Hi John. Managed Service Accounts are definitely an interesting technology, but they are not supported in SCOM 2012. Standard domain accounts are required for the install of SCOM to complete successfully, and while it might be possible to try to change over to MSA’s post install, it is not a supported scenario. The solution referenced above is actually even easier to manage than MSA’s, and it is supported.