locked
SQL SysAdmin Rights and SCCM RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    Wonder if anyone can help here - I've tried to search for some more information on this but with limited results.

    After the initial setup of SCCM (including setting up roles on other site systems), do I need Sysadmin rights to SQL? My current admin group has rights for this, but I have been told that these will be removed following the installation. Is this likely to affect any of the ConfigMgr services?

    Regards
    DJF
    Friday, February 6, 2009 5:07 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    ConfigMgr communicates with other systems using the site system's computer account by default, including the SQL Server. This is the only account that needs permissions on the SQL Server and should actually be a local admin on the SQL Server. During database creation, this account is assigned the proper privileges in SQL Server. Although you can remove it from being a local admin on the SQL Server and maintain normal functionality, the backup task will fail. Given that the backup task is the only supported method of backing up and restoring configmgr as a whole, this isn't recommended.

    To directly answer your question, your AD accounts do not have to have any privileges in SQL Server or on the database itself. All communications via the console is done via the the SMS Provider which uses the computer account of the SMS provider to communicate with the database.
    Jason | http://myitforum.com/cs2/blogs/jsandys
    Saturday, February 7, 2009 3:04 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Not sure I understand your question but I think if I understand it correctly all you need is to be in the SMS Admins local group on the site server. This may help you:

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

    John Marcum, Systems Management Architect - www.TrueSec.com
    Friday, February 6, 2009 5:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    ConfigMgr communicates with other systems using the site system's computer account by default, including the SQL Server. This is the only account that needs permissions on the SQL Server and should actually be a local admin on the SQL Server. During database creation, this account is assigned the proper privileges in SQL Server. Although you can remove it from being a local admin on the SQL Server and maintain normal functionality, the backup task will fail. Given that the backup task is the only supported method of backing up and restoring configmgr as a whole, this isn't recommended.

    To directly answer your question, your AD accounts do not have to have any privileges in SQL Server or on the database itself. All communications via the console is done via the the SMS Provider which uses the computer account of the SMS provider to communicate with the database.
    Jason | http://myitforum.com/cs2/blogs/jsandys
    Saturday, February 7, 2009 3:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Jason,

    I have a follow on question as I am in a similar situation on an implementation.  Though I understand the context around this discussions and the reasons behind the answers you gave, I was wondering if you had any "Microsoft supported documentation" that would be useful to prove what you are stating?  My customer is requesting that the rights be removed (reduced) after the installation and creation of the SCCM Database.  I'm having a hard time finding the specific rights that are needed other than sysadmin over the db instance and to be a local administrator on each node.  In fact the documentation here: http://technet.microsoft.com/en-us/library/bb680513.aspx states the computer account needs to be in the local administrator group on each node and the installer ID needs to be a sysadmin. 

    I guess what I am looking for is definitive information on the following:

    SCCM Site Server needs to have XYZ rights during the install (local administrators group and sysadmin I suspect)
    User ID used to install SCCM (same I suspect)

    Post Install
    SCCM Site Server, if removed from local administrators group and sysadmin role removed what breaks.  You mentioned SCCM Site Backup Task no longer works (which is definitely not a good thing and I will document that risk but what else is documented by Microsoft)?

    Monday, July 20, 2009 9:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    ConfigMgr Backup task is the one that I know of.  That should be enough to either 1) never mess with the local admin rights of the computer account being in the remote SQL servers' local Administrators group, or 2) put SQL local on your Primary site server and avoid the entire issue.

    fyi, a SQL backup outside of the ConfigMgr backup task is 99.9% useless.  The only useful backup of the ConfigMgr Sql db is the one performed by the ConfigMgr backup task.  You might as well tell your SQL team to not bother trying to manage the backup.  Well, I have heard of 1 person who used an SQL-only backup once to restore a database.  7 days of 18hr a day of being on the phone with Microsoft support, and it was back in service.  So, unless you think that is somehow a good idea, the SQL backup run outside of the configMgr backup is IMO pointless. 

    fyi, you do know that having the SQL database on a separate server isn't best practice for ConfigMgr?  http://technet.microsoft.com/en-ca/library/bb735870.aspx (see Best Practices for SQL server)
    Standardize. Simplify. Automate.
    Monday, July 20, 2009 10:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you Sherry.  Yep I know about the best practice and I understand where you are coming from.  I'm there too.  I am attempting to support the customer in their request to have all DBs on the cluster and at the same time not give away too much in regards to rights.

    Regarding the backup - yes I understand that is a bad thing and will essentially orhpan their ability to restore and will be documenting that as a risk.  I am just trying to pull together the information so my customer is informed.  Thank you for your input.  I appreciate it and so quickly I might add.
    Monday, July 20, 2009 10:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    My issue is resolved.  I was able to do some testing last night and here are my results:

    Rights requirements for Installation of SCCM as it pertains to SQL.

    Installer Account:  The userid used to install SCCM will need to have sysadmin and a local administrator on each SQL Node for the install of SCCM to be able to successfully provision the DB and give the SCCM Computer Accounts the rights it needs to the SQL DB for SCCM.  These rights for the installer ID are only needed for the initial install.

     

    Computer Account:  The computer account for the SCCM Primary Site Server will need to be added as a local administrator to each node of the SQL Cluster.  These rights will be required post install for proper operation of the VSS job that executes to backup the SCCM Primary Site Server.  The computer account is used for this process.  If the computer account is removed from the local administrators on the cluster nodes, the backup process will fail.  This is the only officially supported Microsoft recovery scenario to successfully bring back a corrupted SCCM Primary Site Server.  A SQL DB backup thru normal SQL backup routines will not be sufficient for restoring SCCM functionality.  In addition, the SCCM Site Server will require local administrator access to ensure proper health of the SCCM Management Point role.  If the computer account is removed from the SQL Cluster local administrators group on each node, the SCCM Primary Site Server MP will move to a degraded state.

     

    Recommended approaches

     

    Option 1

    SCCM DB located remotely: This will require the SCCM Primary Site Server computer account to be a local administrator on each cluster node as documented by Microsoft here: http://technet.microsoft.com/en-us/library/bb680513.aspx.  Add the machine account of the primary site server machine to the Local Administrators group of each Windows Server cluster node computer. This is required to allow the site server to install and configure settings later.

    Option 2

    SCCM DB located locally:  This falls in line with Microsoft Best Practice for SCCM since we would not be leveraging a SQL instance that is used by other enterprise SQL applications.  SQL could be installed locally and rights assigned accordingly.  This however presents two issues:

    1. Licensing costs.
    2. Another SQL to maintain within the organization

      Customer went with Option 1.
    Tuesday, July 21, 2009 6:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jim

    Source for this please?

    Friday, May 29, 2020 10:49 AM