I posted a similar question a while back but I've lost that thread.
After installing SQL Server, if you took the default, the Agent is running as NT Service\SQLServerAgent and the Database service is running as NT Service\MSSQLSERVER
Do you usually change these services to run as a domain account?
For a server that needs to create files on external network shares, it's easier to grant folder permissions to domain accounts, correct? (I've seen some people just grant Everyone Full permissions to a folder but that's not the best option.)
SQL Server service is not generally associated with creating files on shares. Integration services is usually associated with writing files to disk via SSIS
If using SSIS - the recommendation would be to create a credential and a proxy account and run the SSIS package using that proxy account.
If you are asking specifically about using a domain account for the services, then yes - you would want to change the services to run under that domain account. With that said, the recommendation now is to use a gMSA (Group Managed Service Account) instead of normal domain accounts.
The advantage of using a managed service account is that you don't need to know the password. The password is automatically changed for you and managed by the domain. Access is controlled by adding the server(s) to the AD group - which then allows that account to run services on that system.
Share access is then managed the same way as you would for a normal domain account. In most cases, the dialog boxes to select accounts will not find an MSA account - so you have to enter it directly, with the $ at the end.
Thanks, @jeffw8713 We're a very small shop and proxy accounts may be above our abilities. But I have another question. If I go with a domain account... and latwr want to revert back to the default account of NT Service\SQLServerAgent, wouldn't I have to enter the password for it? I have no idea what that password is.
Thanks
see @jeffw8713 answer. it depends how you set it up. out of the box usually it runs NT Service\MSSQLSERVER, but then again it depends how you configured it when you installed the service also.
A proxy account is a normal domain account - it is just setup with a strong password that never expires. It is not setup as a normal user and doesn't need any permissions other than those explicitly defined for access to the database and shares.
To create a proxy - you add a credential in SQL Server - that credential is where you put the domain account (e.g. domain\svc-account) and password.
Once you have a credential you then create a proxy in the agent - using the credential. You then add that proxy account to the Integration Services subsystem.
The agent job step can then be modified to use the proxy account when that step executes instead of the default SQL Server Agent service account.
I recommend you do some reading up on creating proxy accounts - they are not difficult to setup and use and are the recommended method within SSIS.
Finally - changing the account running the service back to NT SERVICE\SQLSERVERAGENT does not require entering a password. This is a special account only available on that system and does not have a password. Using a gMSA account also does not require entering a password because the authentication is handled by the domain.
Instead of just brushing off these ideas as being too complicated - do a bit of research.