Do I need to add my SQLserver agent service account as a login

Question

Do I need to add my SQLserver agent service account as a login

sqlfriend

SSC Guru

Points: 52769
More actions
March 19, 2019 at 12:28 pm

#371753
A question about SQL Server agent service account.
I am using SQL server 2017. We have a windows domain account (for example mydomain\mysqlsvc) setup to run SQL server and SQL server agent service.
I have a SQL agent job that needs to access the database on the server.
Do I need specifically add the SQL agent service account mydomain\mysqlsvc as a login and grant it permission to that database OR I don't need to?
According to book online:
The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles:
- The account must be a member of the sysadmin fixed server role.
  And it shows automatically by default under Logins: NT Service\ SQLSERVERAGENT. I do see this login has sysadmin role.
  But that is not my domain account.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

pretty sure neither the SQL account or the SQL agent need any access to the database. they are used to interact with things outside of SQL server instead...for example backup shares(SQL Account)
they certainly should not be sysadmins in SQL , nor local admins on the server. that's just too much permissions. we certainly don't allow that level of permissions in my shop.
if your SQL Agent account is actually being used to execute SSIS packages, then the service account would need access tot eh databases the SSIS packages are accessing, but you wnat minimal permissions there, like db_datareader/db_datawriter(maybe?) and execute

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sue_H SSC Guru Points: 90860 More actions · Answer 2

sqlfriends - Tuesday, March 19, 2019 12:28 PM
A question about SQL Server agent service account.
I am using SQL server 2017. We have a windows domain account (for example mydomain\mysqlsvc) setup to run SQL server and SQL server agent service.
I have a SQL agent job that needs to access the database on the server.
Do I need specifically add the SQL agent service account mydomain\mysqlsvc as a login and grant it permission to that database OR I don't need to?
According to book online:
The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles:
The account must be a member of the sysadmin fixed server role.
And it shows automatically by default under Logins: NT Service\ SQLSERVERAGENT. I do see this login has sysadmin role.
But that is not my domain account.

That's the virtual account and it will be mapped correctly to your domain account as long as you have used SQL Server Configuration Manager (or setup) when you set the service accounts.

Sue

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

Lowell - Tuesday, March 19, 2019 12:35 PM
pretty sure neither the SQL account or the SQL agent need any access to the database. they are used to interact with things outside of SQL server instead...for example backup shares(SQL Account)
they certainly should not be sysadmins in SQL , nor local admins on the server. that's just too much permissions. we certainly don't allow that level of permissions in my shop.
if your SQL Agent account is actually being used to execute SSIS packages, then the service account would need access tot eh databases the SSIS packages are accessing, but you wnat minimal permissions there, like db_datareader/db_datawriter(maybe?) and execute

Thanks, the SQL agent service account is used for SSIS or Powershell that imports data from other server to this server.
Yes, I will certainly not grant the domain account sysadmin or system administrators on windows, but only minimum database level permissions, for example, for import, it needs read/write.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Both of the service accounts need to be in the sysadmins groups in SQL Server. They are normally in the logins as the "per service SID" - NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT. It's explained in the documentation:
The per-service SID of the SQL Server service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.
The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.
Configure Windows Service Accounts and Permissions

Sue