Date: Tue, 19 Mar 2024 07:59:19 +0000 (GMT)
Message-ID: <1191089759.985.1710835159858@ip-172-31-38-4.eu-west-1.compute.internal>
Subject: Exported From Confluence
MIME-Version: 1.0
Content-Type: multipart/related;
boundary="----=_Part_984_1262190497.1710835159832"
------=_Part_984_1262190497.1710835159832
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Location: file:///C:/exported.html
This page explains the permissions required to use SQL Backup Pr=
o.
Using=
SQL Backup Pro from the graphical user interface
Permissions required=
by the GUI user
The user connecting to the SQL Backup Pro GUI requires:
- Membership of the SQL Server sysadmin fixed server r=
ole, if connecting to the registered SQL Servers using Windows authenticati=
on. For information on how to connect using SQL Server authentication, see&=
nbsp;Adding SQL Server instances by name=
(step 4).
- Execute permissions on the SQL Backup Pro extended stored=
procedure, sqlbackup.
- For the compression=
analyzer, execute permissions on sqbtest=
em>, sqbtestcancel and sqbteststatus =
extended stored procedures.
Permis=
sions required by the SQL Backup Agent service
The SQL Backup Agent service is a Windows service which SQL Backup Pro u=
ses to perform backup and restore operations through the GUI. You spec=
ify the user account used to log on to the SQL Backup Agent service (the st=
artup account) when you install the server components on a SQL Server insta=
nce.
The user account used to log on to the SQL Backup Agent service (the sta=
rtup account) and connect to the SQL Server requires:
- "Log on as a service" rights in order to start the service.
- If using Windows authentication to connect to the SQL Server, membershi=
p of the SQL Server sysadmin fixed server role. If =
using SQL Server authentication, the SQL Server authenticated account must =
be a member of the SQL Server sysadmin fixed server role=
, but the startup account does not need to be. For information on chan=
ging the authentication mode the SQL Backup Agent service startup account u=
ses to connect to the SQL Server instance, see Changing the authentication mode b=
elow.
- Access to any network locations that will be backed up or copied to, or=
restored from.
- Access to the following folders:
- The SQL Backup Pro local data store. By default, this is installed in&n=
bsp;%PROGRAMDATA%\Red Gate\SQL Backup\Data (for Windows Serve=
r 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or %A=
LLUSERSPROFILE%\Application Data\Red Gate\SQL Backup\Data (for Wi=
ndows Server 2003 and Windows XP).
- The SQL Backup Pro logs folder. By default this is %PROGRAMDAT=
A%\Red Gate\SQL Backup\Log (for Windows Server 2008, Windows Serv=
er 2008 R2, Windows Vista and Windows 7) or %ALLUSERSPROFILE%\Appl=
ication Data\Red Gate\SQL Backup\Log (for Windows Server 2003 and=
Windows XP).
- The SQL Backup Pro backup settings registry folder HKEY_LOCAL_=
MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettings\<instance>=
li>
- The SQL Backup Pro backup settings global registry folder HKEY=
_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instan=
ce>
- The Red Gate licensing registry folder HKEY_LOCAL_MACHINE\SOFT=
WARE\Red Gate\Licensing\SQL Backup or HKEY_LOCAL_MACHINE=
\SOFTWARE\WOW6432Node\Red Gate\Licensing\SQL Backup (for 64-bit m=
achines).
- The Red Gate licenses folder %PROGRAMDATA%\Red Gate\Licenses=
em> (for Windows Server 2008, Windows Server 2008 R2, Windows Vista an=
d Windows 7) or %ALLUSERSPROFILE%\Application Data\Red Gate\Licens=
es (for Windows Server 2003 and Windows XP).
- The Microsoft MSSQLServer setup registry folder HKEY_LOCAL_MAC=
HINE\SOFTWARE\Microsoft\MSSQLServer\Setup (read access only).
If you encounter errors related to permissions and access rights, ensure=
that the startup account for the SQL Backup Agent service application has =
been granted the necessary permissions.
Usin=
g SQL Backup Pro from the extended stored procedure
=
Permissions required by the extended stored procedure user
The user running the extended stored procedure requires:
- permission to back up, restore and drop databases=20
- to back up databases, the user must be a member of the db_back=
upoperator fixed database role, or you can use the GRANT BACKUP D=
ATABASE command to grant the permission
- to restore or drop databases, the user must be a member of the db_owner fixed database role or the dbcreator =
;fixed server role, or you can use the GRANT CREATE DATABASE command to gra=
nt the permission
- execute permissions on the SQL Backup Pro extended stored procedure, sqlbackup<=
/li>
- for the compression=
analyzer, execute permissions on sqbtest=
em>, sqbtestcancel and sqbteststatus =
extended stored procedures
Perm=
issions required by the SQL Backup Agent service
The SQL Backup Agent service is a Windows service which SQL Backup Pro u=
ses to perform backup and restore operations through the extended stored pr=
ocedure. You specify the user account used to log on to the SQL Backup=
Agent service (the startup account) when you install the server components=
on a SQL Server instance.
The user account used to log on to the SQL Backup Agent service (the sta=
rtup account) and connect to the SQL Server requires:
- "Log on as a service" rights in order to start the service.
- If using Windows authentication to connect to the SQL Server, membershi=
p of the SQL Server sysadmin fixed server role. If =
using SQL Server authentication, the SQL Server authenticated account must =
be a member of the SQL Server sysadmin fixed server role=
, but the startup account does not need to be. For information on chan=
ging the authentication mode the SQL Backup Agent service startup account u=
ses to connect to the SQL Server instance, see Changing the authentication mode b=
elow.
- Access to any network locations that will be backed up or copied to, or=
restored from.
- Access to the following folders:
- The SQL Backup Pro local data store %PROGRAMDATA%\Red Gat=
e\SQL Backup\Data (for Windows Server 2008, Windows Server 2008 R=
2, Windows Vista and Windows 7) or %ALLUSERSPROFILE%\Application D=
ata\Red Gate\SQL Backup\Data (for Windows Server 2003 and Windows=
XP).
- The SQL Backup Pro logs folder %PROGRAMDATA%\Red Gate\SQL Back=
up\Log (for Windows Server 2008, Windows Server 2008 R2, Windows =
Vista and Windows 7) or %ALLUSERSPROFILE%\Application Data\Red Gat=
e\SQL Backup\Log (for Windows Server 2003 and Windows XP).
- The Red Gate licensing registry folder HKEY_LOCAL_MACHINE\SOFT=
WARE\Red Gate\Licensing\SQL Backup or HKEY_LOCAL_MACHINE=
\SOFTWARE\WOW6432Node\Red Gate\Licensing\SQL Backup (for 64-bit m=
achines).
- The Red Gate licenses folder %PROGRAMDATA%\Red Gate\Licenses=
em> (for Windows Server 2008, Windows Server 2008 R2, Windows Vista an=
d Windows 7) or %ALLUSERSPROFILE%\Application Data\Red Gate\Licens=
es (for Windows Server 2003 and Windows XP).
- The Microsoft MSSQLServer setup registry folder HKEY_LOCAL_MAC=
HINE\SOFTWARE\Microsoft\MSSQLServer\Setup (read access only).
If you encounter errors related to permissions and access rights, ensure=
that the startup account for the SQL Backup Agent service application has =
been granted the necessary permissions.
Using SQL Backup=
Pro from the command line
The SQL Backup Pro command line program communicates with SQL Server dir=
ectly; it does not use the SQL Backup Agent service application. To run&nbs=
p;SQLBackupC.exe, the user must have the SQL Server sysad=
min fixed server role. For more information, see Using the command line.
Changing=
the SQL Backup Agent service credentials
When you install the SQL Backup Pro server components, you specify:
- the user the SQL Backup Agent service logs on as, and
- the authentication mode the SQL Backup Agent service uses to connect to=
the SQL Server instance.
You can change the credentials used by the SQL Backup Agent service at a=
ny time, as described below.
Chang=
ing the SQL Backup Agent service startup account
To change the account the SQL Backup Agent service logs on as, use the W=
indows Services snap-in:
- From the Windows Control Panel, select Administrative Tool=
s > Services, or run servic=
es.msc.
- Select the SQL Backup Agent service for the SQL Server instance: <=
em>SQL Backup Agent - <instance name>.
Note: The SQL Backup Agent service for the local instance is called j=
ust SQL Backup Agent.
- Right-click the service and select Properties.
- On the Log On tab, specify the account you w=
ant the service to log on as. The account must have the permissions listed =
above.
- Click OK to close.
- Right-click the service and select Restart t=
o restart the service and apply your changes.
Changing the authenti=
cation mode for the SQL Backup Agent service
To change the authentication mode the SQL Backup Agent service startup a=
ccount uses to connect to the SQL Server instance, use the sqbsetl=
ogin extended stored procedure:
- Add the sqbsetlogin stored procedure from the SQL Ba=
ckup Pro extended stored procedure dynamic link library (xp_sqlbackup.d=
ll).
- Provide the user name and password to specify SQL Server authentication=
.
- Remove the sqbsetlogin extended stored procedure whe=
n you have finished using it. (This step is optional but recommended.)
For example:
EXECUTE =
master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
EXECUTE master..sqbsetlogin 'sa', 'sqbpassword'
EXECUTE master..sp_dropextendedproc sqbsetlogin
To revert to Windows authentication, call sqbsetlogin =
;with blank values:
EXECUTE =
master..sqbsetlogin '', ''
Using a different secur=
ity model
You may want to use a different security model, for example if you want =
to back up locally but copy the backup to a locked down network share. The =
following procedure assumes that you are working in a single domain.
- Create a domain account with minimal permissions. Add the domain accoun=
t to a security group on the Windows server on which the SQL Server is inst=
alled; the security group must have sufficient permissions to run as a serv=
ice.
- Create a SQL Server authenticated account that has the ability to back =
up and restore databases.To do this, add the account to the sysadm=
in or db_backupoperator fixed role, or if you =
are using SQL Server 2005, 2008 or 2012, you can use the GRANT BACKUP comma=
nd.
- When you install the SQL Backup Pro server components on the SQL Server=
:=20
- For the SQL Backup Agent service credentials, select Specific&n=
bsp;account and enter the domain account you created in step =
1.
- For the SQL Server credentials, select SQL Server authenti=
cation, and specify the credentials for the SQL Server authenticat=
ed account you created in step 2.
- Create the folder on the local server in which you want to create the b=
ackups, and a folder on a network share to which you want to copy the backu=
p files.
- Confirm that the permissions on both folders are set such that the doma=
in user you created in step 1 can access and write to them.
To check that all the accounts have the appropriate permissions, use the=
Back Up wizard to=
create a backup in a local folder and copy it to a network share.
Alternatively, run the following query to ensure that the domain account=
has sufficient permissions on the network share:
EXECUTE =
master..sqbutility 999, 'RWE', '<network location>'
If this is successful and the SQL Backup Agent service has read (R), wri=
te (W), and execute (E) permissions, the query will return:
If there is a problem, the query will return a value of 0,=
followed by a message, for example:
<SQBUTILITYRESULT>:0:Folder does not exist :
<network location>
Working =
with servers on different Windows domains
If you are working with servers which do not participate in the same Win=
dows domain, you can still use SQL Backup Pro to work with them as usual by=
setting up "matching accounts". This will be necessary if you want to copy=
backups to a locked down network share on a different Windows domain, or s=
et up log shipping between servers on different domains.
- Create accounts on each machine with identical user names and passwords=
.
- Set the SQL Backup Agent service to log in to the SQL Server using the =
account created in step 1, using the sqbsetlogin extende=
d stored procedure. For more information, see Changing the authentication mode above.<=
br class=3D"atl-forced-newline">
When log shipping, the SQL Backup Agent on both SQL Ser=
vers must log in using the matched account.
- Give the account on the other domain access permissions to the relevant=
locations.
------=_Part_984_1262190497.1710835159832--