SQL Server service account change from virtual account to AD account

Question

Had a question about how local permission are granted(like Log on as a service and Replace a process-level token) when you change the SQL Server service account from Virtual account (NT SERVICE\MSSQLSERVER) to an Windows AD account.

I first installed sql server without specifying an windows account. After installation when I check "Local Security Policy" I could see "NT SERVICE\MSSQLSERVER" added to permissions like "Log on as a service" and "Replace a process-level token".

Then I changed service account to a windows AD account using configuration manager and went back and checked Local Security Policy again and I couldn't find the new windows AD account added in any of them.

So I am wondering how the windows AD account gets those permissions? Is it somehow linked to the virtual accounts?

SQL Server 2014, Windows 2012R2

I have gone through the below questions, but this specific question is not covered.

SQL Server service account Windows privileges and rights

Answer 1

The virtual accounts tied to the service sid (in this case NT SERVICE\MSSQLSERVER) will retain any permissions given to them. This is because they don't go away, they are still tied to the service via the service sid.

When you change the account, for the example you gave, other than a few house keeping items there is nothing else given. It is very well possible to change to a domain account that does not have the proper permissions to even start the SQL Server service. Nothing is transferred.

For example, if you had an account that had full access to read and write to a directory not in the SQL Server standard locations (that are part of the install process) and changed the account, the permissions stay with that account. The new account would need to be granted those permissions in order for SQL Server to properly work with anything in that folder.

The last part of being linked to the virtual account. No, they are not linked. The virtual account and service sid will stay with whatever they had.

Answer 2

You did not specify how you changed the service account. I have been told that you should always use the SQL Server Configuration Manager over Services.msc because the former will correctly apply permissions in the registry and validate the password for correctness, whereas the latter just changes the account. That is why they needed to create something separate from using services.