I am having a maintenance plan configured with Windows Authentication
being run by an SQL Server Agent job with owner sa
Whom does Windows Authentication of a maintenance plan authenticate, for whom and for what – why is it needed and/or how can it be used?
To which user I should give permissions for accessing backup (being stipulated in maintenance plan) dir location?
And how does the owner of an SQL Server Agent job enter into all this considering that an owner sa has SQL Server authentication?
Best Answer
The Manage Connections dialog is for a connection to the Database or Instance you are performing maintenance on.
So if you type in a sql authenticated user called 'bob' there, the user bob will be the one that connects to the instance to do the maintenance tasks you are specifying.
You can prove that out by running SQL Server Trace/Profiler (in a dev environment) and seeing the connection being made.
That is basically the user that is connecting to the objects within SQL Server.
The connection to the folders and external resources will be, in this case, whatever your SQL Server Service Account is running as.
It's a bit confusing but it sort of works like this:
1.) Your SQL Server Agent Service Account (or the proxy account used) will do all of the executing. It will call out to the maintenance plan, it will handle the job logging for job history/etc.
2.) When the maintenance plan is run - any of the tasks inside of it will either use whatever permissions the agent account has, or whatever you have explicitly defined in the maintenance plan connection manager -as you have done.
3.) For any access to resources outside of SQL Server - the SQL Server Service account will actually be the account interacting. So to write a backup to a folder, your SQL Server Service account will need that permission. The agent tells the server to run the maintenance plan which tells the server to do a backup. The service account of the SQL Server service then performs what it was requested and it writes out the backup.
Also
You didn't ask here - but I'll mention it again like I did in the last answer. I don't get anything for recommending a free resource - but the Ola Hallengren maintenance solution I mentioned on a related question from you is a really good resource. It will intelligently do your index rebuilds/reorganizes so you only touch what needs to be, it will add more control and intelligence to your statistics updates, it does a great job with backups, etc. But the nicest thing going for it? It gets rid of the black box of maintenance plans and takes a variable out of play for you to have to worry about seeing inside of with some of the more advanced security questions you are trying to get answered.