February 21, 2018 at 5:27 am
In production we are running SQL Server 2014 with the "SQL Server Agent" and "SQL Server Database Engine" service accounts changed from the default to an Active Directory account which was made for reasons of pressure in the past to get things working part of the administrators group on the machine SQL Server was running on.
During a recent PCI audit the fact that our service account was in the administrators group was, quite reasonably, flagged as an issue.
I have been set the task of fixing this.
I have read the article https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions and I thought that I would just need to remove the service account from the administrators group and apply all the permissions defined in this article.
In my development environment I decided first to see what went wrong without these permissions. So I removed the service account from administrators on the local machine and restarted the SQL service. To my amazement it came up! In fact I got most of our application running just by giving full control of this service account to part of the registry and adding the "Allowlog on locally" privilege! I checked the service account and as far as I could see it did not have, for example, "Replace a process-level token", "Bypass traverse checking", "Adjust memory quotas for a process" and had no permissions at all to access the Instid\MSSQL\data folder! I checked my development Active directory machine and it did not seem to me to grant these permissions there either.
I decided I must be missing something quite fundamental.
Has anyone any idea what that might be?
I was rather hoping to see more things not working before fixing them by applying the recommended permissions.
February 21, 2018 at 5:31 am
It might be easier to change the service account to another account, then change it back to the original. Make sure you do this in SQL Server Configuration Manager, so that the correct permissions are automatically added.
John
February 21, 2018 at 5:40 am
John Mitchell-245523 - Wednesday, February 21, 2018 5:31 AMIt might be easier to change the service account to another account, then change it back to the original. Make sure you do this in SQL Server Configuration Manager, so that the correct permissions are automatically added.John
Hi John,
Thank you for that.
I was thinking of that to a degree. However SQL Server was installed with this Active Directory service account (in the Administrators group). I was wondering why the necessary permissions were not added then. Was it because the setup realised that the service account had local administrator privileges and so had no need to add these finer grained privileges?
It is worth trying out anyway. If I see all these permissions getting added automatically I will get a warm feeling!
Gerald
February 21, 2018 at 5:47 am
Gerald
Yes, that may be the reason. Or it may be a Windows thing. Maybe Windows doesn't bother adding explicit privileges to the ACL for accounts that are already administrators.
John
February 21, 2018 at 10:40 am
John Mitchell-245523 - Wednesday, February 21, 2018 5:47 AMGeraldYes, that may be the reason. Or it may be a Windows thing. Maybe Windows doesn't bother adding explicit privileges to the ACL for accounts that are already administrators.
John
John
I used SQLServer 2014 Configuration Manager to change the SQL Server and SQL Server agent accounts to a totally new account I had created. The services ran OK but the extra permissions documented in https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions had not been set. I noticed they were set for the built in default accounts NT SERVICE\MSSQLSERVER and NT Service\SQLSERVERAGENT that had never been used though.
Is this just finger trouble on my system or is it the case that these permissions they refer to are not used all the time but just for less common things? I do not like the thought of setting them all manually with no may of testing that I have set them correctly. On the other hand anything I release will be tried out extensively in our test departments before it goes live and so there will be some level of protection.
Gerald
February 21, 2018 at 12:26 pm
Gerald.Barnes - Wednesday, February 21, 2018 10:40 AMJohn
I used SQLServer 2014 Configuration Manager to change the SQL Server and SQL Server agent accounts to a totally new account I had created. The services ran OK but the extra permissions documented in https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions had not been set. I noticed they were set for the built in default accounts NT SERVICE\MSSQLSERVER and NT Service\SQLSERVERAGENT that had never been used though.
Is this just finger trouble on my system or is it the case that these permissions they refer to are not used all the time but just for less common things? I do not like the thought of setting them all manually with no may of testing that I have set them correctly. On the other hand anything I release will be tried out extensively in our test departments before it goes live and so there will be some level of protection.
Gerald
That would be correct to see the permissions set that way. Even if running under a domain account, SQL Server will create those per-service sids. The permissions is a combination of your new account and the per service sid. The two are tied together. In the document for configuring the services accounts and permissions, an important pieces of info is:
For most components SQL Server configures the ACL for the per-service account directly, so changing the service account can be done without having to repeat the resource ACL process.
So permissions are granted to the per service sid rather than the domain account itself for most objects, rights local to the server. It's just a way to grant permissions to the service itself rather than a specific account. This post is a pretty good explanation of it:
SQL Server Service Account and Per-Service SID
Sue
February 22, 2018 at 3:45 am
Sue_H - Wednesday, February 21, 2018 12:26 PMGerald.Barnes - Wednesday, February 21, 2018 10:40 AMJohn
I used SQLServer 2014 Configuration Manager to change the SQL Server and SQL Server agent accounts to a totally new account I had created. The services ran OK but the extra permissions documented in https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions had not been set. I noticed they were set for the built in default accounts NT SERVICE\MSSQLSERVER and NT Service\SQLSERVERAGENT that had never been used though.
Is this just finger trouble on my system or is it the case that these permissions they refer to are not used all the time but just for less common things? I do not like the thought of setting them all manually with no may of testing that I have set them correctly. On the other hand anything I release will be tried out extensively in our test departments before it goes live and so there will be some level of protection.
GeraldThat would be correct to see the permissions set that way. Even if running under a domain account, SQL Server will create those per-service sids. The permissions is a combination of your new account and the per service sid. The two are tied together. In the document for configuring the services accounts and permissions, an important pieces of info is:
For most components SQL Server configures the ACL for the per-service account directly, so changing the service account can be done without having to repeat the resource ACL process.So permissions are granted to the per service sid rather than the domain account itself for most objects, rights local to the server. It's just a way to grant permissions to the service itself rather than a specific account. This post is a pretty good explanation of it:
SQL Server Service Account and Per-Service SIDSue
Sue,
Thank you. Now it is clear why removing the service account from the administrators group resulted in so few issues. I can now go ahead and satisfy the PCI audit without too much further worry. The only issue I have actually found is an application that needed full control of its part of the registry for the service account once it was not in the Administrators group.
Gerald
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply