Account running SQL server agent service

  • I would like to ask expert on this forum a question that has been a puzzle for me for a long time.

    Although I did a lot of research and post messages online, but still cannot find clear answer.

    When installing SQL server using the setup wizard, at the step that ask for what services account you want to use, I set up using a domain account for example mydomain\mysvcuser for both SQl server and SQL server agent services.

    So after installation, I can see in SQL server configuration manager, the services are using the domain account.

    My question is : is this account a sysadmin automatically because it is the account that runs SQL service agent? I cannot see it is in anywhere in SSMS like logins, users?

    Thanks

  • sqlfriends (3/8/2012)


    I would like to ask expert on this forum a question that has been a puzzle for me for a long time.

    Although I did a lot of research and post messages online, but still cannot find clear answer.

    When installing SQL server using the setup wizard, at the step that ask for what services account you want to use, I set up using a domain account for example mydomain\mysvcuser for both SQl server and SQL server agent services.

    So after installation, I can see in SQL server configuration manager, the services are using the domain account.

    My question is : is this account a sysadmin automatically because it is the account that runs SQL service agent? I cannot see it is in anywhere in SSMS like logins, users?

    Thanks

    is not automatically sysadmin. The account is added to the proper SQL Server group (similar to a domain group) which gives the user the proper permissions. It does this so that you do not have to add a user to SQL Server in order to make it a service account, and also so that if you change the service login it will automatically remove those permissions from the old user and give them to the new one.

    Domain account assigned to MSSQLSERVER service is placed into group NT SERVICE\MSSQLSERVER

    Domain account assigned to SQL Server Agent service is placed into group NT SERVICE\SQLSERVERAGENT

    This is why you MUST only use SQL Server configuration manager to make changes to service account logins. It will automatically give the proper permissions to the assigned login.

    Jared
    CE - Microsoft

  • <quote>

    Domain account assigned to MSSQLSERVER service is placed into group NT SERVICE\MSSQLSERVER

    Domain account assigned to SQL Server Agent service is placed into group NT SERVICE\SQLSERVERAGENT

    This is why you MUST only use SQL Server configuration manager to make changes to service account logins. It will automatically give the proper permissions to the assigned login.[/quote]

    First I setup the service account when doing the intial installation using the set up wizard when asking service accounts, so I don't need to change using Configuraiton manager later.

    Secondly, how can I know the account is added to the NT SERVICE\SQLSERVERAGENT

    Is it something all done at the background?

    In SSMS, I see it is a group, but I don't know my domain account is a member of that group or not.

    You may mention SQLServerSQLAgentUser$computername $MSSQLSERVER in windows group, but all that group has is a sid number there, I cannot see my domain account is included.

    This is where I got confused, no one seems no good answer on this one.

  • Domain account assigned to MSSQLSERVER service is placed into group NT SERVICE\MSSQLSERVER

    Domain account assigned to SQL Server Agent service is placed into group NT SERVICE\SQLSERVERAGENT

    This is why you MUST only use SQL Server configuration manager to make changes to service account logins. It will automatically give the proper permissions to the assigned login.

    First I setup the service account when doing the intial installation using the set up wizard when asking service accounts, so I don't need to change using Configuraiton manager later.

    Secondly, how can I know the account is added to the NT SERVICE\SQLSERVERAGENT

    Is it something all done at the background?

    In SSMS, I see it is a group, but I don't know my domain account is a member of that group or not.

    You may mention SQLServerSQLAgentUser$computername $MSSQLSERVER in windows group, but all that group has is a sid number there, I cannot see my domain account is included.

    This is where I got confused, no one seems no good answer on this one.

    Separate, it is not a windows group. It is actually what they call a Virtual Account. As far as I know, you cannot see which domain users are assigned to that virtual account. It is all done through SQL Server Configuration Manager after install, or by the installer during install.

    http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.110%29.aspx

    Jared
    CE - Microsoft

  • sqlfriends (3/8/2012)


    I would like to ask expert on this forum a question that has been a puzzle for me for a long time.

    Although I did a lot of research and post messages online, but still cannot find clear answer.

    When installing SQL server using the setup wizard, at the step that ask for what services account you want to use, I set up using a domain account for example mydomain\mysvcuser for both SQl server and SQL server agent services.

    So after installation, I can see in SQL server configuration manager, the services are using the domain account.

    My question is : is this account a sysadmin automatically because it is the account that runs SQL service agent? I cannot see it is in anywhere in SSMS like logins, users?

    Thanks

    are you installing onto Windows 2003 or Windows 2008 server?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • windows 2008

  • sqlfriends (3/8/2012)


    windows 2008

    did you use group based or SID based security?

    The agent and sql service accounts are default sysadmins. Using SID based security secures this and stops people from dropping their windows account into one of the local groups that would have been created to gain sysadmin privileges

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Separate, it is not a windows group. It is actually what they call a Virtual Account. As far as I know, you cannot see which domain users are assigned to that virtual account. It is all done through SQL Server Configuration Manager after install, or by the installer during install.

    http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.110%29.aspx

    So my domain account is a sysadmin because it is setup as the account running the sql agent service?

  • sqlfriends (3/8/2012)


    Separate, it is not a windows group. It is actually what they call a Virtual Account. As far as I know, you cannot see which domain users are assigned to that virtual account. It is all done through SQL Server Configuration Manager after install, or by the installer during install.

    http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.110%29.aspx

    So my domain account is a sysadmin because it is setup as the account running the sql agent service?

    you have used your own windows account to run the sql server services is that correct?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • No, it is not my windows account , it is a domain account created in AD.

    For each of our SQL server, we asked our network engineer created a domain account in active directory for us with minimum privilidges .

    And then we use it when install SQL server, and at the step asking service account, we enter this domain account in.

  • Sorry, I wonder why it is so hard to explain.

    When you install SQL server using the setup wizard, and at the step to choose account for SQl server and sql server agent services, there are some default there like local system account, network service account , I don't use them and right below it, it says something like using other accounts to run the service, I click that one, the I entered a domain account there.

    I always read it is a good practice to use a domain account with minimum privilege to run services, instead of using local sytem, networkservice, etc, for domain account is good later for use to interact with other servers etc.

  • sqlfriends (3/8/2012)


    Separate, it is not a windows group. It is actually what they call a Virtual Account. As far as I know, you cannot see which domain users are assigned to that virtual account. It is all done through SQL Server Configuration Manager after install, or by the installer during install.

    http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.110%29.aspx

    So my domain account is a sysadmin because it is setup as the account running the sql agent service?

    No, it is a member of the NT SERVICE\SQLSERVERAGENT virtual account and is granted minimum permissions needed to be that service account. If YOU set up your domain account as sysadmin, then it is. If your domain account was never added as a login for SQL Server, but only as the service account... It will not show up in the logins. It is simply an account being used to run the service and is granted minimum permissions to do so by being a part of the virtual account. I repeat, it is not sysadmin by being a part of this virtual account, nor is it granted a login to SQL Server.

    Jared
    CE - Microsoft

  • No, it is a member of the NT SERVICE\SQLSERVERAGENT virtual account and is granted minimum permissions needed to be that service account. If YOU set up your domain account as sysadmin, then it is. If your domain account was never added as a login for SQL Server, but only as the service account... It will not show up in the logins. It is simply an account being used to run the service and is granted minimum permissions to do so by being a part of the virtual account. I repeat, it is not sysadmin by being a part of this virtual account, nor is it granted a login to SQL Server.

    Thanks,

    First, I didn't add the domain account to sql logins, and I didn't grant it as sysadmin.

    second, you said it is a member of the NT SERVICE\SQLSERVERAGENT virtual account,

    but I see in SSMS, the group NT SERVICE\SQLSERVERAGENT is a sysadmin by default, how to explain a group is sysadmin, but the member of the group is not a sysadmin?

    Thanks

  • sqlfriends (3/8/2012)


    No, it is a member of the NT SERVICE\SQLSERVERAGENT virtual account and is granted minimum permissions needed to be that service account. If YOU set up your domain account as sysadmin, then it is. If your domain account was never added as a login for SQL Server, but only as the service account... It will not show up in the logins. It is simply an account being used to run the service and is granted minimum permissions to do so by being a part of the virtual account. I repeat, it is not sysadmin by being a part of this virtual account, nor is it granted a login to SQL Server.

    Thanks,

    First, I didn't add the domain account to sql logins, and I didn't grant it as sysadmin.

    second, you said it is a member of the NT SERVICE\SQLSERVERAGENT virtual account,

    but I see in SSMS, the group NT SERVICE\SQLSERVERAGENT is a sysadmin by default, how to explain a group is sysadmin, but the member of the group is not a sysadmin?

    Thanks

    the groups above are mapped to the service SID, the groups are sysadmins inside sql server but you cannot just drop other users into them unlike using the old group based security

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • SQLKnowItAll (3/8/2012)


    sqlfriends (3/8/2012)


    Separate, it is not a windows group. It is actually what they call a Virtual Account. As far as I know, you cannot see which domain users are assigned to that virtual account. It is all done through SQL Server Configuration Manager after install, or by the installer during install.

    http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.110%29.aspx

    So my domain account is a sysadmin because it is setup as the account running the sql agent service?

    No, it is a member of the NT SERVICE\SQLSERVERAGENT virtual account and is granted minimum permissions needed to be that service account. If YOU set up your domain account as sysadmin, then it is. If your domain account was never added as a login for SQL Server, but only as the service account... It will not show up in the logins. It is simply an account being used to run the service and is granted minimum permissions to do so by being a part of the virtual account. I repeat, it is not sysadmin by being a part of this virtual account, nor is it granted a login to SQL Server.

    You have to remember that it is not a "group", it is a virtual account. I think that the best way to explain it is that it is only granted those permissions when acting as the service account. However, the login when not acting as the service account (like just logging into SQL Server if it was also added as an SQL Server Login) does not inherit those permissions. Kind of like impersonation. When running the service the domain account is allowed to impersonate the virtual account. However, when simply performing other operations through connections or SSMS it is not inheriting the rights of the virtual account.

    Jared
    CE - Microsoft

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply