I'm using SQL Server 2008 R2 to execute a backup command that will create a backup file on a remote share from my production server. The credentials it's using are PRODSERVER$ rather than those of my domain user when using Management Studio. The same happens when using a job even though the job owner is set to my domain user (the job is just a t-sql script). ???? My only guess is that it's using the Local System account as that's what is set as the credential for the SQL Server service (not the agent, the agent is using my domain user). Unfortunately I need this figured out today so any help would be greatly appreciated.
You cannot use Local System for that as such user has no access to other machine.
Setup a SQL job and run it with an account that already has permissions on that server or share.
Usually the account that it's being used for the SQL service it's the one that needs the access. So if you are using a Domain Account, try to set that account on the share so the job can successfully read and write and hence, create the backup file there.
Your assumption is correct. No matter what security context the SQL Job is running under, the actual Database Backup is always performed using the security context that the SQL Server Service is using; in your case Local System. If that account doesn't have access to the remote file resource, you'll need a different approach. One alternative would be to change the SQL Server Service account. Another would be to backup to a local file and then let the SQL Job perform the file transfer to the remote resource.
================================================= Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)