1

In a default SQL installation the engine account is NT Service\MSSQLSERVER, and agent account is NT Service\SQLSERVERAGENT

In SQL security I can see them both listed as logins with sysadmin role.

These are also having the necessary security permissions on the various folders automatically.

If I change the engine and agent account via the config manager to domain or local accounts (like mydomain\sqlengine, mydomain\sqlagent), then:

  1. Do I need to create these accounts under SQL security and assign sysadmin role to them?

  2. Are there any other post steps (like any other permissions assignment - like following: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver16#Windows - or is this implicitly applied via the virtual account?)?

  3. Can I delete the above 2 NT Service accounts from my SQL server logins?

The reason for asking is that - I know that behind the scenes SQL engine makes use of the service sid NT SERVICE\MSSQLSERVER account, and agent makes use of NT SERVICE\SQLSERVERAGENT, for the various folder permissions (data/log/etcetra), and windows privileges and rights like Log in as a service, Replace a process level token, etcetra. But does it still use this virtual account to access the folders/files even after changing the SQL server engine service account to a domain user account?

When I changed the SQL server service account via the configuration manager to a domain user account, it made no automatic changes to the data or log folder or file permissions. The folder/file security popup had always been showing MSSQLSERVER (not the domain user account) and the SQL server works perfectly fine.

Does this mean SQL server engine uses NT SERVICE\MSSQLSERVER even though the SQL engine service account is changed to a domain user account?

Improve this question
2
  • I think Configuration manager takes care of a lot of stuff but you might need to fix the SPN registration. DBATools has these commands you can use Test-DbaSpn Set-DbaSpn
    – David Wiseman
    Commented Feb 16 at 11:19
  • Yes that bit is being done by Windows team for us, my question was purely from SQL server operations perspective.
    – variable
    Commented Feb 16 at 12:48

1 Answer 1

Reset to default
1
+50

These accounts (NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT) are known as Virtual Accounts and hold the permissions as you have observed when you use configuration manager to change the account things keep on working. You can read the official documentation at the following link, it's rather long so cannot reproduce the entire contents here, plus the link seems to be updated regularly.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver16#VA_Desc

Improve this answer
6
  • So does this mean I don't have to assign any folder permission or windows priviledges to the domain user that I update into the SQL engine service account?
    – variable
    Commented Feb 16 at 13:50
  • not if they are the standard SQL folders, if you've added your own location for a data file or backup, or you are writing to disk elsewhere then those would need to be added
    – Stephen Morris - Mo64
    Commented Feb 16 at 16:47
  • I do have separate data and log folders spread on various drives and it seems to all work fine without assigning any folder permission to the new SQL engine domain service account.
    – variable
    Commented Feb 16 at 18:27
  • When you say those would need to be added, do you mean to add the NT SERVICE\MSSQLSERVER or the domain service account, or is is either fine?
    – variable
    Commented Feb 19 at 11:12
  • Can I have some response please when you get a moment
    – variable
    Commented Feb 23 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.