A simple question,
We have just done a P2V and are re-testing the environment and moved from SQL 2005 to SQL 2008R2. I am taking this chance to redo my back up plans. I have set up a remote folder on the domain and am having trouble accessing it. I have permissions on the folder of SERVICE plus AD administrators, and my SQL instance is running the Local Network service built in account.
This is not working I am trying to follow the question Here but not sure how to use this proxy in a maintenance plan / sql job. I am trying to avoid setting my SQL service accounts to administrator. Can this be done?
Best Answer
You may proceed with something like creating a proxy account and then assigning the same to SQL Agent job:
Assuming you have Maintenance plan created, which will have its corresponding job created out there on Job activity. Create a SQL login which later you need to give the required roles to execute the SQL agent jobs and assign this login as job owner of the Main'ce plan job
Before you create a proxy account you will need to create a credential as explained in How to: Create a Credential
Now for the SQL login created earlier assign the roles out of below as per you're security standards:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Once login has been created with sufficient roles as above you can create proxy account as explained How to: Create a Proxy. In the ‘Principals’ option, select the SQL login which was created in above step.
Now the final step would be to run this proxy account in the job step properties of the maintenance plan. You can select the proxy created from dropdown under the section which says "Run As".
Finally test the proxy by executing the job.