February 13, 2012 at 4:44 pm
I just setup a new SQL server 2008 R2 server on a windows R2.
I noticed in the logins menu, I see there are two accounts called :
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
And they are both Sysadmin by default. why they are sysadmin, and what are they used for?
I setup the service account while installation using a domain account for example I will call it "mydomain\sqlsvc20" it is used for logon of both Sqlserver and agent login account.
Now I would like to run a job using the SQl server agent service account, do I need to specifically add it to the login, meaning to add "mydomain\sqlsvc20" to login?
Or it will automatically use NT SERVICE\SQLSERVERAGENT
which actually I don't want it be, because it has too extensive permissions as a sysadmin.
thanks
February 14, 2012 at 2:49 am
Under which account is SQL Server Agent running? Is it [NT SERVICE\SQLSERVERAGENT] or a domain user?
Please run this to discover the service account name.
SELECT servicename, service_account
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server Agent%'
Anyway, the SQL Server Agent service account MUST be a member of the sysadmin fixed server role. If it is not that way already, I suspect someone messed with service accounts.
Run this to fix:
EXEC sp_addsrvrolemember '<Domain>\<Account>', 'sysadmin';
If you want to run a job with lower permissions, change the job owner and set up proxy accounts.
-- Gianluca Sartori
February 14, 2012 at 9:30 am
Gianluca Sartori (2/14/2012)
Under which account is SQL Server Agent running? Is it [NT SERVICE\SQLSERVERAGENT] or a domain user?Please run this to discover the service account name.
SELECT servicename, service_account
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server Agent%'
We use a domain account to run sql server and sql server agent service.
If so, what is [NT SERVICE\SQLSERVERAGENT] used for us?
I remember I read before we should give minimum privilage to SQL server /agent service account.
This now becomes confusing
Anyway, the SQL Server Agent service account MUST be a member of the sysadmin fixed server role. If it is not that way already, I suspect someone messed with service accounts.
February 15, 2012 at 2:32 am
sqlfriends (2/14/2012)
I remember I read before we should give minimum privilage to SQL server /agent service account.
This now becomes confusing
You may be confusing privileges on the host computer with privileges on the SQL server--the SQL server agent service account must be a sysadmin on the SQL server, but that doesn't mean it has to be an administrator on the host computer.
February 15, 2012 at 2:52 am
I think the easiest way to think of NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT is as groups, much like the admin group in computer management.
Bascially, it means that the account running the services has the right access needed to SQL, so say for instance I installed SQL on all 10 servers using the same domain account svc_SQL1, then I wanted to change this to svc_SQLServer1, svc_SQLServer2 etc, I dont have to go into SSMS and add in the new service accounts individually as the service itself has the right access via this "group"
February 15, 2012 at 3:46 am
The NT SERVICE\SQLSERVERAGENT and NT SERVICE\MSSQLSERVER entries are what are known as Service SIDs. Google can tell you more about what these are.
To me, it looks like your SQL Server instance got installed using Service SIDs, not domain accounts. There is no particular harm in this, but I am not sure what access a Service SID would have to other machines in the domain - possibly they have no access.
If your SQL instance is working as desired and you are using Service SIDs, then there is no need to move away from these. In many ways a Service SID is the most secure account you can have for running SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 15, 2012 at 9:47 am
EdVassie (2/15/2012)
The NT SERVICE\SQLSERVERAGENT and NT SERVICE\MSSQLSERVER entries are what are known as Service SIDs. Google can tell you more about what these are.To me, it looks like your SQL Server instance got installed using Service SIDs, not domain accounts. There is no particular harm in this, but I am not sure what access a Service SID would have to other machines in the domain - possibly they have no access.
If your SQL instance is working as desired and you are using Service SIDs, then there is no need to move away from these. In many ways a Service SID is the most secure account you can have for running SQL Server.
No, actually I install it use a domain account. If I look SQL server and SQL server agent in service panel in adminstrative tools menu, or in SQL server configration manager, I can see they both run using the login of a domain account, mydomain\sqlsvc20.
The NT SERVICE\SQLSERVERAGENT and NT SERVICE\MSSQLSERVER are shown in SQL server -security -login, but I don't know what is used for, and what are their relationship with the domain account I use for service account?
February 15, 2012 at 10:35 am
They are groups. Notice when you look at the icons in SSMS they are not a single person, they are 2. So, a domain account that gets added in SQL Server Configuration Manager gets the permissions of the group. I believe that this is how the Configuration Manager gives the proper permissions when you change the login for the service. Basically, don't touch them 🙂
Jared
CE - Microsoft
February 15, 2012 at 10:55 am
SQLKnowItAll (2/15/2012)
They are groups. Notice when you look at the icons in SSMS they are not a single person, they are 2. So, a domain account that gets added in SQL Server Configuration Manager gets the permissions of the group. I believe that this is how the Configuration Manager gives the proper permissions when you change the login for the service. Basically, don't touch them 🙂
Thanks, I do realize I will never touch them, but still not sure what they are used for.
and what are their relationship with the domain account I used for?
I don't think my domain account has the same permssion granted as the same with the two account.
The two accunts are sysadmins, but my domain account is not.
February 15, 2012 at 10:57 am
sqlfriends (2/15/2012)
SQLKnowItAll (2/15/2012)
They are groups. Notice when you look at the icons in SSMS they are not a single person, they are 2. So, a domain account that gets added in SQL Server Configuration Manager gets the permissions of the group. I believe that this is how the Configuration Manager gives the proper permissions when you change the login for the service. Basically, don't touch them 🙂Thanks, I do realize I will never touch them, but still not sure what they are used for.
and what are their relationship with the domain account I used for?
I don't think my domain account has the same permssion granted as the same with the two account.
The two accunts are sysadmins, but my domain account is not.
So you are telling me that the service account assigned to run the SQL Server Agent Service is not a sysadmin? It MUST be a sysadmin. and it probably is based on the fact that is a member of the NT SERVICE\SQLSERVERAGENT group.
Jared
CE - Microsoft
February 15, 2012 at 11:06 am
Let's try this explanation... When you use SQL Server Configuration Manager to change the logon for a service, it does not "add" that domain user to SQL Server. It adds the domain user to be a part of the local user group that is defined for that service. If you change the logon for the service again, the old domain user will be taken out of that group. Note: You should not be using a domain account that would login to SSMS as a service account. So this allows you to elevate the proper privileges to the domain account without having to specify security settings for it in SSMS.
Does that help? 🙂
Jared
CE - Microsoft
February 15, 2012 at 11:21 am
SQLKnowItAll (2/15/2012)
Let's try this explanation... When you use SQL Server Configuration Manager to change the logon for a service, it does not "add" that domain user to SQL Server. It adds the domain user to be a part of the local user group that is defined for that service. If you change the logon for the service again, the old domain user will be taken out of that group. Note: You should not be using a domain account that would login to SSMS as a service account. So this allows you to elevate the proper privileges to the domain account without having to specify security settings for it in SSMS.Does that help? 🙂
Thanks, but I don't see the domain account is in the windows group.
February 15, 2012 at 11:28 am
sqlfriends (2/15/2012)
SQLKnowItAll (2/15/2012)
Let's try this explanation... When you use SQL Server Configuration Manager to change the logon for a service, it does not "add" that domain user to SQL Server. It adds the domain user to be a part of the local user group that is defined for that service. If you change the logon for the service again, the old domain user will be taken out of that group. Note: You should not be using a domain account that would login to SSMS as a service account. So this allows you to elevate the proper privileges to the domain account without having to specify security settings for it in SSMS.Does that help? 🙂
Thanks, but I don't see the domain account is in the windows group.
Open the SqlServerSqlAgent$... group. Who is the user there?
Jared
CE - Microsoft
February 15, 2012 at 11:34 am
Thanks, I got to windows computer management, I see a group called SQLServerSQLAgentUser$..
What I see is only one member:
NT SERVICE\SQLSERVERAGENT (S-1-5-80....)
There is no my domain account there.
February 15, 2012 at 11:38 am
Try this link, http://social.msdn.microsoft.com/Forums/en-NZ/sqlsecurity/thread/9e6bb2de-8fd0-45de-ab02-d59bbe05f72e it does a better job at explaining it than I did. Sorry if I have confused you more.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply