28

I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in the control panel -> user accounts area.

How do I give this service account access to the file system? Specifically, on Windows 7.

I've read several approaches and none seem to be an option for me. I tried a PowerShell approach, but the AD commands were not valid commands. I even downloaded and installed the required windows patch for those commands. I've also read that I should be able to do this via the SQL Server Configuration Manager or the Management Studio. I can't seem to figure out where to adjust these permissions, though.

The SQL Server Agent process fails to start, with the following error message: 

Login failed for user 'NT SERVICE\SQLSERVERAGENT'.  
Reason: Failed to open the explicitly specified database 'msdb'.  
[CLIENT: <local machine>]

Based on my Googling, it has to do with this account's permissions.

I'm not sure if the database is corrupt, but it says the database is in a Normal status. The SQL Server Agent login is part of the sysadmin and public groups.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Ryan
  • 493
  • 2
  • 5
  • 8

4 Answers4

25

To specifically answer your question, here's how you give disk access rights to the built in SQL Server Agent account. But read on as I answer what I believe to be the real issue:

1.> Right-click your drive, select properties, select security, click the Add button and enter the SQLSERVERAGENT account(make sure your domain isn't selected in the From this location text box, but rather your computer name):

enter image description here

2.> Click the Check Names button to validate that the account is valid:

enter image description here

3.> Now, add the file permission you need to the SQLSERVERAGENT account. For trouble-shooting purposes, you may want to give Full control and then scale it back later as needed:

enter image description here

That being said, you probably just need to use SQL Server Configuration Manger to re-add the SQL Agent user--according to the comments I saw about msdb and logins. Configuration Manager makes more changes to SQL Server than using the Windows Services applet--so Configuration Manager should always be used to change ANY SQL Service.

This will fix the issue if someone may have changed the account within Windows Services causing the service to fail on startup. You need to reset it within Configuration Manager. Doing so allows Configuration Manager to add to SQL Server the much needed permissions to manage the MSDB database for the Local Service account (NT SERVICE\SQLSERVERAGENT) whereas changing accounts within the Windows services applet does not.

Caveat: Versions SQL Server Express above 2000 do not include a SQL Agent. Certain aspects of it may appear to be there, but its unusable in the Express version of the product.

To begin, open SQL Server Configuration Manager and double-click the SQL Server Agent service in the SQL Server Services. Select the Built-in account radio button and choose Local Service, and click the Apply button. Important : if you already see that this account is selected chose another account and click the Apply Button. Then, change it back to Local Service and click the Apply button to allow Configuration Manager to add the correct MSDB permissions for the SQL Agent service to start. Now, restart SQL Server Agent to reflect this new setting.

enter image description here

Daniel L. VanDenBosch
  • 340
  • 1
  • 4
  • 16
Sting
  • 2,788
  • 11
  • 20
  • 1
    My SQL Server Agent account was set to NT Service\SQLAgent$MyInstanceName and trying to set security on a folder and pressing Check Names said Name Not Found and wouldn't find it. After much frustration I realised that the default 'From This Location' when pressing Add to add permissions wasn't set to my local machine, it was set to my domain. Changing it to my local machine (the very root node in the tree) allowed it to detect my NT Service\SQLAgent$MyInstanceName account to add it to the folder permissions. – NibblyPig Oct 09 '18 at 14:47
  • I obtained the "MyInstanceName" from the Event Log which logged the errors. – Jasen Dec 19 '18 at 20:57
4

I agree frustrating.. apparently it's as simple as just typing the name. http://zarez.net/?p=3187

Summary: Type NT SERVICE\MSSQLSERVER and then don't click check name just hit OK and you are able to set folder permissions for SQL agent account.

Jack Douglas
  • 39,869
  • 15
  • 101
  • 176
VJason
  • 51
  • 1
  • 1
    Welcome on the DBA SE! I did not vote down. Your answer falls into the "link-only answer" category. These answers become incomprehensible, when the remote side goes down. The reviewers of your post should have said it to you, it was their mistake. So: 1) your answer should actually an answer (to the question on the top) 2) if you are citing anything, refering a web page, copy-paste its most important also into the post. | I gave you an upvote, but I suggest to make convincing for the other reviewers. – peterh Mar 23 '19 at 07:12
  • Excellent advice @peterh, thank you for being so helpful. V Jason, I've added the summary from your comment into your answer — that may be enough but you might like to review to see if you want to add anything else. All improvements are appreciated! – Jack Douglas Mar 23 '19 at 07:45
2

I resolved this problem by setting SQL Server Agent Login account to LocalSystem.

RDFozz
  • 11,631
  • 4
  • 24
  • 38
louis
  • 19
  • 1
0

I solved this giving the rights to the group Users (MYSERVER\Users).

Very probably all users that have a valid login have the right to do the same as the SQLServer.

Damian Vogel
  • 111
  • 2