Options
Restoring files-cannot browse share w/out local admin rights
pgrove
Posts: 12
I have tried this every possible way.
I am trying to give the SQL developers permission to restore databases from the snapshot archive (on another Windows Server share) to their development and QA servers, without giving them local administrator rights (developers should never develop software with full admin rights, or their application security won't be tested properly).
I go to the "Restore Wizard", select "Browse for Files...", then "Network Shares". I add a server and can browse the server shares just fine, restore works, everything.
I then remove my account from the local administrators on the server, (with just give sysadmin rights to the SQL instance). (I also have to restart the SQL Backup GUI). Now I can do everything, except browse the remote shares. When I click on the twisty next to the server that I have added, it pauses for a second (like it is trying to browse get the share names) and then nothing (the twisty even disappears).
I tried other local roles like Backup Admin, Power User, etc. but this didn't allow me to do this.
The SQLBackup Agent service account has permissions to the remote share/filesystem. All Local backup/restores work fine. All functions seem to be operating as expected, just not remote server share enumeration.
Please Help!
I am trying to give the SQL developers permission to restore databases from the snapshot archive (on another Windows Server share) to their development and QA servers, without giving them local administrator rights (developers should never develop software with full admin rights, or their application security won't be tested properly).
I go to the "Restore Wizard", select "Browse for Files...", then "Network Shares". I add a server and can browse the server shares just fine, restore works, everything.
I then remove my account from the local administrators on the server, (with just give sysadmin rights to the SQL instance). (I also have to restart the SQL Backup GUI). Now I can do everything, except browse the remote shares. When I click on the twisty next to the server that I have added, it pauses for a second (like it is trying to browse get the share names) and then nothing (the twisty even disappears).
I tried other local roles like Backup Admin, Power User, etc. but this didn't allow me to do this.
The SQLBackup Agent service account has permissions to the remote share/filesystem. All Local backup/restores work fine. All functions seem to be operating as expected, just not remote server share enumeration.
Please Help!
Comments
Thank you for your post into the forum.
If you paste the location of the backup file directly into the SQL Backup GUI rather than using the File Browser, does it allow you to restore it?
Also, does the account logged into Windows have permissions to the network share?
Thanks,
Redgate Foundry
If I paste the full path and filename in the "File" field (for backup and restore) it does work.
This would imply that:
The problem is with server share browsing on remote machines for "non local windows administrators" on the SQL server the console is connected to.
Any ideas on this one, or is it a bug?
It seems like it's a Logged in Windows User permissions problem.
I can create a test machine, create 2 users : BackupUser and BackupAgent, add both users to the Users group on the SQL Server machine only and assign them both sysadmin rights.
I set the SQL Backup Agent Log On account to BackupAgent and log into Windows as BackupUser.
I then create a shared folder on another test machine, assign both BackupAgent and BackupUser read and write permissions.
Using the SQL Backup GUI I can both use the File Browser to browse the network share and backup to it.
If I deny access to BackupUser to the folder I can no longer browse in File Browser but can backup to it if I specify the Servername\Share directly.
Giving access back to BackupUser and taking it away from BackupAgent, I can now search to network share using the File Browser but can no longer successfully back up to it.
What Windows versions and SQL Server versions are you running?
Are you using Windows or SQL Authentication?
Which machine are you taking the Administrator permissions away from, the developer's machine or the network share?
Also if your SQL Server Service running under the account you are logging in with or different and have these permissions been changed?
Thanks,
Redgate Foundry
1. SQLServer1 (Win2k3 w/ SQL2005)
- Redgate SQLBackup Agent 5.3
- Domain service account for agent - "MYDOMAIN\Service-SQLBackup"
- MYDOMAIN\Service-SQLBackup is a Local Server Admin, SysAdmin SQL Role
- MYDOMAIN\User1 - Member of only "SQLServer1\User" local group, SysAdmin SQL instance role
- MYDOMAIN\User2 - Member of SQLServer1\Administrators local group, SysAdmin SQL instance role
2. FileServer1 (Win2k3)
BackupsShare1
- MYDOMAIN\Service-SQLBackup - Full Permissions to Share, Change Permission to FileSystem.
- MYDOMAIN\User1 - Full permissions to share, Read/List/Execute permission to FileSystem.
- MYDOMAIN\User2 - Full permissions to share, Read/List/Execute permission to FileSystem.
3. Workstation1 (Windows XP)
- Red Gate SQL Backup 5 Management Console
- Registered/Connected to SQLServer1's SQL instance
What I know:
1. Agent works fine, backs up full locally every day, logs every hour. Sends copy to \\FileServer1\BackupShare1\SQLServer1\<<Filename>>.SQB
2. User1 and User2 are identical, except User2 is in the Local Admin group on SQLServer1.
3. Both users can get to, and add, server names (i.e. "FileServer1") in the "File Browser" dialog, "Network Shares" option.
4. Both users can see the server name and a "Twisty" (plus sign) next to the server.
5. When User1 clicks the twisty, in flickers for a second, then the twisty disappears.
6. When User2 clicks the twisty, the network shares on FileServer1 are listed. He can then browse down to the files.
7. Both users can manually enter the full path and filename and the files will restore.
8. Workaround: If I add User1 to the Local Administrators group on SQLServer1, he too can work just as well as User2 (he can use the twisty, and browse for the files).
Problem:
This is used primarily by developers to set/reset development and QA testing databases. Often times they are pulling these files from the archive so they have recent production data. I don't want to give them Local Administrator rights to the Windows server.
It gives them:
Remote desktop access Access to other SQL instances that they shouldn't have access Too many permissions, that can "enable" security on developed software to require unauthorized SysAdmin permissions on SQL
Sorry for my delayed response.
I have been recreating your environment in a test network and have been able to recreate the issue you are encountering.
It seems that this problem has been noted before and occurs because of SQL Server's account impersonation.
Unfortunately we don't have a fix for this and the only workaround is as follows :
Create SQL Server Authentication accounts for your developers on SQLServer1 with the sysadmin role.
Create a AllowSQLBrowsing registry entry of DWORD type and set it to 1 in the following key :
32-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
64-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
Set the SQL Backup GUI on each workstation to use SQL Authentication for SQLServer1.
More information on this can be found in the help file under Tips>Specifying File Paths.
I hope this help and apologise we don't have a fix for this issue.
Redgate Foundry