Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joerattz
Starting Member
4 Posts |
 Posted - 2011-07-28 : 14:45:33
|
| Before installing SQL Server 2008 R2 on Windows Server 2008, I created individual service accounts for the relevant SQL Server services. During the install, I came to a step that allowed me to specify the logon account for each service. I specified the individual service account that I created for its appropriate service on that step.The installation completed successfully.I was under the impression from documentation I have found that the SQL Server install would create a group for each service with the appropriate permissions set for that particular group. I have also read that the install would set the logon account for each service based on what I specified and add each of those specified service accounts to their respective group.The install did create the groups. The install did use the specified service accounts for each service. I can open the Services dialog and see the relevant SQL Server services and that they are using the service accounts I created and specified. The services also show that they are started.The only thing that seems wrong is that if I look at the service groups the SQL Server install created, I don't see that any of the service accounts I created were actually added to their respective group. Why is that? I can also look at the service accounts themselves and see that they aren't in their respective SQL Server service group.1. Did the install actually add the service accounts to the appropriate service group? I can actually bring up SQL Server Managment Studio and perform a query on the system database tables (I haven't added any user databases yet). This implies to me that the database engine service is working properly. That service is running as one of my service accounts I created but that service account is not (or at least doesn't show that it is) a member of the database engine group.2. If my service accounts have been added to their appropriate service group, why can't I see that when I display the service account's groups or a service group's members?3. Should I still manually add the service account to their appropriate group?I don't know if all the sevices are working yet (and may not any time soon). I only know so far that the database engine appears to be working.Using individual service accounts always seems so intimidating because I am always concerned that I won't ever be able to get the permissions set correctly for each account. I think this is why so many places end up using the easiet built-in account with enough permissions to make SQL Server work. When I read that the install would create a group for each service with the appropriate permissions and add my specified service accounts to their group I thought, "They finally have it figured out. This should be a slam dunk." And, it almost is except for this wrinkle of the service accounts not showing up in their groups (whether they were added or not).Thanks in advance. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-28 : 16:11:32
|
| In each of the local groups that were created do you have something like: NT SERVICE\SQLSERVERAGENT in the SQLServerSQLAgentUser${server}$MSSQLSERVER group? And, do you also have NT SERVICE\MSSQLSERVER in the SQLServerMSSQLUser${server}$MSSQLSERVER group?If so, then everything is setup correctly and working as expected. Check each group and make sure you have the relevant NT SERVER\{servicename} account in each group.Jeff |
 |
|
|
joerattz
Starting Member
4 Posts |
Posted - 2011-07-28 : 16:38:03
|
Thanks for responding.quote:
In each of the local groups that were created do you have something like: NT SERVICE\SQLSERVERAGENT in the SQLServerSQLAgentUser${server}$MSSQLSERVER group? And, do you also have NT SERVICE\MSSQLSERVER in the SQLServerMSSQLUser${server}$MSSQLSERVER group?
Yes to both.quote:
If so, then everything is setup correctly and working as expected. Check each group and make sure you have the relevant NT SERVER\{servicename} account in each group.
OK, I do have the relevant NT SERVICE (as opposed to NT SERVER) service name account in each service group.So what is the point in having created individual service accounts then? I don't see what the point is if the accounts I created (which are the accounts the services are started up using) don't belong to the groups that define the correct permissions? It seems pointless. What am I missing? How will those services work properly if they are running as the users I created but those users aren't in the groups with the correct permissions? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-28 : 21:59:26
|
| Well, what you are seeing is the service SID for those services. This is an added level of security in Windows Server 2008 that is now available.So, what happens is your service account is granted the permissions it needs to run the service. The service SID is added to the appropriate groups and SQL Server with the appropriate permissions they need. This way, the service account only has the privileges required - and nobody can use that service account for anything else, unless you specifically grant that additional access.For example, if you want to grant the service account access to a network share - you can do that and then SQL Server will be able to authenticate to the share with the service account. However, I believe you have to configure the service account as a proxy to grant that additional access - haven't tried it yet myself.In short, you are now running SQL Server with an account that currently has no privileges to login to the server (unless you specifically added it to the local administrators group - which is not recommended). It also has no other privileges on the server - it cannot even access the folders where the databases reside because that is managed by the Service SID.Google Service SID's to get more information - Microsoft has this fairly well documented.Jeff |
|
|
|
joerattz
Starting Member
4 Posts |
Posted - 2011-07-29 : 14:26:03
|
| I have read a lot now about the fact that SIDs get created for each SQL Server service. But, is there any reason to still create different service accounts on Windows Server 2008 since it uses SIDs? It seems like it is no longer needed. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-29 : 15:24:29
|
| I would say yes - because using the built-in accounts grants elevated rights to SQL Server on that system. With a service account, you have at least two advantages:1) Minimum security rights on the server2) Access to network resources when neededThere is also the benefit of limited rights when using xp_cmdshell if enabled. Jeff |
|
|
|
joerattz
Starting Member
4 Posts |
Posted - 2011-08-01 : 11:25:34
|
| Thanks! |
|
|
|
|
|
|
|
|
Service accounts not in service groups?