April 20, 2016 at 2:17 pm
So we are trying to setup our sql maintenance plans to write to a DFS share(UNC Path). The Microsoft way of doing this is give the sql service agent account access to that folder share. We have tested this method and it works no problem. But our network team doesn't like having service accounts having access to file shares. I cannot fault them on this. So we decided to try impersonation and see if we can get this to work. I can see my sql job being invoked as the user that was setup with impersonation. But I am getting access denied errors when the system writes to the share with the impersonation account. The account being used to impersonate does have permission to the share folder. If I add the service account back to the share everything works fine again.
has anyone done this? Does the impersonation actually work? We have sql 2k8 R2.
April 28, 2016 at 9:29 am
Even though you invoke the maintenance plan as another user when it goes to interface with the file system it uses the SQL Server agent account.
To do this I would create a job and use a CmdExec step in place of your file share process where it is running as a proxy user, setup as your impersonated account.
here is how to create a proxy
https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
*edited for clarity
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply