1

I have an SQL server running on machine 'A', running under the usual NT Server\MSSQLSERVER object. I cannot change the account used to run the server.

I need to be able to have that server do a restore from files which are on a network share. Specifically, the files are in a directory on machine 'B', and that directory is then shared out. I need to set permissions on the share so that the SQL server running on machine 'A' has permissions to read from the share.

All the instructions I have seen explain how to add permissions for the "NT Server\MSSQLSERVER" object on the local machine only. That is, when on machine 'B', setting up permissions for the share, I can certainly add the "NT Server\MSSQLSERVER" object when I choose "Locations" to be 'B', but that does not seem to give the "NT Server\MSSQLSERVER" account on machine 'A' permissions to read the files.

Any help or pointers appreciated.

David I. McIntosh
  • 2,038
  • 4
  • 23
  • 45
  • permissions on the share are irrelevant. the account you're using to access the share needs to have rights to the underlying file system as well. that means you need an mssqlserver account of some sort on the B machine. – Marc B Aug 14 '14 at 19:19
  • Machine B is in fact another SQL server, and the directory with the files is where Machine B's SQL server is writing out its backups. Permissions on that dir include full control for: "SQLServerMSSQLUser$redacted$MSSQLSERVER (redacted\SQLServerMSSQLUser$redacted$MSSQLSERVER)" but I can't weem to find the simple object "NT Server\MSSQLSERVER" – David I. McIntosh Aug 14 '14 at 19:40
  • You might want to check out [How to schedule SSIS package to run as somthing other than sql agent service account](http://stackoverflow.com/questions/812428/how-to-schedule-ssis-package-to-run-as-something-other-than-sql-agent-service-ac) – Tak Aug 14 '14 at 19:51
  • @t_m: Not sure how that helps - see second sentence in my question, or am I misunderstanding something? – David I. McIntosh Aug 14 '14 at 20:05
  • I am assuming you are using SQL Agent to run the job. The reference is to use a Domain Account and setting permissions across SQL Server and your Machine A and B. In the reference it goes one additional step of setting up a proxy. – Tak Aug 15 '14 at 12:49

1 Answers1

0

I often accomplish this by using Windows Authentication. You can also do the inverse of what you are trying to and push the backup to machine 'A' from machine 'B' using a Restore-SqlDatabase command from Powershell.

https://www.simple-talk.com/sql/backup-and-recovery/backup-and-restore-sql-server-with-the-sql-server-2012-powershell-cmdlets/

Apothis
  • 406
  • 3
  • 7