Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having a maintenance plan configured with Windows Authentication

Windows Authentication in Maintenance Plans

being run by an SQL Server Agent job with owner sa

enter image description here

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
one info to find is SQL Server Service account is goto->services->right click on the service and click properties in the resulting box there is a tab log on in that this account shows the user that access resources outside sql server,in sql server agent its the user of sql server agent service account.this shows that the users are different when connections are made –  Biju jose Nov 18 '13 at 4:03
    
@biju-Jose , I couldn't grasp what is "this" in your ".this shows that the users are different when connections are made" –  Fulproof Nov 18 '13 at 5:58
    
@Fulproof,did you give a try what I said,this account which shows the account which sql server service is running, if you need to access a specific folder or a drive ,it is this account you have to give necessary permissions.I have given a solution how to find the sql server service account –  Biju jose Nov 18 '13 at 6:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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