SQL Backup Restore from Network Issue
chinton
Posts: 2
SQL Backup V5.3.0.178
We recently starting locking down security of the backups more comprehensively and have run into an issue.
Configuration:
Server1
SQL Server 2005 Service Credentials: \\DOODAA\UserOne
SQL Backup Agent Service Credentials: \\DOODAA\UserTwo
BackupServer34
\\Share1
\\ShareB
On BackupServer34, UserOne lives in a security group that is given full control of Share1. UserOne does not have rights in NTFS to see any directories or files on ShareB. UserTwo is in a group that gives full control to both Share1 and ShareB.
Using master..sqbdir \\BackupServer34 the shares can be seen.
Using master..sqbdir \\BackupServer34\Share1 the files can seen.
Using master..sqbdir \\BackupServer34\ShareB no files can be seen.
Using the UI to attempt to restore, I also cannot see the files on ShareB.
Product documentation indicates that the service credentials of the SQLBackupAgent are used to access the network resources, yet I am unable to view this share. What are the expected results supposed to be?
We recently starting locking down security of the backups more comprehensively and have run into an issue.
Configuration:
Server1
SQL Server 2005 Service Credentials: \\DOODAA\UserOne
SQL Backup Agent Service Credentials: \\DOODAA\UserTwo
BackupServer34
\\Share1
\\ShareB
On BackupServer34, UserOne lives in a security group that is given full control of Share1. UserOne does not have rights in NTFS to see any directories or files on ShareB. UserTwo is in a group that gives full control to both Share1 and ShareB.
Using master..sqbdir \\BackupServer34 the shares can be seen.
Using master..sqbdir \\BackupServer34\Share1 the files can seen.
Using master..sqbdir \\BackupServer34\ShareB no files can be seen.
Using the UI to attempt to restore, I also cannot see the files on ShareB.
Product documentation indicates that the service credentials of the SQLBackupAgent are used to access the network resources, yet I am unable to view this share. What are the expected results supposed to be?
Comments
sqbdir runs using the credentials of the logged on user, or at least it tries to. So if you were connected to the SQL Server instance as \\DOODAA\UserOne, you can only see \\Share1. If you were connected to SQL Server instance as \\DOODAA\UserOne, you should be able to see both shares using sqbdir.
The GUI uses sqbdir to display the network shares, hence the results would be identical to the above.
However, if you were backing up the database to a remote share, or restoring from a remote share, using the sqlbackup extended stored procedure, access to the remote share is determined by the rights assigned to the SQL Backup Agent service startup account, as per the documentation. So if you ran something like this via Management Studio or Query Analyzer, regardless of which account* you are using to connect to the SQL Server instance, the restore should succeed:
EXEC master..sqlbackup '-sql "RESTORE DATABASE ... FROM DISK = [\\BackupServer34\ShareB\... ] "'
* The account must have rights to perform the restore.
Thus, the GUI may not always allow you to browse to all the remote shares accessible by the SQL Backup Agent service startup account. Some versions of the SQL Backup GUI will allow you to enter the full name of the remote file manually during the restore, and allow the restore to proceed.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8