locked
SQL Server service accounts RRS feed

  • Question

  • I am trying to understand some things about Windows Server 2008 and SQL Server 2008 security.

    The services are granted sysadmin access in SQL Server through service logins.

    NT SERVICE\MSSQL$INSTANCE
    NT SERVICE\SQLAgent$INSTANCE

    Is it possible to grant permissions to these accounts on another server? (Could I grant connect permissions to this account in a database mirroring setup?)

    Does this new security setup, mean that the old best practise of having separate SQL Server service account on different servers, is not so important anymore? (Even if two sql servers are running under the same service accounts, they still don't have access to each other.)

    I can see that the old local sql server groups are still being created. The original purpose of them was that it should be easy to change service account, but what is the purpose of them with this new security setup?

    Ola Hallengren
    Thursday, December 24, 2009 11:17 AM

Answers

  • Is it possible to grant permissions to these accounts on another server? (Could I grant connect permissions to this account in a database mirroring setup?)

    NO , this is just a local service account and that cannot be used for granting connect to a partner in a mirrored instance.

    Does this new security setup, mean that the old best practise of having separate SQL Server service account on different servers, is not so important anymore

    I would still think the best practice is to run SQL under a dedicated service account. Since the OS groups like SQLServerMSSQLUser$ COMPUTERNAME $ INSTANCENAME are installed at the OS level and not granted ssyadmin on SQL 2008 , SQL setup installs these two accounts for starting the services first time when they are started immediately after the installation.

    If you see the security changes in SQL 2008 , the two important points are
    * The Windows groups that are created for use by the SQL Server service, such as SQLServerMSSQLUser$ COMPUTERNAME $ INSTANCENAME and SQLServerSQLAgentUser$ COMPUTERNAME $ INSTANCENAME, are no longer included in the sysadmin fixed server role. Instead, the service account that is used to start the SQL Server service and SQL Server Agent service are individually granted sysadmin rights in SQL Server. When SQL Server is installed on the Windows Server 2008 or Windows Vista operating systems, a service SID is provisioned as a member of the sysadmin fixed server role.
    * By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.


    Thanks, Leks
    Thursday, December 24, 2009 4:16 PM

All replies

  • Is it possible to grant permissions to these accounts on another server? (Could I grant connect permissions to this account in a database mirroring setup?)

    NO , this is just a local service account and that cannot be used for granting connect to a partner in a mirrored instance.

    Does this new security setup, mean that the old best practise of having separate SQL Server service account on different servers, is not so important anymore

    I would still think the best practice is to run SQL under a dedicated service account. Since the OS groups like SQLServerMSSQLUser$ COMPUTERNAME $ INSTANCENAME are installed at the OS level and not granted ssyadmin on SQL 2008 , SQL setup installs these two accounts for starting the services first time when they are started immediately after the installation.

    If you see the security changes in SQL 2008 , the two important points are
    * The Windows groups that are created for use by the SQL Server service, such as SQLServerMSSQLUser$ COMPUTERNAME $ INSTANCENAME and SQLServerSQLAgentUser$ COMPUTERNAME $ INSTANCENAME, are no longer included in the sysadmin fixed server role. Instead, the service account that is used to start the SQL Server service and SQL Server Agent service are individually granted sysadmin rights in SQL Server. When SQL Server is installed on the Windows Server 2008 or Windows Vista operating systems, a service SID is provisioned as a member of the sysadmin fixed server role.
    * By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.


    Thanks, Leks
    Thursday, December 24, 2009 4:16 PM
  • Hi,

    And does it makes any harm to remove/deny access, to such account? or at least remove their sysadmin rights?

    Thanks in advance,

    Regards,

    Sergio

    Friday, August 19, 2011 10:32 AM